aw511(点点星灯)有如下信息：起始地 目的地 距离（公里）A B 1000A C 1100A D 900A E 400B D 300D F 600E A 400F G 1000C B 600请用SQL语句或一段代码写出从A出发，可达到的目的地（包括间接方式）。
--测试数据create table t (st varchar(20),ed varchar(20),km int)goinsert 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)asbegin 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 returnendgo
--调用--select * from dbo.f_go('A')select col,km from dbo.f_go('A')
--删除环境drop function f_godrop table t
--结果col km------------------------------ -----------A-B 1000A-C 1100A-D 900A-E 400A-B-D 1300A-C-B 1700A-D-F 1500A-E-A 800A-C-B-D 2000A-B-D-F 1900A-D-F-G 2500A-C-B-D-F 2600A-B-D-F-G 2900A-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))asbegin 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 @tendgo
--执行存储过程exec sp_test 'B'go
--删除测试数据drop procedure sp_testdrop table testgo
--执行结果/*direct indirect--------------------2 4*/
