mysql求和 子查询"/>
mysql求和 子查询
这个问题留下了解释的空间.要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;
更多推荐
mysql求和 子查询
发布评论