提问人:jackstraw22 提问时间:10/27/2023 最后编辑:Thom Ajackstraw22 更新时间:10/27/2023 访问量:38
按 Null 字母顺序排序,然后按 Not Null 排序
Order by Nulls alphabetically then Not Nulls
问:
我有一个数据集,我尝试先按 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
答:
1赞
Charlieface
10/27/2023
#1
更有效的版本是用于合并两半,并使用排序列对其进行排序ORDER BY CASE
UNION 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。
评论
CASE
select * from @t order by case when startdate is null then 0 else 1 end, lastname