窗口函数与CASE语句结果异常：按MondayHours排名需求

需求说明 #

• 需将MondayHours数值最低的记录排名设为1，数值越高排名依次递增
• 若MondayHours为NULL或IsMonday为NULL，该记录需排除在排名计数之外，排名字段显示NULL
• 仅通过一个CASE语句实现

问题分析 #

之前的写法存在两个核心问题：

1. 使用PARTITION BY EmpID会按员工ID分组排名，不符合全局排名的需求
2. 窗口函数未处理MondayHours为NULL的记录，NULL值会干扰有效记录的排名计算

解决方案代码 #

DROP TABLE IF EXISTS #t1

CREATE TABLE #t1 ( 
    EmpID VARCHAR(36), 
    [IsMonday] bit, 
    IsTuesday bit, 
    IsWednesday bit, 
    IsThursday bit, 
    [MondayHours] decimal(9,2), 
    TuesdayHours decimal(9,2), 
    WednesdayHours decimal(9,2), 
    ThursdayHours decimal(9,2), 
    [LastName] varchar(30), 
    [FirstName] varchar(30) 
)

INSERT INTO #t1 (EmpID, [IsMonday], IsTuesday, IsWednesday, IsThursday, [MondayHours], TuesdayHours, WednesdayHours, ThursdayHours, [LastName], [FirstName])
VALUES
( 'FDD12035-792D-4C53-80F1-AT28065A0968', 1, NULL, NULL, NULL, 0.00, NULL, NULL, NULL, 'SMITH', 'JOHN' ),
( 'B9968A1F-0203-4A69-EW14-366A05BC2B2E', 1, NULL, NULL, NULL, 1.50, NULL, NULL, NULL, 'HIGHTOWER', 'OFFICER' ),
( 'FDD12035-792D-4C53-80F1-AT28065A0968', NULL, NULL, 1, NULL, NULL, NULL, 18.50, NULL, 'SMITH', 'JOHN' ),
( 'FDD12035-792D-4C53-80F1-AT28065A0968', NULL, NULL, NULL, 1, NULL, NULL, NULL, 0.00, 'SMITH', 'JOHN' )

-- 正确查询语句
SELECT 
    empid,
    CASE 
        WHEN IsMonday IS NULL OR MondayHours IS NULL THEN NULL
        ELSE DENSE_RANK() OVER (
            ORDER BY 
                CASE WHEN MondayHours IS NOT NULL THEN MondayHours ELSE 999999.99 END, 
                LastName, 
                FirstName
        )
    END AS MondayHours_DenseRank,
    IsMonday,
    MondayHours,
    LastName,
    FirstName
FROM #T1

关键逻辑解释 #

• 外部CASE语句：直接过滤掉IsMonday或MondayHours为NULL的记录，返回NULL值，排除其排名资格
• 窗口函数内的CASE：将MondayHours为NULL的记录映射到一个极大值，确保它们在排序时排到最后，不会干扰有效记录的排名计算
• DENSE_RANK()全局排序：按MondayHours升序、姓氏、名字排序，保证数值最低的记录排名为1，相同数值的记录排名一致

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