如有三张表A,B,C
A表
ID Start End
李四 1 2
李四 3 5
B表
ID Start End
李四 1 5
C表
ID Start End
李四 5 7
李四 8 9
能否得到
ID Start End
李四 1 7
李四 8 9
------解决方案--------------------
create table A表(ID varchar(10),Start int,[End] int)
create table B表(ID varchar(10),Start int,[End] int)
create table C表(ID varchar(10),Start int,[End] int)
insert into A表
select '李四',1,2 union all
select '李四',2,5
insert into B表
select '李四',1,5
insert into C表
select '李四',5,7 union all
select '李四',8,9
-- 计算过程
select * into #t
from
(select * from A表
union all
select * from B表
union all
select * from C表) t
select * into #u
from A表
where 1=2
declare @id varchar(10),@s int,@e int
declare ap scroll cursor for select * from #t
open ap
fetch first from ap into @id,@s,@e
while(@@fetch_status<>-1)
begin
if not exists(select 1 from #u where ID=@id and Start<=@s and [End]>=@e)
begin
declare @v table(ID varchar(10),Start int,[End] int)
delete from @v
insert into @v select @id,@s,@e
while(@@rowcount>0)
begin
insert into @v
select b.*
from @v a
inner join #t b on a.Start=b.[End] or b.Start=a.[End]
where not exists(select 1 from @v c where c.ID=b.ID and c.Start=b.start and c.[End]=b.[End])
end
insert into #u
select @id,min(Start),max([End]) from @v
end
fetch next from ap into @id,@s,@e
end
close ap
deallocate ap
-- 结果
select * from #u
/*
ID Start End
---------- ----------- -----------
李四 1 7
李四 8 9