有一张表设计大概是这样:
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
谢谢!!!
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
谢谢!!!
首先最好是用存储过程declare @start int
declare @end int
declare @lastEnd int
set @lastEnd = 0create table #tbl (start int,end int)Declare cur Cursor
for select start,end from tablename
Open cur
Fetch Next From cur Into @start,@endWHILE(@@FETCH_STATUS = 0)
BEGIN
if @lastEnd < @start
begin
insert into #tbl values(@start,@end)
set @lastEnd = @start
end
endselect * from #tbl
drop table #tblclose cur我时间很赶,还帮你写了这个东东,对不对也给个分啊,谢谢了
declare @end int
declare @lastEnd int
set @lastEnd = 0create table #tbl (start int,end int)Declare cur Cursor
for select start,end from tablename
Open cur
Fetch Next From cur Into @start,@endWHILE(@@FETCH_STATUS = 0)
BEGIN
if @lastEnd < @start
begin
insert into #tbl values(@start,@end)
set @lastEnd = @start
end
Fetch Next From cur Into @start,@end
endselect * from #tbl
drop table #tblclose cur
NumTab
Num
1
2
3
....select groups,b.num from yourtab a left join numtab b on b.num between a.start and a.end
group by groups,b.num
where count(*)=1大概这意思吧,
create table #t(ID int, Start int, [End] int, Groups int)
insert #t select 1, 4, 8, 1
insert #t select 2, 10, 28, 2
insert #t select 3, 20, 45, 3
insert #t select 4, 23, 45, 4
insert #t select 5, 33, 55, 5
insert #t select 6, 49, 78, 6
insert #t select 7, 60, 65, 7
insert #t select 8, 75, 100, 8
gowith t1 as
(
select b.number
from #t a join (select number from master..spt_values where type='P') b on b.number between a.start and a.[end]
group by b.number
having count(*)=1
union
select start from #t a where (select count(1) from #t b where a.start between b.start and b.[end]) = 2
union
select [end] from #t a where (select count(1) from #t b where a.[end] between b.start and b.[end]) = 2
)
select min(number),max(number)
from (
select id=row_number() over(order by number), number from t1
) a
group by id-number
order by id-number desc/*
----------- -----------
4 8
10 20
46 49
55 60
65 75
78 100(6 行受影响)
*/
不要说你用的是2000....