mysql - MYSQL JOIN同一个表+ COUNT确定排名

我有一个表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

谢谢您的提示！

最佳答案

只需将group by添加到您的查询中：

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