MYSQL JOIN同表+COUNT判断排名

Question

我有一个表 RANKINGS，其中包含几个 userId，与一个 genreId 相关联，以及一个与该流派相关的排名。

作为基准，我将使用 userId = 30520，但该表包含很多记录。

userId    genreId    ranking
30520     0          135
30520     13         91
30520     24         163

手动计算userId的位置很容易，例如最后记录:

SELECT COUNT(ranking) as position
FROM rankings
WHERE genreId = 24
AND ranking >= 163
ORDER BY ranking DESC

通过这种手动方法，我得到:

userId    genreId    ranking    position
30520     0          135        5
30520     13         91         17
30520     24         163        7

但我无法想象这是如何通过一个查询来实现的。我的尝试:

SELECT userId, a.genreId, COUNT(a.ranking) as position,
FROM rankings a
LEFT JOIN 
(
    SELECT genreId, ranking
    FROM rankings
    WHERE userId = 30520
) b
ON a.genreId = b.genreId
AND a.ranking >= b.ranking
ORDER BY a.ranking DESC

我只得到一行:

userId    genreId    position
30520     0          3741

感谢您的任何提示!

Answer 1

只需将分组依据添加到您的查询中:

SELECT userId, a.genreId, COUNT(a.ranking) as position
FROM rankings a
LEFT JOIN 
(
    SELECT genreId, ranking
    FROM rankings
    WHERE userId = 30520
) b
ON a.genreId = b.genreId
AND a.ranking >= b.ranking
GROUP BY genreId
ORDER BY a.ranking DESC