order by case when col<getdate() then 1 else 0 end
分两部分排序然后union all
order by case when datediff(dd,col,getdate())>=1 then 1 else 0 end
order by datediff(dd,col,getdate()) desc
精确到秒吧 order by datediff(ss,col,getdate()) desc
能够给条完整的sql吗? 不要调用系统函数
这个可以 if object_id('t1') is not null drop table t1 GOCreate table t1 ( id int, col datetime ) insert into t1 select 1,dateadd(day,1,getdate()) union all select 2,dateadd(day,-1,getdate()) union all select 3,dateadd(day,2,getdate()) union all select 4,dateadd(day,-2,getdate()) Goselect * from t1 order by case when datediff(dd,col,getdate())>=1 then 1 else 0 end,id descid col ----------- ----------------------- 3 2011-04-14 11:30:03.300 1 2011-04-13 11:30:03.300 4 2011-04-10 11:30:03.300 2 2011-04-11 11:30:03.300(4 行受影响)
select * from 表 order by case when rq>=getdate() then DATEDIFF(dd,rq,getdate()) else rq end
order by datediff(ss,col,getdate()) desc
不要调用系统函数
if object_id('t1') is not null
drop table t1
GOCreate table t1
(
id int,
col datetime
)
insert into t1
select 1,dateadd(day,1,getdate()) union all
select 2,dateadd(day,-1,getdate()) union all
select 3,dateadd(day,2,getdate()) union all
select 4,dateadd(day,-2,getdate())
Goselect * from t1 order by case when datediff(dd,col,getdate())>=1 then 1 else 0 end,id descid col
----------- -----------------------
3 2011-04-14 11:30:03.300
1 2011-04-13 11:30:03.300
4 2011-04-10 11:30:03.300
2 2011-04-11 11:30:03.300(4 行受影响)
order by case when rq>=getdate() then DATEDIFF(dd,rq,getdate()) else rq end