要按一个日期字段排序,默认的排序是null值在最上面,如下:
null 2006-2-1
2006-1-1 2006-2-1
2006-5-1 2006-3-1
----------------------------
想要这样的效果:
2006-1-1 2006-2-1
2006-5-1 2006-3-1
null 2006-2-1
-----------------------------
null 2006-2-1
2006-1-1 2006-2-1
2006-5-1 2006-3-1
----------------------------
想要这样的效果:
2006-1-1 2006-2-1
2006-5-1 2006-3-1
null 2006-2-1
-----------------------------
--对于smalldatetime类型的字段,取最大值'2079-06-06'
--对于datetime类型的字段,取最大值'9999-12-31'
create table #t(rq smalldatetime, num int)insert into #t(rq,num)
select null,1 union all
select '2006-1-1',2 union all
select '2006-5-1',3 select * from #t order by rqselect * from #t order by case when rq is null then '2079-06-06' else rq enddrop table #t
select null,1 union all
select '2006-1-1',2 union all
select '2006-5-1',3
select * from #t order by case when rq is null then 1 else 0 end ,rq
大家五一快乐。