表:test
字段:id,start,end
记录为:
5,90,100
4,80,150
3,160,200
2,170,180
1,151,400
要求是:如果每两条记录start和end的范围值有交集或子集,那么就保留id最大的记录。 最终需要得到的结果是两条记录:
5,90,100
3,160,200
字段:id,start,end
记录为:
5,90,100
4,80,150
3,160,200
2,170,180
1,151,400
要求是:如果每两条记录start和end的范围值有交集或子集,那么就保留id最大的记录。 最终需要得到的结果是两条记录:
5,90,100
3,160,200
ID Start End
1 50 100
2 80 120
3 110 150
怎么取呢?
1和2存在交集
2和3存在交集
1和3没有任何关系
declare @test table([id] int,[start] int,[end] int)
Insert @test
select 5,90,100 union all
select 4,80,150 union all
select 3,160,200 union all
select 2,170,180 union all
select 1,151,400Select a.* from @test a
inner join (select id=[id]+1,[start],[end] from @test where [id]%2=0) b on b.[id] = a.[id]
where (a.[start] <=b.[end] )or (a.[end]>=b.[start])
or (b.[start]<=a.[end]) or (b.[end] >=a.[start])
/*
id start end
----------- ----------- -----------
5 90 100
3 160 200
*/
declare @MAX_id int
declare @tmp_start int
declare @tmp_end int
select * , cast( '0' as char(1)) as f into #aa
from test while(select 1 from #aa where f='0')
begin
select @MAX_id=max(id) from #aa where f='0'
update #aa
set f='1'
where id=@MAX_id
select @tmp_start=start,@tmp_end=[end]
from #aa where id=@MAX_iddelete #aa
where f='0'
and ((start>=@tmp_start and start<=@tmp_end)
or([end]>=@tmp_start and [end]<=@tmp_end))endselect * from #aa
declare @MAX_id int
declare @tmp_start int
declare @tmp_end int
select * , cast( '0' as char(1)) as f into #aa
from test while(select 1 from #aa where f='0')
begin
select @MAX_id=max(id) from #aa where f='0'
update #aa
set f='1'
where id=@MAX_id
select @tmp_start=start,@tmp_end=[end]
from #aa where id=@MAX_iddelete #aa
where f='0'
and ((start>=@tmp_start and start<=@tmp_end)
or([end]>=@tmp_start and [end]<=@tmp_end)
or (start<=@tmp_start and [end]>=@tmp_end) )//子集
endselect * from #aa刚少了个条件 子集
为什么b表中要加入[id]%2=0的过滤条件呢
DECLARE @test TABLE(
id int, start int, [end] int)
INSERT @test
SELECT 5,90,100 UNION ALL
SELECT 4,80,150 UNION ALL
SELECT 3,160,200 UNION ALL
SELECT 2,170,180 UNION ALL
SELECT 1,151,400 -- 查询
SELECT * FROM @test A
WHERE NOT EXISTS(
SELECT * FROM @test B
WHERE id > A.id
AND(
B.start <= A.start AND B.[end] > A.start
OR
B.start > A.start AND A.[end] > B.start)
)
SELECT * FROM @test A
WHERE NOT EXISTS(
SELECT * FROM @test B
WHERE id > A.id
AND(
B.start <= A.start AND B.[end] >= A.start
OR
B.start > A.start AND A.[end] >= B.start)
)
start int,
[end] int )
insert into test
values(5,90,100)insert into test
values(4,80,150)insert into test
values(3,160,200)insert into test
values(2,170,180)insert into test
values(1,151,400)declare @MAX_id int
declare @tmp_start int
declare @tmp_end int
select * , cast( '0' as char(1)) as f into #aa
from test while(exists(select 1 from #aa where f='0'))
begin
select @MAX_id=max(id) from #aa where f='0'
update #aa
set f='1'
where id=@MAX_id
select @tmp_start=start,@tmp_end=[end]
from #aa where id=@MAX_iddelete #aa
where f='0'
and ((start>=@tmp_start and start<=@tmp_end)
or([end]>=@tmp_start and [end]<=@tmp_end)
or (start<=@tmp_start and [end]>=@tmp_end))endselect * from #aa
(2 行受影响)
id start end f
----------- ----------- ----------- ----
5 90 100 1
3 160 200 1
select @maxid=max(id) from test
set @tempvalue=0
set @status=0
while (@maxid>1)
begin
set @tempid=@maxid-1
while (@tempid>=1)
begin
select @tempvalue=(a.start-b.start)*(a.[end]-b.[end]) from (select * from test where id=@maxid) as a,(select * from test where id=@tempid) as b --这里是主逻辑判断,等同于a.start>=b.start and a.end>=b.end or a.start<=b.start and a.end<=b.end ,逻辑可以修改
if @tempvalue>=0
begin
set @status=1
break
end
set @tempid=@tempid-1
end
if @status=1 break
set @maxid=@maxid-1
end
if @status=1
begin
select * from test where id=@maxid or id=@tempid
end
else
begin
select top 1 * from test order by id desc
end
2,15,30
3,11,13
4,22,25這樣是 1&2, 1&3, 2&4相交,不知道LZ要的結果是??
你的要求
要求是:如果每两条记录start和end的范围值有交集或子集,那么就保留id最大的记录。
应给是3和4 啊
是不是还有条件没有写出来
--不知道对不对,随便写了个,稍微测了下还OK--建立測試環境
create table test(id int,
start int,
[end] int )
insert into test
values(11,90,100)
insert into test
values(10,80,150)
insert into test
values(9,160,200)
insert into test
values(8,170,180)
insert into test
values(7,151,400)
--
insert into test
values(6,31,32)
insert into test
values(5,40,50)
insert into test
values(4,22,33)
insert into test
values(3,11,13)
insert into test
values(2,15,23)
insert into test
values(1,10,20)
GO--建立usp
Create proc usp_test
as
select * into #test from testwhile ( select count(*) from #test B where exists(select 1 from #test where (id!=B.id) and ((start>=B.start and start<=B.[end]) or (start<=B.start and [end]>=B.start)) ) )>0
begin
update A
set start=(select min(start) from #test where (start>=A.start and start<=A.[end])
or (start<=A.start and [end]>=A.start)
),
[end]=(select max([end]) from #test where (start>=A.start and start<=A.[end])
or (start<=A.start and [end]>=A.start)
)
from #test A delete #test where exists(select 1 from #test a where a.start=#test.start and a.[end]=#test.[end] and a.id>#test.id)endselect * from test where id in(select id from #test)
GO--check result
exec usp_test
/*
id start end
----------- ----------- -----------
11 90 100
9 160 200
6 31 32
5 40 50
*/--刪除測試環境
drop table test
drop proc usp_test
按需求,直接update扩大了同组边界,直接检索同组边界就行了。
我开始还想,第一次遍历,分好组,一串一组,然后,去每组最大id。但速度应该不如此方法吧。不去试了