order by case when col is null then 2 else 1 end, col
select * from tb order by case when 时间字段 is null then 1 else 0 end,时间字段
if not object_id('ta') is null drop table ta Go Create table ta ([id] int,date1 datetime,date2 varchar(20)) Insert ta select 1 , '2009-01-01', '2009-01-03' union all select 2 , NULL , '2009-01-02' union all select 3 , '2009-02-01' , '2009-02-02' union all select 4 , '2009-01-05' , NULL union all select 5 , '2009-03-04' , '2009-03-06'select * from ta order by case when date2 is null then getdate()+1 else date2 end ascid date1 date2 ----------- ----------------------- -------------------- 2 NULL 2009-01-02 1 2009-01-01 00:00:00.000 2009-01-03 3 2009-02-01 00:00:00.000 2009-02-02 5 2009-03-04 00:00:00.000 2009-03-06 4 2009-01-05 00:00:00.000 NULL(5 行受影响)
case when col is null then 2 else 1 end,
col
order by
case when 时间字段 is null then 1 else 0 end,时间字段
if not object_id('ta') is null
drop table ta
Go
Create table ta ([id] int,date1 datetime,date2 varchar(20))
Insert ta select
1 , '2009-01-01', '2009-01-03' union all select
2 , NULL , '2009-01-02' union all select
3 , '2009-02-01' , '2009-02-02' union all select
4 , '2009-01-05' , NULL union all select
5 , '2009-03-04' , '2009-03-06'select * from ta order by case when date2 is null then getdate()+1 else date2 end ascid date1 date2
----------- ----------------------- --------------------
2 NULL 2009-01-02
1 2009-01-01 00:00:00.000 2009-01-03
3 2009-02-01 00:00:00.000 2009-02-02
5 2009-03-04 00:00:00.000 2009-03-06
4 2009-01-05 00:00:00.000 NULL(5 行受影响)
我菜吧。
原来case when 也可以这样用