按 Null 字母顺序排序,然后按 Not Null 排序

Order by Nulls alphabetically then Not Nulls

提问人:jackstraw22 提问时间:10/27/2023 最后编辑:Thom Ajackstraw22 更新时间:10/27/2023 访问量:38

问:

我有一个数据集,我尝试先按 null startdate 值排序,然后按姓氏排序。然后是非空开始日期,也按姓氏:

  declare @t table
  (lastname varchar(100),
  firstname varchar(50),
  startdate date)

  insert into @t
  values('Smith', 'John', '01/01/2023'),
  ('Marshall', 'Tom', NULL),
  ('Thomas', 'Frank', '01/01/2022'),
  ('Schmidt', 'Mike', NULL),
  ('Carter', 'Gary', '03/01/2023'),
  ('Belle', 'Albert', NULL)

  LastName     FirstName       StartDate
 Smith         John               2023-01-01
 Marshall      Tom               NULL
 Thomas        Frank             2022-01-01
 Schmidt       Mike              NULL
 Carter        Gary             2023-03-01
 Belle         Albert           NULL

期望输出:

  LastName     FirstName        StartDate
   Belle         Albert           NULL
   Marshall       Tom             NULL
   Schmidt        Mike            NULL
   Carter        Gary             2023-03-01
   Smith         John             2023-01-01
   Thomas        Frank            2022-01-01     

我试过做一个联盟:

  select * 
  into #nulls
  from @t
  where startdate is null
  order by lastname

  select * 
  into #nonnulls
  from @t
  where startdate is not null
  order by lastname

  select *
  from #nulls
  UNION
  select *
  from #nonnulls

我还尝试过使用按大小写排序:

  select *
  from @t
  order by case when startdate is null then lastname 
                when startdate is not null then lastname end
sql-server 案例 sql-order-by 联合

评论

0赞 Thom A 10/27/2023
想想你的;它实际上是 x = 然后 z else z。它始终解析为 z。对于不同的结果,您需要不同的结果。CASE
0赞 Xabi 10/27/2023
怎么样 ?select * from @t order by case when startdate is null then 0 else 1 end, lastname

答:

1赞 Charlieface 10/27/2023 #1

更有效的版本是用于合并两半,并使用排序列对其进行排序ORDER BY CASEUNION ALL

SELECT *, 1 AS ordering
FROM @t
WHERE startdate IS NULL

UNION ALL

SELECT *, 2
FROM @t
WHERE startdate IS NOT NULL

ORDER BY
  ordering,
  lastname

这允许服务器使用 Merge Concatenation 而不是 Sort