我搞了半天还是不行:
表有2个日期字段startDate(开始日期)和endDate(结束日期)
我要的结果是:
未过期的总体排在已过期的前面(endDate>getDate()),
未过期的内部排列是:按照order by startDate asc
已过期的内部排列是:按照order by startDate desc,
表有2个日期字段startDate(开始日期)和endDate(结束日期)
我要的结果是:
未过期的总体排在已过期的前面(endDate>getDate()),
未过期的内部排列是:按照order by startDate asc
已过期的内部排列是:按照order by startDate desc,
from (select top 100 percent * from 表名 where endDate> getDate() order by startDate) as t1
union all
select *
from (select top 100 percent * from 表名 where endDate<= getDate() order by startDate desc) as t2
union all
select * from test where endDate< getDate() order by startDate desc 这样应该行,你试试
CASE WHEN endDate>=GETDATE() THEN 0 ELSE 1 END,
CASE WHEN endDATE>=GETDATE() THEN startDATE ELSE DATEDIFF(mi,startDate,GETDATE()) END
insert into @tb select 1,'手机','2007-12-10 15:44:41.530','2007-12-15 15:44:41.530'
insert into @tb select 1,'电脑','2007-12-11 15:44:41.530','2007-12-15 15:44:41.530'
insert into @tb select 1,'机箱','2007-12-12 15:44:41.530','2007-12-18 15:44:41.530'
insert into @tb select 1,'键盘','2007-12-13 15:44:41.530','2007-12-19 15:44:41.530'
insert into @tb select 1,'鼠标','2007-12-14 15:44:41.530','2007-12-11 15:44:41.530'select * from @tb order by
case when enddate >=getdate() then 1 else 0 end,
case when enddate >=getdate() then datediff(ss,startdate,getdate()) else startdate end1 手机 2007-12-10 15:44:41.530 2007-12-15 15:44:41.530
1 电脑 2007-12-11 15:44:41.530 2007-12-15 15:44:41.530
1 鼠标 2007-12-14 15:44:41.530 2007-12-11 15:44:41.530
1 键盘 2007-12-13 15:44:41.530 2007-12-19 15:44:41.530
1 机箱 2007-12-12 15:44:41.530 2007-12-18 15:44:41.530
你写的 第一个排序列条件的话,要加 desc.是你写反了,哈哈.
你看看你在五楼的结果集就知道了,呵呵
开始时间为 2007-11-11,结束时间为 2007-11-20
另一个活动
开始时间为 2007-12-11,结束时间为 2007-12-20当前时间为 2007-12-15对比当前时间,你说哪个是过期的,哪个是未过期的?
弄清楚了哪个过期,哪个未过期,那么取startdate,还是取datediff... 就清楚多了.
呵呵.
按照你的这个方法,内部的排序不能按照要求排序.
select * from(select a.*,'1' as 'pr' from tableName a where endDate>=getDate() union all select b.*,'2' as 'pr' from tableName b where endDate<getDate())c order by pr asc,startDate asc
如何对pr=2的部分实行order by startDate desc ?
union all
select * from tab1 where sertDate>endDate order by desc