表Aid t u
1 2011-06-10 2011-06-20
2 2011-06-10 2011-06-21
3 2011-06-09
4 2011-06-15 2011-06-02我要的排序效果为 以当天日期为判断参数 去比较U字段 以相差的天数大小为排序,如果U字段为空,则转为和T字段比较这样的SQL应该写?
1 2011-06-10 2011-06-20
2 2011-06-10 2011-06-21
3 2011-06-09
4 2011-06-15 2011-06-02我要的排序效果为 以当天日期为判断参数 去比较U字段 以相差的天数大小为排序,如果U字段为空,则转为和T字段比较这样的SQL应该写?
select * from tb where u is not null order by datediff(day,u,,getdate())
union all
select * from tb where u is null order by datediff(day,t,,getdate())
select 1 ,'2011-06-10','2011-06-20' union all
select 2 ,'2011-06-10','2011-06-21' union all
select 3 ,'2011-06-09',null union all
select 4 ,'2011-06-15','2011-06-02'select *
from a
order by datediff(dd,isnull(u,t),getdate())/*
id t u
----------- ----------------------------------------------------
2 2011-06-10 00:00:00.000 2011-06-21 00:00:00.000
1 2011-06-10 00:00:00.000 2011-06-20 00:00:00.000
3 2011-06-09 00:00:00.000 NULL
4 2011-06-15 00:00:00.000 2011-06-02 00:00:00.000(所影响的行数为 4 行)*/