使用DENSE_RANK()按日期排序时排名不符合预期的技术求助

我需要用DENSE_RANK()函数对支付记录进行排名，排序逻辑为：优先使用[PAYROLL DATE]字段，若该字段为空则使用[PROCESS DATE]。核心需求如下：

• 相同[PAYROLL DATE]的记录排名一致
• 当[PROCESS DATE]的值与某条[PAYROLL DATE]的值相同时，该PROCESS DATE记录需单独占用一个排名，不能与对应PAYROLL DATE的记录同排名

原查询的问题 #

此前使用的查询语句来自Stack Overflow，但仅当重复的PROCESS DATE位于数据集末尾时符合预期，其他场景结果异常：

DENSE_RANK() OVER (PARTITION BY [Plan_ID],ee_id,loan_number ORDER BY CASE WHEN payroll_date IS NULL THEN 1 ELSE 0 END, payroll_date, process_date ASC)

实际与预期排名差异 #

原查询会先对所有非空PAYROLL DATE的记录完成排名，再处理PROCESS DATE的记录，无法实现按日期整体排序的要求。我尝试过多种ORDER BY组合、CASE语句，以及RANK()、ROW_NUMBER()函数，均未得到符合需求的结果。

测试表与原查询代码 #

create table table1 (
  plan_id varchar(10), 
  ee_id integer, 
  loan_number integer, 
  payroll_date date, 
  process_date date);

insert into table1 values 
('aaa', 1234, 1, '2021-11-26', null), 
('aaa', 1234, 1, null, '2021-11-26'), 
('aaa', 1234, 1, '2022-02-18', null), 
('aaa', 1234, 1, '2022-02-18', null), 
('aaa', 1234, 1, null, '2022-02-18'); 

SELECT 
*
,PayNum = 
DENSE_RANK() OVER (PARTITION BY [Plan_ID],ee_id,loan_number ORDER BY CASE WHEN payroll_date IS NULL THEN 1 ELSE 0 END, payroll_date, process_date ASC)
FROM table1
ORDER BY ISNULL(payroll_date,process_date)

解决方案 #

要实现整体日期排序，同时区分同日期值下的PAYROLL DATE与PROCESS DATE记录，需调整DENSE_RANK()的排序逻辑：先按合并后的日期排序，再按日期类型区分（确保同日期下两类记录属于不同排名组）。

修改后的查询语句：

SELECT 
*
,PayNum = 
DENSE_RANK() OVER (
    PARTITION BY [Plan_ID], ee_id, loan_number 
    ORDER BY ISNULL(payroll_date, process_date), 
             CASE WHEN payroll_date IS NOT NULL THEN 0 ELSE 1 END
)
FROM table1
ORDER BY ISNULL(payroll_date,process_date), CASE WHEN payroll_date IS NOT NULL THEN 0 ELSE 1 END

逻辑说明 #

1. 第一排序字段ISNULL(payroll_date, process_date)：确保所有记录按实际日期值整体排序，不会将两类日期分开处理
2. 第二排序字段CASE WHEN payroll_date IS NOT NULL THEN 0 ELSE 1 END：标记日期类型，非空PAYROLL DATE的记录排在前面，且因为该字段值不同，DENSE_RANK()会为同日期值的两类记录分配不同的排名，满足单独排名的需求

验证结果 #

执行修改后的查询，得到的PayNum列完全符合预期排名：

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