SQLite如何获取按score排序后指定ID行的排名索引

完全可以通过单条SQL直接返回目标id对应的排名，不需要拉取全表数据遍历，根据不同排名规则可以选择对应写法：

支持窗口函数的版本（SQLite 3.25.0及以上，2018年后发布的版本基本都支持） #

窗口函数是最直观的实现方式，根据排名规则选择对应函数即可：

• 如果你需要的排名和原方案全表排序后遍历的行号完全一致（同分不做并列，名次连续），使用ROW_NUMBER()：

WITH ranked AS (
    SELECT
        id,
        ROW_NUMBER() OVER (ORDER BY score DESC) AS rank_position
    FROM scores
)
SELECT rank_position FROM ranked WHERE id = ?;

语句里的?是预编译参数占位符，传入你要查询的目标id即可。

• 如果业务需要同分并列排名（例如两个用户都是满分，两人均为第1名，下一个分数的用户排名为3），把上述语句里的ROW_NUMBER()替换为RANK()即可。
• 如果需要同分并列但名次不跳号（例如两个满分用户都是第1名，下一个分数的用户排名为2），把ROW_NUMBER()替换为DENSE_RANK()即可。

低版本兼容写法（不支持窗口函数的旧版SQLite可用） #

通过计数逻辑实现排名，不需要依赖窗口函数，兼容性更强：

• 并列排名逻辑（和RANK()效果一致）：统计分数比目标用户高的总人数，结果加1就是目标用户的排名

SELECT COUNT(*) + 1 AS rank_position
FROM scores
WHERE score > (SELECT score FROM scores WHERE id = ?);

• 连续行号排名逻辑（和原全表遍历方案结果完全一致）：除了分数更高的用户，同分情况下id更小的用户也排在前面，计数加1即为准确行号

SELECT COUNT(*) + 1 AS rank_position
FROM scores s1
WHERE
    s1.score > (SELECT s2.score FROM scores s2 WHERE s2.id = ?)
    OR (s1.score = (SELECT s2.score FROM scores s2 WHERE s2.id = ?) AND s1.id < ?);

上述语句里的三个?都传入同一个目标id即可，如果你的原排序逻辑里同分是按id降序排，把s1.id < ?改成s1.id > ?就行。

性能优化建议 #

给score字段建立降序索引，可以避免查询时全表排序，大幅提升排名计算速度，建索引语句如下：

CREATE INDEX IF NOT EXISTS idx_scores_score_desc ON scores(score DESC);

加索引后即使是十万级以上的数据量，排名查询也可以做到毫秒级返回。

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