declare @t table(开始时间 datetime, 结束时间 datetime) insert @T select '2006-3-3 7:00:00', '2006-3-3 9:30:00' union all select '2006-3-3 8:00:00', '2006-3-3 9:40:00' union all select '2006-3-3 8:30:00', '2006-3-3 10:30:00' union all select '2006-3-3 10:00:00', '2006-3-3 11:30:00' union all select '2006-3-3 10:00:00', '2006-3-3 12:30:00' union all select '2006-3-2 7:00:00', '2006-3-2 9:30:00' union all select '2006-3-1 7:00:00', '2006-3-1 9:30:00' declare @i datetime,@n int select @i='2006-03-03 09:00:00',@n=1select *,col1=1,col2=identity(int,1,1) into #w from @t where 结束时间>@i select *,col1=2,col2=identity(int,1,1) into #j from @t where 结束时间<@iselect 开始时间 , 结束时间 from ( select * from #w union all select * from #j )h order by col1,col2drop table #w drop table #j用了两个排序列
declare @t table(开始时间 datetime,结束时间 datetime)insert into @t select '2006-03-03 07:00:00','2006-03-03 09:30:00' insert into @t select '2006-03-03 08:00:00','2006-03-03 09:40:00' insert into @t select '2006-03-03 08:30:00','2006-03-03 10:30:00' insert into @t select '2006-03-03 10:00:00','2006-03-03 11:30:00' insert into @t select '2006-03-03 10:00:00','2006-03-03 12:30:00' insert into @t select '2006-03-02 07:00:00','2006-03-02 09:30:00' insert into @t select '2006-03-01 07:00:00','2006-03-01 09:30:00'declare @d datetime set @d='2006-3-3 9:00:00'select * from @t order by case when 结束时间>=@d then 1 when 开始时间>=@d then 2 else 3 end, case when 开始时间>=@d or 结束时间>=@d then 开始时间 end, case when 开始时间>=@d or 结束时间>=@d then 结束时间 end, case when 开始时间<@d and 结束时间<@d then 开始时间 end desc, case when 开始时间<@d and 结束时间<@d then 结束时间 end desc/* 开始时间 结束时间 ----------------------- ----------------------- 2006-03-03 07:00:00.000 2006-03-03 09:30:00.000 2006-03-03 08:00:00.000 2006-03-03 09:40:00.000 2006-03-03 08:30:00.000 2006-03-03 10:30:00.000 2006-03-03 10:00:00.000 2006-03-03 11:30:00.000 2006-03-03 10:00:00.000 2006-03-03 12:30:00.000 2006-03-02 07:00:00.000 2006-03-02 09:30:00.000 2006-03-01 07:00:00.000 2006-03-01 09:30:00.000 */
insert @T
select '2006-3-3 7:00:00', '2006-3-3 9:30:00' union all
select '2006-3-3 8:00:00', '2006-3-3 9:40:00' union all
select '2006-3-3 8:30:00', '2006-3-3 10:30:00' union all
select '2006-3-3 10:00:00', '2006-3-3 11:30:00' union all
select '2006-3-3 10:00:00', '2006-3-3 12:30:00' union all
select '2006-3-2 7:00:00', '2006-3-2 9:30:00' union all
select '2006-3-1 7:00:00', '2006-3-1 9:30:00' declare @i datetime,@n int
select @i='2006-03-03 09:00:00',@n=1select *,col1=1,col2=identity(int,1,1) into #w from @t where 结束时间>@i
select *,col1=2,col2=identity(int,1,1) into #j from @t where 结束时间<@iselect 开始时间 , 结束时间 from
(
select * from #w
union all
select * from #j
)h
order by col1,col2drop table #w
drop table #j用了两个排序列
insert into @t select '2006-03-03 08:00:00','2006-03-03 09:40:00'
insert into @t select '2006-03-03 08:30:00','2006-03-03 10:30:00'
insert into @t select '2006-03-03 10:00:00','2006-03-03 11:30:00'
insert into @t select '2006-03-03 10:00:00','2006-03-03 12:30:00'
insert into @t select '2006-03-02 07:00:00','2006-03-02 09:30:00'
insert into @t select '2006-03-01 07:00:00','2006-03-01 09:30:00'declare @d datetime
set @d='2006-3-3 9:00:00'select * from @t
order by
case when 结束时间>=@d then 1 when 开始时间>=@d then 2 else 3 end,
case when 开始时间>=@d or 结束时间>=@d then 开始时间 end,
case when 开始时间>=@d or 结束时间>=@d then 结束时间 end,
case when 开始时间<@d and 结束时间<@d then 开始时间 end desc,
case when 开始时间<@d and 结束时间<@d then 结束时间 end desc/*
开始时间 结束时间
----------------------- -----------------------
2006-03-03 07:00:00.000 2006-03-03 09:30:00.000
2006-03-03 08:00:00.000 2006-03-03 09:40:00.000
2006-03-03 08:30:00.000 2006-03-03 10:30:00.000
2006-03-03 10:00:00.000 2006-03-03 11:30:00.000
2006-03-03 10:00:00.000 2006-03-03 12:30:00.000
2006-03-02 07:00:00.000 2006-03-02 09:30:00.000
2006-03-01 07:00:00.000 2006-03-01 09:30:00.000
*/
---------------------------------------------------------------------------------------------------------
变量@d只是为了模拟一个既定的系统时间,在真实环境下把@d替换成getdate()即可。