这个问题留下了解释的余地。要
UNIOn产生的所有三个查询的行，然后挑选5行最高“金额”：
(SELECt event_id, count(*) AS amountFROM pageview GROUP BY event_idORDER BY pageviews DESC, rand()LIMIT 1000)UNIOn ALL(SELECt event_id, count(*)FROM upvoteGROUP BY event_idORDER BY upvotes DESC, rand()LIMIT 1000)UNIOn ALL(SELECt event_id, count(*)FROM attendingGROUP BY event_idORDER BY attendants DESC, rand()LIMIT 1000)ORDER BY 2 DESCLIMIT 5;
手册：
要适用于
ORDER BY或适用LIMIT于个人SELECt，请将条款放在括号内SELECT。
UNIOn ALL，因此不会删除重复项。
如果要 为每个添加计数event_id
，则此查询应这样做：
SELECT event_id, sum(amount) AS totalFROM ( (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) ) xGROUP BY 1ORDER BY sum(amount) DESCLIMIT 5;
这里的棘手部分是，并非所有event_id都将出现在所有三个基本查询中。所以你必须注意JOIN不会完全丢失行并且添加不会出现
NULL。
使用
UNIOn ALL，而不是
UNIOn。您不想删除相同的行，而是要添加它们。
该
x是速记
AS x-表别名。子查询必须具有名称。在这里可以是任何其他名称。
SOL功能
FULL OUTER JOIN未在MySQL中实现（我上次查看），因此您必须使用UNIOn。
FULL OUTERJOIN将联接所有三个基本查询而不会丢失行。
回答跟进问题
SELECt event_id, sum(amount) AS totalFROM ( (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 ... ) ) xGROUP BY 1ORDER BY sum(amount) DESCLIMIT 5;
或者，如果您想以多种方式使用基本计数：
SELECt event_id ,sum(CASE source WHEN 'p' THEN amount / 100 WHEN 'u' THEN amount * 5 WHEN 'a' THEN amount * 10 ELSE 0END) AS totalFROM ( (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 ... ) ) xGROUP BY 1ORDER BY 2 DESCLIMIT 5;
