表:
create table T_Event
(
EventID int identity primary key,
EventStartDate varchar(20),
EventStartHour varchar(10),
EventStartMinute varchar(10),
EventEndDate varchar(20),
EventEndHour varchar(10),
EventEndMinute varchar(10),
EventDescription varchar(250),
)
go记录:
885 2007-09-25 5 am 00 2007-09-25 6 am 15
767 2007-09-25 8 pm 30 2007-09-25 10 pm 00
768 2007-09-25 7 pm 00 2007-09-25 8 pm 00
769 2007-09-25 10 pm 00 2007-09-25 11 pm 00
746 2007-09-26 5 am 00 2007-09-26 6 am 00
650 2007-09-26 5 am 00 2007-09-26 12 am 00
969 2007-09-26 5 am 20 2007-09-26 7 am 00
所有记录的起止时间都在范围5 am :00 到 12 am :00 之内以前是通过下句来排序
select * from T_Event order by EventStartDate,cast(EventStartHour as datetime),cast(EventStartMinute as int)现在需要在上面排序的基础上新增一条件:
如果有EventStartHour 为'5 am',EventStartMinute 为'00',EventEndHour 为 '12 am',EventEndMinute 为 '00'的记录
则将此记录放在当天所有记录之前
create table T_Event
(
EventID int identity primary key,
EventStartDate varchar(20),
EventStartHour varchar(10),
EventStartMinute varchar(10),
EventEndDate varchar(20),
EventEndHour varchar(10),
EventEndMinute varchar(10),
EventDescription varchar(250),
)
go记录:
885 2007-09-25 5 am 00 2007-09-25 6 am 15
767 2007-09-25 8 pm 30 2007-09-25 10 pm 00
768 2007-09-25 7 pm 00 2007-09-25 8 pm 00
769 2007-09-25 10 pm 00 2007-09-25 11 pm 00
746 2007-09-26 5 am 00 2007-09-26 6 am 00
650 2007-09-26 5 am 00 2007-09-26 12 am 00
969 2007-09-26 5 am 20 2007-09-26 7 am 00
所有记录的起止时间都在范围5 am :00 到 12 am :00 之内以前是通过下句来排序
select * from T_Event order by EventStartDate,cast(EventStartHour as datetime),cast(EventStartMinute as int)现在需要在上面排序的基础上新增一条件:
如果有EventStartHour 为'5 am',EventStartMinute 为'00',EventEndHour 为 '12 am',EventEndMinute 为 '00'的记录
则将此记录放在当天所有记录之前
*
from
T_Event
order by
EventStartDate,
(case when EventStartHour='5 am ' and EventStartMinute='00 ' and EventEndHour='12 am ' and EventEndMinute='00 ' then 0 else 1 end),
cast(EventStartHour as datetime),cast(EventStartMinute as int)
from T_Event
order by
EventStartDate,
case when (EventStartHour ='5 am' and EventStartMinute='00') or (EventStartHour='12 am' and EventEndMinute='00') then 1 else 2 end,
cast(EventStartHour as datetime),
cast(EventStartMinute as int)
--应该是and的关系。
select *
from T_Event
order by
EventStartDate,
case when (EventStartHour = '5 am ' and EventStartMinute= '00 ' and EventStartHour= '12 am ' and EventEndMinute= '00 ') then 1 else 2 end,
cast(EventStartHour as datetime),
cast(EventStartMinute as int)
子陌红尘 就是牛
select *
from T_Event
order by
EventStartDate,
case when (EventStartHour ='5 am' and EventStartMinute='00' and EventEndHour='12 am' and EventEndMinute='00') then 1 else 2 end,
cast(EventStartHour as datetime),
cast(EventStartMinute as int)
EventEndDate, EventEndHour, EventEndMinute, EventDescription
from (
select *, case when EventStartHour=' 5 am' AND EventStartMinute = '00' AND
EventEndHour = '12 am' AND EventEndMinute = '00' THEN 0 Else 1 END addition from T_Event
)T
order by addition, EventStartDate,cast(EventStartHour as datetime),cast(EventStartMinute as int)