呼吁所有sql爱好者!快速信息:使用PostgreSQL。
我有一个查询,返回每个类别用户的最大点赞次数。我现在想要的是显示每个类别中最喜欢的前3个用户。
一个有用的资源正在使用此示例来解决问题:
select type, variety, price
from fruits
where (
select count(*) from fruits as f
where f.type = fruits.type and f.price <= fruits.price
) <= 2;
我理解这一点,但是我的查询使用的是联接,而且我还是一个初学者,所以我无法有效地使用此信息。
顺便说一句,这是我的查询,用于针对每个类别返回用户的MAX个赞。
SELECT category, username, MAX(post_likes) FROM (
SELECT c.name category, u.username username, SUM(p.like_count) post_likes, COUNT(*) post_num
FROM categories c
JOIN topics t ON c.id = t.category_id
JOIN posts p ON t.id = p.topic_id
JOIN users u ON u.id = p.user_id
GROUP BY c.name, u.username) AS leaders
WHERE post_likes > 0
GROUP BY category, username
HAVING MAX(post_likes) >= (SELECT SUM(p.like_count)
FROM categories c
JOIN topics t ON c.id = t.category_id
JOIN posts p ON t.id = p.topic_id
JOIN users u ON u.id = p.user_id WHERE c.name = leaders.category
GROUP BY u.username order by sum desc limit 1)
ORDER BY MAX(post_likes) DESC;
任何和所有帮助将不胜感激。我很难解决这个问题。谢谢!
如果您希望每个类别的喜欢最多,请使用窗口功能:
SELECT cu.*
FROM (SELECT c.name as category, u.username as username,
SUM(p.like_count) as post_likes, COUNT(*) as post_num,
ROW_NUMBER() OVER (PARTITION BY c.name ORDER BY COUNT(*) DESC) as seqnum
FROM categories c JOIN
topics t
ON c.id = t.category_id JOIN
posts p
ON t.id = p.topic_id JOIN
users u
ON u.id = p.user_id
GROUP BY c.name, u.username
) cu
WHERE seqnum <= 3;
即使存在联系,每个类别始终返回三行。如果您想做其他事情,请考虑DENSE_RANK()
或RANK()
代替ROW_NUMBER()
。
另外,as
在FROM
子句中使用列别名。尽管是可选的,但有一天您会省略逗号,并感谢您有使用的习惯as
。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句