求时间相交记录的SQL语句

[SS_]

myTable记录如下，StartDate、EndDate为日期型数据， 同一条记录EndDate>StartDate

ID MID StartDate EndDate
1 111 2009-1-1 2009-1-10
2 111 2009-1-11 2009-1-20

3 222 2009-1-1 2009-1-15

4 333 2009-1-1 2009-1-10
5 333 2009-1-1 2009-1-15
6 333 2009-1-15 2009-1-20

7 444 2009-1-1 2009-1-15
8 444 2009-1-5 2009-1-10

9 555 2009-1-5 2009-1-10
10 555 2009-1-15 2009-1-20
11 555 2009-1-25 2009-1-30

说明：
111 相关的2条数据日期不相交，排除
222 单条记录可以排除
333 的4、5记录日期相交，故将所有333记录列出
444 2条记录相交，2条件记录列出
555 所有记录不相交，排除

需要的结果就是：
4 333 2009-1-1 2009-1-10
5 333 2009-1-1 2009-1-15
6 333 2009-1-15 2009-1-20
7 444 2009-1-1 2009-1-15
8 444 2009-1-5 2009-1-10

请问，这条SQL语句怎么写？

[黄_瓜]

去试试

[Yang_]

select * from myTable a
where exists (
  select 1 from myTable b
  where b.MID = a.MID
  and (a.StartDate between b.StartDate and b.EndDate or a.endDate between b.StartDate and b.EndDate or b.StartDate between a.StartDate and a.EndDate or b.endDate between a.StartDate and a.EndDate)
  )

[xxmsuper]

select * from myTable
where MID in
(
select MID
from myTable A
inner join myTable B
on A.MID=B.MID and A.ID<>B.ID
where A.StartDate<=B.StartDate and
A.EndDate>=B.StartDate
)

[sdhdy]

--try:
select * from tb a where exists
(select 1 from tb where mid=a.mid and ID<>a.ID and 
((StartDate between a.StartDate and a.EndDate and EndDate between a.StartDate and a.EndDate) 
or (a.StartDate between StartDate and EndDate and a.EndDate between StartDate and EndDate)))

[黄_瓜]

---------------------------------
--  Author: Beirut(贝鲁特)
--  Comment:小爱
--  Date  : 2009-08-04 10:39:14
---------------------------------

if object_id('tb') is not null drop table tb
go
create table tb(id int,mid int,StartDate datetime,EndDate datetime)
go
insert into tb select 1,  111, '2009-1-1',  '2009-1-10' 
union all select 2,  111 , '2009-1-11 ',  ' 2009-1-20' 

union all select 3,  222, ' 2009-1-1 ',  ' 2009-1-15 '

union all select 4,  333, ' 2009-1-1',  '  2009-1-10 '
union all select 5,  333, ' 2009-1-1',  '  2009-1-15' 
union all select 6,  333, ' 2009-1-15',  '  2009-1-20' 

union all select 7,  444, ' 2009-1-1',  '  2009-1-15' 
union all select 8,  444, ' 2009-1-5',  '  2009-1-10' 

union all select 9,  555, ' 2009-1-5 ',  ' 2009-1-10' 
union all select 10, 555, ' 2009-1-15 ',  ' 2009-1-20' 
union all select 11, 555, ' 2009-1-25',  '  2009-1-30'
-------------------------------------------------------------
select * from tb a
where mid in
(
select a.mid
from tb A
inner join tb B
on A.MID=B.MID and A.ID<>B.ID
where A.StartDate<=B.StartDate and
A.EndDate>=B.StartDate
)
/*id          mid         StartDate               EndDate
----------- ----------- ----------------------- -----------------------
4           333         2009-01-01 00:00:00.000 2009-01-10 00:00:00.000
5           333         2009-01-01 00:00:00.000 2009-01-15 00:00:00.000
6           333         2009-01-15 00:00:00.000 2009-01-20 00:00:00.000
7           444         2009-01-01 00:00:00.000 2009-01-15 00:00:00.000
8           444         2009-01-05 00:00:00.000 2009-01-10 00:00:00.000

(5 行受影响)*/

[xxmsuper]

declare @t table(id int,mid int,startdate datetime,enddate datetime)
insert @t
select 1,  111, '2009-1-1' , '2009-1-10' union all
select 2,  111, '2009-1-11',  '2009-1-20' union all
select 3,  222, '2009-1-1' , '2009-1-15' union all 
select 4,  333, '2009-1-1' , '2009-1-10' union all 
select 5,  333, '2009-1-1' , '2009-1-15' union all 
select 6,  333, '2009-1-15',  '2009-1-20' union all 
select 7,  444, '2009-1-1' , '2009-1-15' union all 
select 8,  444, '2009-1-5',  '2009-1-10' union all 
select 9,  555, '2009-1-5' ,'2009-1-10' union all 
select 10, 555, '2009-1-15',  '2009-1-20' union all
select 11, 555, '2009-1-25' , '2009-1-30'

select * from @t
where MID in
(
select A.MID
from @t A
inner join @t B
on A.MID=B.MID and A.ID<>B.ID
where A.StartDate<=B.StartDate and
A.EndDate>=B.StartDate
)

id          mid         startdate               enddate
----------- ----------- ----------------------- -----------------------
4           333         2009-01-01 00:00:00.000 2009-01-10 00:00:00.000
5           333         2009-01-01 00:00:00.000 2009-01-15 00:00:00.000
6           333         2009-01-15 00:00:00.000 2009-01-20 00:00:00.000
7           444         2009-01-01 00:00:00.000 2009-01-15 00:00:00.000
8           444         2009-01-05 00:00:00.000 2009-01-10 00:00:00.000

[xiequan2]

declare @t table(id int,mid int,StartDate datetime,enddate datetime)
insert into @t
select 1,  111, '2009-1-1',  '2009-1-10' union all
select 2,  111, '2009-1-11' , '2009-1-20' union all
select 3 , 222, '2009-1-1' , '2009-1-15' union all
select 4 , 333 ,'2009-1-1' , '2009-1-10' union all
select 5 ,333 ,'2009-1-1' , '2009-1-15' union all
select 6 , 333 ,'2009-1-15' , '2009-1-20' union all
select 7 , 444, '2009-1-1' , '2009-1-15' union all
select 8 , 444 ,'2009-1-5' , '2009-1-10' union all
select 9 , 555 ,'2009-1-5' , '2009-1-10' union all
select 10 , 555, '2009-1-15' , '2009-1-20' union all
select 11 , 555 ,'2009-1-25' , '2009-1-30' 

 
select * from @t where mid in
(
	select mid
    from @T t 
	where  exists(select 1 from @T 
	where  datediff(day,(select top 1 enddate from @t where mid=t.mid and id=t.id-1),t.startdate)<0)
)
/*
id          mid         StartDate               enddate
----------- ----------- ----------------------- -----------------------
4           333         2009-01-01 00:00:00.000 2009-01-10 00:00:00.000
5           333         2009-01-01 00:00:00.000 2009-01-15 00:00:00.000
6           333         2009-01-15 00:00:00.000 2009-01-20 00:00:00.000
7           444         2009-01-01 00:00:00.000 2009-01-15 00:00:00.000
8           444         2009-01-05 00:00:00.000 2009-01-10 00:00:00.000

(5 行受影响)

*/

[sdhdy]

declare @myTable table(ID int, MID int,StartDate datetime, EndDate datetime)

insert @myTable select 1,  111 ,'2009-1-1'  ,'2009-1-10' 
insert @myTable select 2 , 111 ,'2009-1-11'  ,'2009-1-20' 

insert @myTable select 3 , 222 ,'2009-1-1'  ,'2009-1-15' 

insert @myTable select 4 , 333 ,'2009-1-1'  ,'2009-1-10' 
insert @myTable select 5,  333 ,'2009-1-1'  ,'2009-1-15' 
insert @myTable select 6 , 333 ,'2009-1-15'  ,'2009-1-20' 

insert @myTable select 7 , 444 ,'2009-1-1'  ,'2009-1-15' 
insert @myTable select 8 , 444 ,'2009-1-5'  ,'2009-1-10' 

insert @myTable select 9 , 555 ,'2009-1-5'  ,'2009-1-10' 
insert @myTable select 10, 555 ,'2009-1-15'  ,'2009-1-20' 
insert @myTable select 11, 555 ,'2009-1-25'  ,'2009-1-30' 
/*
说明： 
111 相关的2条数据日期不相交，排除 
222 单条记录可以排除 
333 的4、5记录日期相交，故将所有333记录列出 
444 2条记录相交，2条件记录列出 
555 所有记录不相交，排除 

需要的结果就是： 
4  333 2009-1-1  2009-1-10 
5  333 2009-1-1  2009-1-15 
6  333 2009-1-15  2009-1-20 
7  444 2009-1-1  2009-1-15 
8  444 2009-1-5  2009-1-10 
*/
--try:
select * from @myTable
where MID in
(
select A.MID
from @myTable A,@myTable B
where A.MID=B.MID and A.ID<>B.ID and A.StartDate<=B.StartDate and A.EndDate>=B.StartDate 
)
/*
ID          MID         StartDate                                              EndDate                                                
----------- ----------- ------------------------------------------------------ ------------------------------------------------------ 
4           333         2009-01-01 00:00:00.000                                2009-01-10 00:00:00.000
5           333         2009-01-01 00:00:00.000                                2009-01-15 00:00:00.000
6           333         2009-01-15 00:00:00.000                                2009-01-20 00:00:00.000
7           444         2009-01-01 00:00:00.000                                2009-01-15 00:00:00.000
8           444         2009-01-05 00:00:00.000                                2009-01-10 00:00:00.000

（所影响的行数为 5 行）
*/

[sdhdy]

declare @myTable table(ID int, MID int,StartDate datetime, EndDate datetime)

insert @myTable select 1,  111 ,'2009-1-1'  ,'2009-1-10' 
insert @myTable select 2 , 111 ,'2009-1-11'  ,'2009-1-20' 

insert @myTable select 3 , 222 ,'2009-1-1'  ,'2009-1-15' 

insert @myTable select 4 , 333 ,'2009-1-1'  ,'2009-1-10' 
insert @myTable select 5,  333 ,'2009-1-1'  ,'2009-1-15' 
insert @myTable select 6 , 333 ,'2009-1-15'  ,'2009-1-20' 

insert @myTable select 7 , 444 ,'2009-1-1'  ,'2009-1-15' 
insert @myTable select 8 , 444 ,'2009-1-5'  ,'2009-1-10' 

insert @myTable select 9 , 555 ,'2009-1-5'  ,'2009-1-10' 
insert @myTable select 10, 555 ,'2009-1-15'  ,'2009-1-20' 
insert @myTable select 11, 555 ,'2009-1-25'  ,'2009-1-30' 

--try:
select id,mid,StartDate=convert(varchar(10),StartDate,120),EndDate=convert(varchar(10),EndDate,120) from @myTable
where MID in
(
select A.MID
from @myTable A,@myTable B
where A.MID=B.MID and A.ID<>B.ID and A.StartDate<=B.StartDate and A.EndDate>=B.StartDate 
)
/*
id          mid         StartDate  EndDate    
----------- ----------- ---------- ---------- 
4           333         2009-01-01 2009-01-10
5           333         2009-01-01 2009-01-15
6           333         2009-01-15 2009-01-20
7           444         2009-01-01 2009-01-15
8           444         2009-01-05 2009-01-10

（所影响的行数为 5 行）
*/

[feixianxxx]

if object_id('tb') is not null drop table tb
go
create table tb(id int,mid int,StartDate datetime,EndDate datetime)
go
insert into tb select 1,  111, '2009-1-1',  '2009-1-10' 
union all select 2,  111 , '2009-1-11 ',  ' 2009-1-20' 

union all select 3,  222, ' 2009-1-1 ',  ' 2009-1-15 '

union all select 4,  333, ' 2009-1-1',  '  2009-1-10 '
union all select 5,  333, ' 2009-1-1',  '  2009-1-15' 
union all select 6,  333, ' 2009-1-15',  '  2009-1-20' 

union all select 7,  444, ' 2009-1-1',  '  2009-1-15' 
union all select 8,  444, ' 2009-1-5',  '  2009-1-10' 

union all select 9,  555, ' 2009-1-5 ',  ' 2009-1-10' 
union all select 10, 555, ' 2009-1-15 ',  ' 2009-1-20' 
union all select 11, 555, ' 2009-1-25',  '  2009-1-30'
-------------------------------------------------------------
select * from tb a
where mid in
(
select mid
from tb A
where exists(select * from  tb b 
where B.mid= a.mid and  a.ID<>B.ID and A.StartDate<=B.StartDate and
A.EndDate>=B.StartDate
))
/*

(11 行受影响)
id          mid         StartDate               EndDate
----------- ----------- ----------------------- -----------------------
4           333         2009-01-01 00:00:00.000 2009-01-10 00:00:00.000
5           333         2009-01-01 00:00:00.000 2009-01-15 00:00:00.000
6           333         2009-01-15 00:00:00.000 2009-01-20 00:00:00.000
7           444         2009-01-01 00:00:00.000 2009-01-15 00:00:00.000
8           444         2009-01-05 00:00:00.000 2009-01-10 00:00:00.000

*/

。

[zjb211434]

declare @tb table(id int,mid int,StartDate datetime,EndDate datetime)
insert into @tb select 1, 111, '2009-1-1', '2009-1-10'
union all select 2, 111 , '2009-1-11 ', ' 2009-1-20'

union all select 3, 222, ' 2009-1-1 ', ' 2009-1-15 '

union all select 4, 333, ' 2009-1-1', ' 2009-1-10 '
union all select 5, 333, ' 2009-1-1', ' 2009-1-15'
union all select 6, 333, ' 2009-1-15', ' 2009-1-20'

union all select 7, 444, ' 2009-1-1', ' 2009-1-15'
union all select 8, 444, ' 2009-1-5', ' 2009-1-10'

union all select 9, 555, ' 2009-1-5 ', ' 2009-1-10'
union all select 10, 555, ' 2009-1-15 ', ' 2009-1-20'
union all select 11, 555, ' 2009-1-25', ' 2009-1-30'

select distinct a.* from @tb a inner join @tb b on a.mid=b.mid where a.startdate<=b.enddate and a.enddate>=b.startdate and a.id<>b.id

select * from @tb a where exists(select top 1 * from @tb b where a.startdate<=b.enddate and a.enddate>=b.startdate and a.mid = b.mid and a.id<>b.id)

[zjb211434]

两个时间段存在交叉的充分必要条件是：a.startDate <= b.endDate and a.endDate >= b.startDate

[华夏小卒]

declare @a table(id int,mid varchar(12),startdate datetime,enddate datetime)
insert @a select
1,  '111', '2009-1-1' , '2009-1-10' union all select 
2,  '111', '2009-1-11',  '2009-1-20' union all select
3,  '222', '2009-1-1' , '2009-1-15' union all select
4,  '333', '2009-1-1' , '2009-1-10' union all select
5,  '333', '2009-1-1' , '2009-1-15' union all select
6,  '333', '2009-1-15',  '2009-1-20' union all select
7,  '444', '2009-1-1' , '2009-1-15' union all select
8,  '444', '2009-1-5' , '2009-1-10' union all select
9,  '555', '2009-1-5' , '2009-1-10'union all select
10, '555', '2009-1-15',  '2009-1-20' union all select
11, '555', '2009-1-25',  '2009-1-30' 

select * from @a where mid in(
select mid from @a a
where exists(select 1 from @a where mid=a.mid and id<>a.id and a.startdate between startdate and enddate))

id          mid          startdate                                              enddate                                                
----------- ------------ ------------------------------------------------------ ------------------------------------------------------ 
4           333          2009-01-01 00:00:00.000                                2009-01-10 00:00:00.000
5           333          2009-01-01 00:00:00.000                                2009-01-15 00:00:00.000
6           333          2009-01-15 00:00:00.000                                2009-01-20 00:00:00.000
7           444          2009-01-01 00:00:00.000                                2009-01-15 00:00:00.000
8           444          2009-01-05 00:00:00.000                                2009-01-10 00:00:00.000

（所影响的行数为 5 行）

[SS_]

大体思路上大家都对的，只是对时间相交概念上可能略有偏差，时间相交会有4种情况存在，这里不做赘述，结贴，谢谢大家！