Create Table #temp(AName int ,AStart int , AEnd int )
INSERT into #temp(AName ,AStart , AEnd )
SELECT 1 , 10 , 13 union ALL
SELECT 1 , 13 , 15 union ALL
SELECT 1 , 14 , 17 union ALL --這行與上一行相交,要把這兩行都提取出來SELECT 2 , 1 , 4 union ALL
SELECT 2 , 1 , 6 union ALL
SELECT 2 , 10 , 11 union ALL
SELECT 2 , 11 , 14 union ALL
SELECT 2 , 9 , 11 SELECT * FROM #temp drop table #temp同一AName, AStart , AEnd 不能出現相交,求一SQL 語句,找出相交的行。
INSERT into #temp(AName ,AStart , AEnd )
SELECT 1 , 10 , 13 union ALL
SELECT 1 , 13 , 15 union ALL
SELECT 1 , 14 , 17 union ALL --這行與上一行相交,要把這兩行都提取出來SELECT 2 , 1 , 4 union ALL
SELECT 2 , 1 , 6 union ALL
SELECT 2 , 10 , 11 union ALL
SELECT 2 , 11 , 14 union ALL
SELECT 2 , 9 , 11 SELECT * FROM #temp drop table #temp同一AName, AStart , AEnd 不能出現相交,求一SQL 語句,找出相交的行。
from #tmep A
where exists (select 1 from #temp B where A.Aname=B.Aname and( A.Astart between B.Astart and B.Aend) or(A.Aend betwwen B.Astart and B.Aend))
結果:
AName ,AStart , AEnd
1 13 15
1 14 17
2 1 4
2 1 6
2 10 11
2 9 11
INSERT into #temp(AName ,AStart , AEnd )
SELECT 1 , 10 , 13 union ALL
SELECT 1 , 13 , 15 union ALL
SELECT 1 , 14 , 17 union ALL --這行與上一行相交,要把這兩行都提取出來SELECT 2 , 1 , 4 union ALL
SELECT 2 , 1 , 6 union ALL
SELECT 2 , 10 , 11 union ALL
SELECT 2 , 11 , 14 union ALL
SELECT 2 , 9 , 11 SELECT m.* FROM #temp m, #temp n
where m.AName = n.AName and (
(m.AStart > n.AStart and m.AStart < n.AEnd) or
(m.AEnd > n.AStart and m.AEnd < n.AEnd) or
(n.AStart > m.AStart and n.AStart < m.AEnd) or
(n.AEnd > m.AStart and n.AEnd < m.AEnd)
)
drop table #temp/*
AName AStart AEnd
----------- ----------- -----------
1 14 17
1 13 15
2 1 6
2 1 4
2 9 11
2 10 11(所影响的行数为 6 行)
*/
go
Create Table #temp(AName int ,AStart int , AEnd int )
go
INSERT into #temp(AName ,AStart , AEnd )
SELECT 1 , 10 , 13 union ALL
SELECT 1 , 13 , 15 union ALL
SELECT 1 , 14 , 17 union ALL --這行與上一行相交,要把這兩行都提取出來SELECT 2 , 1 , 4 union ALL
SELECT 2 , 1 , 6 union ALL
SELECT 2 , 10 , 11 union ALL
SELECT 2 , 11 , 14 union ALL
SELECT 2 , 9 , 11
/*
相交的几种情况
1.完全被包含,或包含其它记录
2.相交,2种情况 左边或右边
*/
SELECT * FROM #temp as a
where exists(select * from #temp where aname=a.aname
and (astart<>a.astart or aend<>a.aend) --排除自己
and (astart>=a.astart and aend<=a.aend --前2句为包含与被包含,后2句为相交
or astart<=a.astart and aend>=a.aend
or a.astart<astart and a.aend>astart
or a.aend>aend and a.astart<aend)
)
go
drop table #temp