排序
表:
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'的记录
则将此记录放在当天所有记录之前


------解决方案--------------------
select 
* 
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)
------解决方案--------------------

SQL code

--应该是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)