if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] int,[starttime] datetime,[endtime] datetime) insert [tb] select 1,'2011-6-19 08:30:00','2011-6-19 09:30:00' union all select 2,'2011-6-20 08:40:00','2011-6-20 08:50:00' union all select 3,'2011-6-20 10:30:00','2011-6-20 11:10:00' union all select 4,'2011-6-20 14:00:00','2011-6-20 15:00:00' union all select 5,'2011-6-20 14:30:00','2011-6-20 15:20:00' union all select 6,'2011-6-21 18:30:00','2011-6-21 19:10:00' union all select 7,'2011-6-21 18:30:00','2011-6-21 19:10:00' union all select 8,'2011-6-22 15:20:00','2011-6-22 16:10:00' union all select 9,'2011-6-22 16:00:00','2011-6-22 16:50:00'
select *, flag=case when exists(select 1 from tb where id!=t.id and (starttime>=t.starttime and starttime<=t.endtime or starttime<=t.starttime and endtime>=t.starttime)) then 'false' else '' end from tb t /** id starttime endtime flag ----------- ----------------------- ----------------------- ----- 1 2011-06-19 08:30:00.000 2011-06-19 09:30:00.000 2 2011-06-20 08:40:00.000 2011-06-20 08:50:00.000 3 2011-06-20 10:30:00.000 2011-06-20 11:10:00.000 4 2011-06-20 14:00:00.000 2011-06-20 15:00:00.000 false 5 2011-06-20 14:30:00.000 2011-06-20 15:20:00.000 false 6 2011-06-21 18:30:00.000 2011-06-21 19:10:00.000 false 7 2011-06-21 18:30:00.000 2011-06-21 19:10:00.000 false 8 2011-06-22 15:20:00.000 2011-06-22 16:10:00.000 false 9 2011-06-22 16:00:00.000 2011-06-22 16:50:00.000 false(9 行受影响) **/
简化一下 select *, flag=case when exists(select 1 from tb where id!=t.id and not (endtime<t.starttime or starttime>t.endtime )) then 'false' else '' end from tb t
go
create table [tb]([id] int,[starttime] datetime,[endtime] datetime)
insert [tb]
select 1,'2011-6-19 08:30:00','2011-6-19 09:30:00' union all
select 2,'2011-6-20 08:40:00','2011-6-20 08:50:00' union all
select 3,'2011-6-20 10:30:00','2011-6-20 11:10:00' union all
select 4,'2011-6-20 14:00:00','2011-6-20 15:00:00' union all
select 5,'2011-6-20 14:30:00','2011-6-20 15:20:00' union all
select 6,'2011-6-21 18:30:00','2011-6-21 19:10:00' union all
select 7,'2011-6-21 18:30:00','2011-6-21 19:10:00' union all
select 8,'2011-6-22 15:20:00','2011-6-22 16:10:00' union all
select 9,'2011-6-22 16:00:00','2011-6-22 16:50:00'
select *,
flag=case when
exists(select 1 from tb where id!=t.id and (starttime>=t.starttime and starttime<=t.endtime or starttime<=t.starttime and endtime>=t.starttime))
then 'false' else ''
end
from tb t
/**
id starttime endtime flag
----------- ----------------------- ----------------------- -----
1 2011-06-19 08:30:00.000 2011-06-19 09:30:00.000
2 2011-06-20 08:40:00.000 2011-06-20 08:50:00.000
3 2011-06-20 10:30:00.000 2011-06-20 11:10:00.000
4 2011-06-20 14:00:00.000 2011-06-20 15:00:00.000 false
5 2011-06-20 14:30:00.000 2011-06-20 15:20:00.000 false
6 2011-06-21 18:30:00.000 2011-06-21 19:10:00.000 false
7 2011-06-21 18:30:00.000 2011-06-21 19:10:00.000 false
8 2011-06-22 15:20:00.000 2011-06-22 16:10:00.000 false
9 2011-06-22 16:00:00.000 2011-06-22 16:50:00.000 false(9 行受影响)
**/
简化一下
select *,
flag=case when
exists(select 1 from tb where id!=t.id and not (endtime<t.starttime or starttime>t.endtime ))
then 'false' else ''
end
from tb t