排序
表:
create table T_Event
(
EventIDintidentity primary key,
EventStartDatevarchar(20),
EventStartHourvarchar(10),
EventStartMinutevarchar(10),
EventEndDatevarchar(20),
EventEndHourvarchar(10),
EventEndMinutevarchar(10),
EventDescriptionvarchar(250),
)
go

记录:
8852007-09-255 am002007-09-256 am15
7672007-09-258 pm302007-09-2510 pm00
7682007-09-257 pm002007-09-258 pm00
7692007-09-2510 pm002007-09-2511 pm00
7462007-09-265 am002007-09-266 am00
6502007-09-265 am002007-09-2612 am00
9692007-09-265 am202007-09-267 am00


所有记录的起止时间都在范围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)