SQL Server中DischargedDate为空时ROW_NUMBER排序异常问题

需求说明 #

• 当DischargedDate为空时，对应记录的sortOrder需设为1（即排序首位）
• 当DischargedDate不为空时，按Rank列的最小值排序

问题现象 #

ClientId 3634164、3634514的场景逻辑正常，但ClientId 3634795存在DischargedDate为空的记录，sortOrder=1却被分配给了Rank最小的记录，未优先将空DischargedDate的记录设为排序首位，问题出在ROW_NUMBER的排序逻辑上。

原查询代码 #

WITH CTE AS (
SELECT PC.OP__DOCID AS LegacyClientProgramId,
       PC.ClientKey AS ClientId,
       PC.PgmKey AS ProgramId,
       CASE WHEN Date_Discharged_Program IS NULL THEN 4 ELSE 5 END AS STATUS,
       PC.Date_Admit_Program AS RequestedDate,
       PC.Date_Admit_Program AS EnrolledDate,
       PC.Date_Discharged_Program AS DischargedDate,
       TX.RANK,
       ROW_NUMBER() OVER(PARTITION BY PC.ClientKey ORDER BY case when PC.Date_Discharged_Program 
                                                                      IS NULL THEN 0 
                                                                 when TX.Rank IS NOT NULL THEN 0 
                                                                 ELSE 1 END, TX.Rank) AS sortOrder
FROM FD__PROGRAM_CLIENT PC
LEFT JOIN LT__TXPLANHIERARCHY TX ON PC.PgmKey = TX.PgmKey
WHERE pc.ClientKey in ( SELECT ClientKey FROM LT__MIGRATE_CLIENT)
) SELECT LegacyClientProgramId,
         ClientId,
         ProgramId,
         STATUS,
         RequestedDate,
         EnrolledDate,
         DischargedDate,
         sortOrder,
         RANK,
         CASE WHEN sortOrder = 1 THEN 'Y' ELSE 'N' END AS PrimaryAssignment
         FROM CTE
         WHERE ProgramId <> 54

修正后的查询代码 #

WITH CTE AS (
SELECT PC.OP__DOCID AS LegacyClientProgramId,
       PC.ClientKey AS ClientId,
       PC.PgmKey AS ProgramId,
       CASE WHEN Date_Discharged_Program IS NULL THEN 4 ELSE 5 END AS STATUS,
       PC.Date_Admit_Program AS RequestedDate,
       PC.Date_Admit_Program AS EnrolledDate,
       PC.Date_Discharged_Program AS DischargedDate,
       TX.RANK,
       -- 修正排序逻辑：优先将DischargedDate为空的记录排在最前，非空记录按Rank升序排列
       ROW_NUMBER() OVER(PARTITION BY PC.ClientKey ORDER BY 
           CASE WHEN PC.Date_Discharged_Program IS NULL THEN 0 ELSE 1 END, 
           TX.Rank) AS sortOrder
FROM FD__PROGRAM_CLIENT PC
LEFT JOIN LT__TXPLANHIERARCHY TX ON PC.PgmKey = TX.PgmKey
WHERE pc.ClientKey in ( SELECT ClientKey FROM LT__MIGRATE_CLIENT)
) SELECT LegacyClientProgramId,
         ClientId,
         ProgramId,
         STATUS,
         RequestedDate,
         EnrolledDate,
         DischargedDate,
         sortOrder,
         RANK,
         CASE WHEN sortOrder = 1 THEN 'Y' ELSE 'N' END AS PrimaryAssignment
         FROM CTE
         WHERE ProgramId <> 54

修正说明 #

原查询的ROW_NUMBER排序逻辑中，把TX.Rank IS NOT NULL的记录也标记为0，导致这类记录和DischargedDate为空的记录处于同一优先级。当某个ClientId同时存在这两类记录时，数据库会按默认的隐含顺序（而非需求要求的优先级）分配sortOrder，从而出现异常。

修正后明确了优先级：

1. DischargedDate为空的记录排序值为0，直接排在最前面
2. DischargedDate不为空的记录排序值为1，后续按Rank列升序排列，确保需求逻辑生效

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