将几个查询的结果相加，然后在 SQL 中找到前 5 个
我有 3 个查询:
table: pageview
SELECT event_id, count(*) AS pageviews
FROM pageview
GROUP BY event_id
ORDER BY pageviews DESC, rand()
LIMIT 1000
table: upvote
SELECT event_id, count(*) AS upvotes
FROM upvote
GROUP BY event_id
ORDER BY upvotes DESC, rand()
LIMIT 1000
table: attending
SELECT event_id, count(*) AS attendants
FROM attending
GROUP BY event_id
ORDER BY attendants DESC, rand()
LIMIT 1000
我想合并按金额排序的所有 3 个查询的 event_id，然后选择前 5 个.我该怎么做?
I'd like to combine the event_ids of all 3 queries ordered by amount and then choose the top 5. How do I do that?
这是我为让它发生而做的:
SELECT event_id, sum(amount) AS total
FROM (
(SELECT event_id, count(*) AS amount
FROM pageview
GROUP BY event_id
ORDER BY amount DESC, rand()
LIMIT 1000)
UNION ALL
(SELECT event_id, count(*) as amount
FROM upvote
GROUP BY event_id
ORDER BY amount DESC, rand()
LIMIT 1000)
UNION ALL
(SELECT event_id, count(*) as amount
FROM attending
GROUP BY event_id
ORDER BY amount DESC, rand()
LIMIT 1000)
) x
GROUP BY 1
ORDER BY sum(amount) DESC
LIMIT 5;
推荐答案
将所有三个查询的结果行UNION，然后选择amount最高的5行:
To UNION the resulting rows of all three queries and then pick the 5 rows with the highest amount:
(SELECT event_id, count(*) AS amount
FROM pageview
GROUP BY event_id
ORDER BY pageviews DESC, rand()
LIMIT 1000)
UNION ALL
(SELECT event_id, count(*)
FROM upvote
GROUP BY event_id
ORDER BY upvotes DESC, rand()
LIMIT 1000)
UNION ALL
(SELECT event_id, count(*)
FROM attending
GROUP BY event_id
ORDER BY attendants DESC, rand()
LIMIT 1000)
ORDER BY 2 DESC
LIMIT 5;
手册:
要将 ORDER BY 或 LIMIT 应用于单个 SELECT，请将括在 SELECT 的括号内的子句.
To apply
ORDER BYorLIMITto an individualSELECT, place the clause inside the parentheses that enclose theSELECT.
UNION ALL 保留重复项.
要为每个event_id添加计数:
To add the counts for every event_id:
SELECT event_id, sum(amount) AS total
FROM (
(SELECT event_id, count(*) AS amount
FROM pageview
GROUP BY event_id
ORDER BY pageviews DESC, rand()
LIMIT 1000)
UNION ALL
(SELECT event_id, count(*)
FROM upvote
GROUP BY event_id
ORDER BY upvotes DESC, rand()
LIMIT 1000)
UNION ALL
(SELECT event_id, count(*)
FROM attending
GROUP BY event_id
ORDER BY attendants DESC, rand()
LIMIT 1000)
) x
GROUP BY 1
ORDER BY sum(amount) DESC
LIMIT 5;
这里的棘手部分是并非每个 event_id 都会出现在所有三个基本查询中.所以要注意 JOIN 不会完全丢失行并且添加不会变成 NULL.
The tricky part here is that not every event_id will be present in all three base queries. So take care that a JOIN does not lose rows completely and additions don't turn out NULL.
使用UNION ALL，而不是UNION.您不想删除相同的行，而是想将它们相加.
Use UNION ALL, not UNION. You don't want to remove identical rows, you want to add them up.
x 是AS x 的表别名和简写.子查询必须具有名称.此处可以是任何其他名称.
x is a table alias and shorthand for AS x. It is required for for a subquery to have a name. Can be any other name here.
SOL 特性 FULL OUTER JOIN 在 MySQL 中没有实现(我上次检查过)，所以你必须使用 UNION.FULL OUTER JOIN 将连接所有三个基本查询而不会丢失行.
The SOL feature FULL OUTER JOIN is not implemented in MySQL (last time I checked), so you have to make do with UNION. FULL OUTER JOIN would join all three base queries without losing rows.
SELECT event_id, sum(amount) AS total
FROM (
(SELECT event_id, count(*) / 100 AS amount
FROM pageview ... )
UNION ALL
(SELECT event_id, count(*) * 5
FROM upvote ... )
UNION ALL
(SELECT event_id, count(*) * 10
FROM attending ... )
) x
GROUP BY 1
ORDER BY sum(amount) DESC
LIMIT 5;
或者，以多种方式使用基本计数:
Or, to use the base counts in multiple ways:
SELECT event_id
,sum(CASE source
WHEN 'p' THEN amount / 100
WHEN 'u' THEN amount * 5
WHEN 'a' THEN amount * 10
ELSE 0
END) AS total
FROM (
(SELECT event_id, 'p'::text AS source, count(*) AS amount
FROM pageview ... )
UNION ALL
(SELECT event_id, 'u'::text, count(*)
FROM upvote ... )
UNION ALL
(SELECT event_id, 'a'::text, count(*)
FROM attending ... )
) x
GROUP BY 1
ORDER BY 2 DESC
LIMIT 5;
相关文章