mysql求和 子查询_mysql – 对几个查询的结果进行求和,然后在SQL中找到前5个

这个问题留下了解释的空间.要UNION所有三个查询的结果行,然后选择具有最高“金额”的5行：

(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;

The manual:

To apply ORDER BY or LIMIT to an individual SELECT, place the clause

inside the parentheses that enclose the SELECT.

UNION ALL,因此不会删除重复项.

如果要为每个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.

使用UNION ALL,而不是UNION.您不想删除相同的行,而是想要添加它们.

x是AS x的简写 – 表别名.子查询需要具有名称.这里可以是任何其他名称.

SOL-feature FULL OUTER JOIN没有在MySQL中实现(我上次看过),所以你必须要使用UNION. FULL OUTER JOIN将加入所有三个基本查询而不会丢失行.

回答后续问题

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;

或者,如果您想以多种方式使用基本计数：

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;