有一张表设计大概是这样:
ID Start End Groups每一行中Start和End表示下图中的一个线段,Groups表示这些数据属于一组,下图中的线段都属于同一组数据。
我想要用SQL实现这样的效果,只取出图中没有任何其他线段与其重叠的部分,也就是“4~8”,“10~20”,“45~49”,“55~60”,“65~75”,“78~18”这些。现在不知道这么用SQL实现,希望大家能够提供一些好的意见,谢谢!
ID Start End Groups每一行中Start和End表示下图中的一个线段,Groups表示这些数据属于一组,下图中的线段都属于同一组数据。
我想要用SQL实现这样的效果,只取出图中没有任何其他线段与其重叠的部分,也就是“4~8”,“10~20”,“45~49”,“55~60”,“65~75”,“78~18”这些。现在不知道这么用SQL实现,希望大家能够提供一些好的意见,谢谢!
如果还是看不到... 麻烦使用以下链接查看图片:
http://hi.csdn.net/attachment/201102/28/2853790_129887016104BQ.jpg或者
http://hiphotos.baidu.com/wtyeh/pic/item/b5c1da5452cc740bd00906dc.jpg谢谢!!!
create or replace procedure SORTDATA is
v_start integer:=0;
v_end integer:=0;
v_start_temp integer:=0;
v_end_temp integer:=0;
cursor C_SORT is
select ISTART,IEND from test order by ISTART;
begin
execute immediate 'truncate table SORTRESULT';
open C_SORT;
fetch C_SORT into v_start,v_end;
loop
fetch C_SORT into v_start_temp,v_end_temp;
exit when C_SORT%notfound;
if v_start_temp>=v_end then
insert into SORTRESULT(ISTART,IEND) values(v_start,v_end);
commit;
v_start:=v_start_temp;
v_end:=v_end_temp;
else
if v_start_temp>v_start and v_end_temp>=v_end then
insert into SORTRESULT(ISTART,IEND) values(v_start,v_start_temp);
commit;
v_start:=v_end;
v_end:=v_end_temp;
else
if v_start_temp>=v_start and v_end_temp<=v_end then
insert into SORTRESULT(ISTART,IEND) values(v_start,v_start_temp);
commit;
v_start:=v_start_temp;
else
if v_start_temp<v_start and v_end_temp<=v_end then
v_start:=v_end_temp;
else
if v_start_temp<v_start and v_end_temp>v_end then
v_end:=v_end_temp;
end if;
end if;
end if;
end if;
end if;
end loop;
commit;
close C_SORT;
end SORTDATA;
insert #TryTable(Start,[End],Groups) select 4,8,1 union all
select 10,28,1 union all
select 20,45,1 union all
select 23,45,1 union all
select 33,55,1 union all
select 49,78,1 union all
select 60,65,1 union all
select 75,100,1create table #Te (Start int,[End] int, flag int)create proc ProcA
@OStart int,
@OEnd int
as
DECLARE @curTemp CURSOR
SET @curTemp=CURSOR FOR Select Start,[end] FROM #Te where flag=1
OPEN @curTemp
DECLARE @Start as int,@end as int
FETCH NEXT FROM @curTemp INTO @Start,@end
WHILE @@FETCH_STATUS = 0
BEGIN
insert #Te select cStart ,cEnd,2 from [dbo].[chaji](@Start,@end,@OStart,@OEnd)
FETCH NEXT FROM @curTemp INTO @Start,@end
END
CLOSE @curTemp
DEALLOCATE @curTemp
delete from #Te where flag=1
update #Te set flag=1 where flag=2create function [dbo].[chaji]
(
@SStart int,
@SEnd int,
@OStart int,
@OEnd int
)
returns @t table(cStart int,cEnd int)
as
begin
if @SStart=@OStart and @SEnd=@OEnd goto ReturnR if (@SStart between @OStart and @OEnd) and (@SEnd between @OStart and @OEnd) goto ReturnR if @SEnd<=@OStart or @SStart>=@OEnd
begin
insert @t values (@SStart,@SEnd)
goto ReturnR
end if (@OStart between @SStart and @SEnd) and (@OEnd between @SStart and @SEnd)
begin
insert @t values(@SStart,@OStart)
insert @t values(@OEnd,@SEnd)
goto ReturnR
end if (@SStart between @OStart and @OEnd) and @OStart<@SStart
begin
insert @t values(@OEnd,@SEnd)
goto ReturnR
end
if (@OStart between @SStart and @SEnd) and @OEnd>@SEnd
begin
insert @t values(@SStart,@OStart)
goto ReturnR
end
ReturnR:
delete from @t where cStart=cEnd
RETURN
enddelete from #Te
DECLARE @curTemp CURSOR
DECLARE @curTempIn CURSOR
SET @curTemp=CURSOR FOR Select id,Start,[end] FROM #TryTable
SET @curTempIn=CURSOR FOR Select id, Start,[end] FROM #TryTable
OPEN @curTemp
DECLARE @Start as int,@end as int,@id as int
FETCH NEXT FROM @curTemp INTO @id, @Start,@end
WHILE @@FETCH_STATUS = 0
BEGIN
insert #Te select @Start,@End,1
OPEN @curTempIn
DECLARE @inStart as int,@inEnd as int,@inid as int
FETCH NEXT FROM @curTempIn INTO @inid, @inStart,@inEnd
WHILE @@FETCH_STATUS = 0
BEGIN
if @inid<>@id exec ProcA @inStart,@inEnd
FETCH NEXT FROM @curTempIn INTO @inid, @inStart,@inEnd
END
CLOSE @curTempIn
update #Te set flag=3 where flag=1
FETCH NEXT FROM @curTemp INTO @id,@Start,@end
END
CLOSE @curTemp
DEALLOCATE @curTemp
select Start,[End] from #Te order by Start