select posttime,newday from [table] order by (case when datediff(day,getdate(),posttime+newday)>0 then 1 else 0 end) desc,posttime+newday desc
newday 是什么数据 举个例子
--楼主的有效期指什么? 是一个时间值,还是一个天数?--如果是天数,可以用: select * from 表 order by case when postTime>dateadd(day,-cast(newday as int),getdate()) then 1 else 0 end ,postTime --如果有效期是指一个日期值,则用: select * from 表 order by case when newday>getdate() then 1 else 0 end ,postTime
如果postTime和newday類型和模式一樣,這個可以 select * from t order by case when postTime<=newday then 0 else 1 end,postTime
select * from t order by case when datediff(day,cast(newday as int),postTime)<getdate() then 0 else 1 end,postTime
--那这个可以了select * from 表 order by case when postTime>dateadd(day,-cast(newday as int),getdate()) then 1 else 0 end ,postTime
用個具體時間做例子吧,先把過期時間排在后面﹐然后再按時間排序select * from tb order by case when postTime<='2004/08/10' then 0 else 1 end,postTime
谢谢 zjcxc(邹建) ,谢谢各位
declare @tb1 table(posttime datetime,newday varchar(20)) insert into @tb1 select '2004-10-26','30' union all select '2004-10-27','10' union all select '2004-10-28','50' union all select '2004-10-23','70' union all select '2004-09-15','20' select * from @tb1 order by case when postTime>dateadd(day,-cast(newday as int),getdate()) then 1 else 0 end desc ,postTime /* posttime newday ------------------------------------------------------ -------------------- 2004-10-23 00:00:00.000 70 2004-10-26 00:00:00.000 30 2004-10-27 00:00:00.000 10 2004-10-28 00:00:00.000 50 2004-09-15 00:00:00.000 20(所影响的行数为 5 行) */
[table] order by (case when datediff(day,getdate(),posttime+newday)>0 then 1 else 0 end) desc,posttime+newday desc
--楼主的有效期指什么? 是一个时间值,还是一个天数?--如果是天数,可以用:
select * from 表
order by case when postTime>dateadd(day,-cast(newday as int),getdate()) then 1 else 0 end
,postTime
--如果有效期是指一个日期值,则用:
select * from 表
order by case when newday>getdate() then 1 else 0 end
,postTime
select * from t order by case when postTime<=newday then 0 else 1 end,postTime
标题 postTime(时间) newday (字符串)求购信息 2004-10-23 30求购信息 2004-8-23 10
order by case when postTime>dateadd(day,-cast(newday as int),getdate()) then 1 else 0 end
,postTime
insert into @tb1
select '2004-10-26','30' union all
select '2004-10-27','10' union all
select '2004-10-28','50' union all
select '2004-10-23','70' union all
select '2004-09-15','20'
select * from @tb1
order by case when postTime>dateadd(day,-cast(newday as int),getdate()) then 1 else 0 end desc ,postTime
/*
posttime newday
------------------------------------------------------ --------------------
2004-10-23 00:00:00.000 70
2004-10-26 00:00:00.000 30
2004-10-27 00:00:00.000 10
2004-10-28 00:00:00.000 50
2004-09-15 00:00:00.000 20(所影响的行数为 5 行)
*/