SQL实现两个日期列按自定义特殊逻辑排序的问题及解决方案

需求说明 #

排序规则如下：

• date列非空时优先按date列排序，date列为空时按create_date列排序，需优先保障date非空行的排序优先级
• 补充规则：初始期望按create_date排序，但若date值出现顺序错乱（即更新的date出现在更靠前的行），需要将这类行移动到结果集末尾，并按date值排序

初始测试样例 #

表结构与测试数据 #

drop table if exists #a

create table #a(
    create_date datetime,
    [date] datetime,
    [desired_order] int,
    primary key ([create_date])
);

insert into #a values('20170101 14:09:00.000', NULL,  1);
insert into #a values('20170101 14:10:00.000', '20210101',5);
insert into #a values('20170101 14:10:03.000', '20190101', 2);
insert into #a values('20170101 14:10:04.000', NULL, 3);
insert into #a values('20170101 14:10:02.000', '20200101', 4);

预期排序效果 #

初始方案问题 #

后续补充的测试数据会导致原排序方案失效，出现2017年日期排在2016年日期之前的问题，补充测试数据如下：

insert into #a(create_date, [date]) values('20170101 14:09:00.000', NULL);
insert into #a(create_date, [date]) values('20170101 14:10:00.000', '20210101');
insert into #a(create_date, [date]) values('20170101 14:10:05.000', '20180101');
insert into #a(create_date, [date]) values('20170101 14:10:03.000', '20160101');
insert into #a(create_date, [date]) values('20170101 14:10:02.000', '20160205');
insert into #a(create_date, [date]) values('20170101 14:10:04.000', NULL);
insert into #a(create_date, [date]) values('20170101 14:10:01.000', '20200101');
insert into #a(create_date, [date]) values('20170101 14:10:06.000', '20230101');
insert into #a(create_date, [date]) values('20170101 14:10:07.000', '20170101');

最终解决方案 #

通过CTE提前计算两个行号，分别对应按创建时间排序的序号和按业务日期排序的序号，排序时判断date是否为空，分别对应不同的排序维度即可解决问题：

;WITH cte1 AS (
    SELECT 
        create_date, 
        [DATE],
        ROW_NUMBER() OVER(ORDER BY CREATE_DATE) CN,
        ROW_NUMBER() OVER(ORDER BY [DATE]) DN
  FROM #a
)
select *
from 
    cte1
order by 
    case when [date] is null then CN else DN end, 
    CREATE_DATE

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