表结构:
ID Productname Startime EndTime
select id,StartTime,EndTime,
(case when getdate() between StartTime and EndTime then 0 when getdate()<StartTime then 1 else 2 end) as LISTORDER, --判断商品即将开始(1)、正在进行中(0)、已经结束(2)
datediff(second,getdate(),EndTime) as dates,
datediff(second,getdate(),StartTime) as dates1
from [ceb_DiHuDB].[dbo].[ceb_P_TuanGou]
order by ...........
排序条件先后顺序是:正在进行(剩余时间最小的拍前面)、即将开始(最先即将开始的时间拍前面)、已经结束
ps:不知道我说的是否明白,是一个商品竞拍的东西
sql
------解决方案--------------------
select id,StartTime,EndTime,
(case when getdate() between StartTime and EndTime then 0 when getdate()<StartTime then 1 else 2 end) as LISTORDER, --判断商品即将开始(1)、正在进行中(0)、已经结束(2)
datediff(second,getdate(),EndTime) as dates,
datediff(second,getdate(),StartTime) as dates1
from [ceb_DiHuDB].[dbo].[ceb_P_TuanGou]
order by 2
???
------解决方案--------------------
select id,StartTime,EndTime,
(case when getdate() between StartTime and EndTime then 0 when getdate()<StartTime then 1 else 2 end) as LISTORDER,
datediff(second,getdate(),EndTime) as dates,
datediff(second,getdate(),StartTime) as dates1
from [ceb_DiHuDB].[dbo].[ceb_P_TuanGou]
order by
case when getdate() between StartTime and EndTime then datediff(second,GETDATE(),StartTime) end ,
case when datediff(day,GETDATE(),StartTime)>=0 then datediff(day,GETDATE(),StartTime) end,
case when datediff(day,GETDATE(),StartTime)<0 then datediff(day,GETDATE(),StartTime) end desc
------解决方案--------------------