close_time为null的start_time最新的排在前面 那 close_time不为null的是以start_time最新的先排还是以close_time为最新的先排? 下面是以start_time为最新的先排 select * from [Table] order by case when close_time is null then 0 else 1 end,start_time desc,close_time
ORder by start_time DESC,isnull(close_time,getdate()) DESC
ORder by isnull(close_time,'2099-1-1') DESC,start_time DESC
close_time 为空的 排在后面去了,不对。
select * from [Table] order by case when close_time is null then 0 else 1 end,,close_time desc,start_time desc
另外,按照你上面的2个,下面的代码,应该就是你想要的:declare @table table(start_time datetime,close_time datetime)insert into @table(start_time,close_time) values ('2012-3-2' ,null), ('2012-3-15' ,'2012-3-4'), ('2012-3-4' ,'2012-3-3'), ('2012-3-18' ,null) select * from @table order by case when close_time IS null then 1 else 0 end desc ,
case when close_time IS null then start_time else close_time end desc /* start_time close_time2012-03-18 00:00:00.000 NULL 2012-03-02 00:00:00.000 NULL 2012-03-15 00:00:00.000 2012-03-04 00:00:00.000 2012-03-04 00:00:00.000 2012-03-03 00:00:00.000*/
感谢分享,但是这种写法是什么意思呀,能否给点资料。 select * from @table order by case when close_time IS null then 1 else 0 end desc ,
case when close_time IS null then start_time else close_time end desc
那
close_time不为null的是以start_time最新的先排还是以close_time为最新的先排?
下面是以start_time为最新的先排
select * from [Table]
order by case when close_time is null then 0 else 1 end,start_time desc,close_time
第二 close_time不为空的这部分数据中,close_time 降序排列
谢谢
以close_time 为最新的排列,谢谢
close_time 为空的 排在后面去了,不对。
order by case when close_time is null then 0 else 1 end,,close_time desc,start_time desc
另外,按照你上面的2个,下面的代码,应该就是你想要的:declare @table table(start_time datetime,close_time datetime)insert into @table(start_time,close_time)
values ('2012-3-2' ,null),
('2012-3-15' ,'2012-3-4'),
('2012-3-4' ,'2012-3-3'),
('2012-3-18' ,null)
select *
from @table
order by case when close_time IS null
then 1
else 0
end desc ,
case when close_time IS null
then start_time
else close_time
end desc /*
start_time close_time2012-03-18 00:00:00.000 NULL
2012-03-02 00:00:00.000 NULL
2012-03-15 00:00:00.000 2012-03-04 00:00:00.000
2012-03-04 00:00:00.000 2012-03-03 00:00:00.000*/
select *
from @table
order by case when close_time IS null
then 1
else 0
end desc ,
case when close_time IS null
then start_time
else close_time
end desc
http://blog.csdn.net/dba_huangzj/article/details/7684520