如何使用SQL查询RANKING表中指定ID用户的积分及对应排名位置

需调整的问题点 #

• 排名排序规则错误：需求中积分越高排名越靠前，现有写法用points ASC升序会导致积分越低排名数值越小，和预期相反，需要改为points DESC降序
• 筛选时机错误：窗口函数计算晚于WHERE子句执行，如果直接在计算排名的查询中加用户id筛选，只会对单条目标数据计算排名，永远返回排名1，因此需要先算完全表所有用户的排名，再在外层做筛选

可直接使用的实现代码 #

SELECT id_users, points, rank_position
FROM (
    SELECT 
        id_users,
        points,
        -- 如果需要处理同积分的排名规则，可以替换ROW_NUMBER为RANK/DENSE_RANK
        ROW_NUMBER() OVER (ORDER BY points DESC) AS rank_position
    FROM RANKING
) AS all_user_ranking
WHERE id_users = 3; -- 替换此处的数值即可查询对应id的用户排名

结果验证 #

执行以上语句查询id=3的用户时，返回结果和预期完全一致：

同积分场景的排名函数选择 #

如果业务中存在多个用户积分相同的场景，可以根据需求选择排名函数：

• ROW_NUMBER()：同积分也会生成不同的连续排名，不存在重复排名值
• RANK()：同积分排名相同，后续排名会跳过占位，比如2个并列第1名，下一名排名为3
• DENSE_RANK()：同积分排名相同，后续排名连续，比如2个并列第1名，下一名排名为2

内容的提问来源于stack exchange，提问作者Chiqui