如何实现含Cat类型的非线性排名？需保留Dog的RANK()逻辑

需求说明 #

• Dog类型按标准RANK()函数规则排名：同分数据共享同一排名，后续排名跳过对应数量的位次（如示例中Buddy和Duke同为第4名，Max直接排第6名）。
• Cat类型的排名必须等于它之后第一个Dog的排名，不占用Dog的排名位置。

无临时表/存储过程的SQL实现方案 #

WITH dog_ranks AS (
    -- 预先计算所有Dog的标准RANK排名
    SELECT 
        type,
        name,
        score,
        RANK() OVER (ORDER BY score DESC) AS dog_rank
    FROM your_table
    WHERE type = 'Dog'
)
SELECT 
    t.type,
    t.name,
    t.score,
    CASE 
        WHEN t.type = 'Dog' THEN dr.dog_rank
        -- 对Cat取后续第一个Dog的排名，IGNORE NULLS跳过非Dog行
        ELSE FIRST_VALUE(dr.dog_rank) OVER (
            ORDER BY t.score DESC
            ROWS BETWEEN CURRENT ROW + 1 AND UNBOUNDED FOLLOWING
            IGNORE NULLS
        )
    END AS 排名
FROM your_table t
LEFT JOIN dog_ranks dr 
    ON t.type = dr.type 
    AND t.name = dr.name 
    AND t.score = dr.score
ORDER BY t.score DESC;

逻辑解释 #

1. CTE dog_ranks：单独筛选Dog数据，用RANK()窗口函数计算标准排名，确保Dog的排名逻辑完全符合要求。
2. 主查询关联：将原表与dog_ranks关联，为每个Dog匹配预先计算好的排名。
3. Cat的排名处理：使用FIRST_VALUE()窗口函数，从当前Cat行的下一行开始向后查找，忽略非Dog行的NULL值，直接获取第一个Dog的排名作为Cat的排名。
4. 排序输出：最终按分数降序排序，得到符合示例要求的结果。

兼容性补充 #

如果你的SQL方言不支持IGNORE NULLS（如MySQL 8.0早期版本），可以改用子查询方式获取Cat对应的后续第一个Dog排名，示例如下：

WITH dog_ranks AS (
    SELECT 
        type,
        name,
        score,
        RANK() OVER (ORDER BY score DESC) AS dog_rank
    FROM your_table
    WHERE type = 'Dog'
),
sorted_data AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
    FROM your_table
)
SELECT 
    s.type,
    s.name,
    s.score,
    CASE 
        WHEN s.type = 'Dog' THEN dr.dog_rank
        ELSE (
            SELECT dr_inner.dog_rank
            FROM sorted_data s_inner
            JOIN dog_ranks dr_inner ON s_inner.type = dr_inner.type AND s_inner.name = dr_inner.name AND s_inner.score = dr_inner.score
            WHERE s_inner.row_num > s.row_num
            ORDER BY s_inner.row_num
            LIMIT 1
        )
    END AS 排名
FROM sorted_data s
LEFT JOIN dog_ranks dr ON s.type = dr.type AND s.name = dr.name AND s.score = dr.score
ORDER BY s.score DESC;

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