如何在SQL排名查询中生成DiffTop列以展示与排名首位项的差值（首位项展示与次位项的差值）

要实现你需要的DiffTop列，我们可以结合窗口函数和CASE条件判断来完成，完全适配你给出的规则。以下是具体的实现步骤和完整SQL：

核心思路 #

我们需要先获取每个分组（rdate+rtime）下的两个关键值：

• 排名第1的SF值（分组内最大的SF，因为你按SF DESC排名）
• 排名第2的SF值
  然后根据当前行的rank值，用CASE语句计算对应的差值。

完整SQL查询 #

SELECT 
    rdate, 
    rtime, 
    SF, 
    DENSE_RANK() OVER (PARTITION BY rdate, rtime ORDER BY SF DESC) AS rank,
    -- 根据rank计算DiffTop
    CASE
        WHEN DENSE_RANK() OVER (PARTITION BY rdate, rtime ORDER BY SF DESC) = 1 
            THEN FIRST_VALUE(SF) OVER (PARTITION BY rdate, rtime ORDER BY SF DESC) - NTH_VALUE(SF, 2) OVER (PARTITION BY rdate, rtime ORDER BY SF DESC)
        ELSE SF - FIRST_VALUE(SF) OVER (PARTITION BY rdate, rtime ORDER BY SF DESC)
    END AS DiffTop
FROM DailySF;

优化简化（可选） #

如果不想重复写窗口函数，可以用CTE（公共表表达式）来让逻辑更清晰：

WITH RankedSF AS (
    SELECT 
        rdate, 
        rtime, 
        SF, 
        DENSE_RANK() OVER (PARTITION BY rdate, rtime ORDER BY SF DESC) AS rank,
        FIRST_VALUE(SF) OVER (PARTITION BY rdate, rtime ORDER BY SF DESC) AS top1_sf,
        NTH_VALUE(SF, 2) OVER (PARTITION BY rdate, rtime ORDER BY SF DESC) AS top2_sf
    FROM DailySF
)
SELECT 
    rdate, 
    rtime, 
    SF, 
    rank,
    CASE
        WHEN rank = 1 THEN top1_sf - top2_sf
        ELSE SF - top1_sf
    END AS DiffTop
FROM RankedSF;

特殊情况处理 #

如果某个分组（rdate+rtime）只有1条数据，top2_sf会是NULL，此时排名1的DiffTop也会是NULL。如果你希望这种情况显示0，可以修改CASE语句：

CASE
    WHEN rank = 1 THEN COALESCE(top1_sf - top2_sf, 0)
    ELSE SF - top1_sf
END AS DiffTop

这个方案完全匹配你给出的示例结果，比如第一组：

• 排名1的SF=54，top2_sf=52，DiffTop=54-52=2
• 排名2的SF=52，DiffTop=52-54=-2
• 排名3的SF=50，DiffTop=50-54=-4
  完全符合你的预期。

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