22,210
社区成员
发帖
与我相关
我的任务
分享
create table TA(ID1 numeric(18,0), ID2 numeric(18,0), dist float)
insert into TA
select 10001, 11101, 0.01
union all select 10001, 11102, 0.21
union all select 10001, 11103, 0.31
union all select 10001, 11104, 0.41
union all select 10002, 11101, 0.12
union all select 10002, 11102, 0.32
union all select 10002, 11103, 0.52
union all select 10002, 11104, 0.72
union all select 10003, 11101, 0.23
union all select 10003, 11102, 0.43
union all select 10003, 11103, 0.63
union all select 10003, 11104, 0.83
union all select 10004, 11101, 0.64
union all select 10004, 11102, 0.44
union all select 10004, 11103, 0.24
union all select 10004, 11104, 0.14
create proc proc_ta
as
declare @ID1 numeric(18,0),@ID2 numeric(18,0),@dist float
begin
if exists (select * from dbo.sysobjects
where id = object_id(N'#ta') and xtype = 'U')
drop table #ta
create table #ta(tmp_ID1 numeric(18,0),tmp_ID2 numeric(18,0),tmp_dist float)
declare ta_cursor cursor for
select ID1,ID2,dist
from TA
order by dist
open ta_cursor
fetch next from ta_cursor
into @ID1,@ID2,@dist
while @@fetch_status = 0
begin
if not exists(select 1 from #ta where tmp_ID1 = @ID1)
begin
if not exists(select 1 from #ta where tmp_ID2 = @ID2)
insert into #ta(tmp_ID1,tmp_ID2,tmp_dist)
select @ID1,@ID2,@dist
end
fetch next from ta_cursor
into @ID1,@ID2,@dist
end
close ta_cursor
deallocate ta_cursor
select * from #ta
end
exec proc_ta
declare @T table (ID1 numeric(18,0),ID2 numeric(18,0),dist decimal(18,2))
insert @T
select 10001,1110001,0.0
UNION ALL select 10002,1110003,1.0
UNION ALL select 10003,1110004,3.0
UNION ALL select 10004,1110005,10.0
UNION ALL select 10008,1110007,0.5
UNION ALL select 10005,1110003,0.8
UNION ALL select 10007,1110002,0.2
UNION ALL select 10009,1110008,7.0
UNION ALL select 10008,1110006,0.8
UNION ALL select 10006,1110007,0.51
/*
最后结果应该是
10001, 1110001, 0
10007, 1110002, 0.2
10008, 1110007, 0.5
10005, 1110003, 0.8
10003, 1110004, 3
10009, 1110008, 7
10004, 1110005, 10
*/
select * from @T a where not exists (select 1 from @T where dist<a.dist and (ID1=a.ID1 or ID2=a.ID2)) order by dist
/*
ID1 ID2 dist
-------------------- -------------------- --------------------
10001 1110001 .00
10007 1110002 .20
10008 1110007 .50
10005 1110003 .80
10003 1110004 3.00
10009 1110008 7.00
10004 1110005 10.00
*/