--示例--示例数据
declare @t table(C_id varchar(10),start_time char(5),end_time char(5))
insert @t select 'c01','10:00','13:00'
union all select 'c02','09:00','11:00'
union all select 'c03','12:00','15:00'
union all select 'c04','16:00','17:00'
union all select 'c05','17:30','18:00'--查询
select * from @t a
where exists(
select * from @t
where C_id<>a.C_id and
start_time<a.end_time and end_time>a.start_time)/*--测试结果C_id start_time end_time
---------- ---------- --------
c01 10:00 13:00
c02 09:00 11:00
c03 12:00 15:00(所影响的行数为 3 行)
--*/
declare @t table(C_id varchar(10),start_time char(5),end_time char(5))
insert @t select 'c01','10:00','13:00'
union all select 'c02','09:00','11:00'
union all select 'c03','12:00','15:00'
union all select 'c04','16:00','17:00'
union all select 'c05','17:30','18:00'--查询
select * from @t a
where exists(
select * from @t
where C_id<>a.C_id and
start_time<a.end_time and end_time>a.start_time)/*--测试结果C_id start_time end_time
---------- ---------- --------
c01 10:00 13:00
c02 09:00 11:00
c03 12:00 15:00(所影响的行数为 3 行)
--*/
select *
into temp1
from(
select C_id , start_time time,1 flag from t
union all
select C_id , end_time time,2 flag from t)
对temp1按time列排序,保留序号列
生成表temp2(C_id ,time, flag ,orderid)通过比较orderid可以找出那些是冲突的,自己再想想。
where exists(
select C_id from table
where C_id<>a.C_id and
start_time<a.end_time and end_time>a.start_time)