迭归的应用
aw511(点点星灯)
有如下信息：
起始地 目的地 距离（公里）
A B 1000
A C 1100
A D 900
A E 400
B D 300
D F 600
E A 400
F G 1000
C B 600
请用SQL语句或一段代码写出从A出发，可达到的目的地（包括间接方式）。
--测试数据
create table t
(st varchar(20),ed varchar(20),km int)
go
insert t values ('A','B',1000)
insert t values ('A','C',1100)
insert t values ('A','D',900)
insert t values ('A','E',400)
insert t values ('B','D',300)
insert t values ('D','F',600)
insert t values ('E','A',400)
insert t values ('F','G',1000)
insert t values ('C','B',600)
go
--显示插入值
--select * from t
--go
--创建函数
--函数返回一个表，根据实际情况的不同一层一层的插入，可以充分利用生成的表
create function f_go(@col varchar(10))
returns @t table(col varchar(30),st varchar(20),ed varchar(20),km int,level int)
as
begin
declare @i int
set @i=1
insert @t select st+'-'+ed,*,@i from t where st=@col
while exists (select * from t a,@t b where
b.ed=a.st and b.level=@i and b.ed<>@col )
begin
set @i=@i+1
insert @t
select b.col+'-'+a.ed,a.st,a.ed,b.km+a.km,@i from t a,@t b
where b.level=@i-1 and b.ed=a.st and b.ed<>@col
end
return
end
go
--调用
--select * from dbo.f_go('A')
select col,km from dbo.f_go('A')
--删除环境
drop function f_go
drop table t
--结果
col km
------------------------------ -----------
A-B 1000
A-C 1100
A-D 900
A-E 400
A-B-D 1300
A-C-B 1700
A-D-F 1500
A-E-A 800
A-C-B-D 2000
A-B-D-F 1900
A-D-F-G 2500
A-C-B-D-F 2600
A-B-D-F-G 2900
A-C-B-D-F-G 3600
（所影响的行数为 14 行）
--给个例子,仅供参考
libin_ftsafe(子陌红尘)
--测试数据
create table test(username varchar(10),guanlian varchar(10),add_time datetime)
insert into test select 'B','A','2005-12-22'
insert into test select 'C','B','2005-12-22'
insert into test select 'D','C','2005-12-22'
insert into test select 'E','D','2005-12-22'
insert into test select 'F','D','2005-12-22'
insert into test select 'G','F','2005-12-22'
go
--创建存储过程
create procedure sp_test(@username varchar(20))
as
begin
declare @t table(username varchar(10),level int)
declare @level int
set @level = 1
insert into @t
select username, @level from test where guanlian=@username
union
select guanlian,-@level from test where username=@username
while @@rowcount<>0
begin
set @level=@level+1
insert into @t
select a.username, @level from test a,@t b where a.guanlian=b.username and b.level=@level-1
union
select a.guanlian,-@level from test a,@t b where a.username=b.username and b.level=1-@level
end
select
direct =sum(case when abs(level)=1 then 1 else 0 end),
indirect=sum(case when abs(level)>1 then 1 else 0 end)
from @t
end
go
--执行存储过程
exec sp_test 'B'
go
--删除测试数据
drop procedure sp_test
drop table test
go
--执行结果
/*
direct indirect
--------------------
2 4
*/