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表) tselect * into #u from A表 where 1=2declare @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 endclose ap deallocate ap -- 结果 select * from #u/* ID Start End ---------- ----------- ----------- 李四 1 7 李四 8 9(2 row(s) affected) */
A表 ID Start End
李四 1 2
李四 2 5
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表) tselect * into #u
from A表
where 1=2declare @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
endclose ap
deallocate ap
-- 结果
select * from #u/*
ID Start End
---------- ----------- -----------
李四 1 7
李四 8 9(2 row(s) affected)
*/
A表里三个字段
ID START END
1 2 4
2 1 4
3 1 5
1 3 9
2 5 10
3 4 9这种情况 如何取相同ID的并集?