PostgreSQL带WHERE条件查询时如何用RANK()获取全局绝对排名

你当前SQL拿到的是房间内排名而非全表排名，核心原因是SQL执行顺序中WHERE子句会先于SELECT阶段的窗口函数执行：先过滤掉了所有room != 2的记录，RANK()只能基于过滤后剩余的2条数据计算排名，自然无法得到全表维度的绝对排名。

调整执行逻辑，先对全表所有数据计算全局排名，再按房间条件过滤结果即可，通用写法如下（所有支持窗口函数的数据库均适用）：

-- 子查询写法，兼容性最好
SELECT * 
FROM (
    SELECT 
        Id,
        Name,
        Room,
        Point,
        RANK() OVER (ORDER BY Point ASC) AS rnk
    FROM users
) AS global_ranked_users
WHERE Room = 2;

如果你的数据库支持CTE语法（MySQL 8.0+、PostgreSQL、SQL Server等），也可以用可读性更好的CTE写法，执行效果完全一致：

-- CTE写法，可读性更高
WITH global_ranked_users AS (
    SELECT 
        *,
        RANK() OVER (ORDER BY Point ASC) AS rnk 
    FROM users
)
SELECT * FROM global_ranked_users WHERE Room = 2;

执行后将得到你预期的结果：

你可以根据排名规则的实际需求替换窗口函数：

• 若需要并列分数占用同一排名、后续排名跳号（如两个并列第2后下一名直接为第4），保留RANK()即可
• 若需要并列分数占用同一排名、后续排名不跳号（如两个并列第2后下一名为第3），替换为DENSE_RANK()
• 若不需要处理并列，按行依次生成连续排名，替换为ROW_NUMBER()

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