时间戳

首页 > 解决方案 > 两个日期列按特殊逻辑排序

sql - 两个日期列按特殊逻辑排序

问题描述

我需要一些帮助来通过查询获得以下所需的顺序，我已经在下面硬编码了所需的顺序。我需要按日期列排序或按创建日期排序，如果日期列为 null 但优先 [date] 其中不为 null，如下所示

创建日期 日期 所需顺序
2017-01-01 14:09:00.000 无效的 1
2017-01-01 14:10:03.000 2019-01-01 00:00:00.000 2
2017-01-01 14:10:04.000 无效的 3
2017-01-01 14:10:02.000 2020-01-01 00:00:00.000 4
2017-01-01 14:10:00.000 2021-01-01 00:00:00.000 5 
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);

select * from #a order by desired_order

评论的进一步解释：首先，我想按 create_date 排序，但任何出现乱序的日期值，即较新的日期出现在较早的行中，则需要将其移至结果集的末尾以及任何这些行按日期排序。

接受答案后：我发现一组数据破坏了已接受的解决方案：

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');

出于某种原因，2017 年早于 2016 年日期，感谢您的帮助。

通过所有测试用例的解决方案：

;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

标签： sqlsql-serversortingtsql

解决方案

这似乎是一个非常奇怪的要求。您可以使用解析函数来解决这个问题，以找出前面min是否有更小的值。date这是一个可能的解决方案，带有注释来解释逻辑：

with cte1 as (
  select *
    -- Find the smallest value between this row and the last row
    , min([Date]) over (partition by case when [date] is not null then 1 else 0 end order by create_date desc rows between unbounded preceding and unbounded following) mindate
  from #a
)
select create_date, [date]
  -- Testing - uncomment to see how the logic works
  --, desired_order, mindate
from cte1
-- If no date, always keep in the first section, and order by create_date
-- If there is a date, check whether this date is greater than the smallest date ahead of us, and if so move ahead
order by case when [date] is null then 0 else case when [date] > [mindate] then 1 else 0 end end, case when [date] > [mindate] then [date] else create_date end;

回报：

创建日期 日期
2017-01-01 14:09:00.000 无效的
2017-01-01 14:10:03.000 2019-01-01 00:00:00.000
2017-01-01 14:10:04.000 无效的
2017-01-01 14:10:02.000 2020-01-01 00:00:00.000
2017-01-01 14:10:00.000 2021-01-01 00:00:00.000

注意：由于您的示例数据非常简短，因此我无法确定此功能是否适用于所有边缘情况。

推荐阅读