order by 排序问题 ,在线等~(完美解决的满分送)
表结构:
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 ...........
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
SELECT * FROM
(
SELECT id ,
StartTime ,
EndTime ,
( CASE WHEN GETDATE() BETWEEN StartTime AND EndTime THEN 0
WHEN GETDATE() > StartTime THEN 1--这儿有问题吧,应该是: StartTime < GETDATE(),开始时间小于今天
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]
) t
ORDER BY LISTORDER,
CASE
WHEN LISTORDER = 0 THEN DATEDIFF(MINUTE, GETDATE(), EndTime)--正在进行(剩余时间最小的拍前面)
WHEN LISTORDER = 1 THEN DATEDIFF(MINUTE, StartTime, GETDATE())--即将开始(最先即将开始的时间拍前面)
ELSE 0
END