2、有如下信息: 起始地 目的地 距离(公里) 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) goselect * 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 行)--给个例子,仅供参考
function sjiangjin(id,zdate) dim rs000 dim rs111 dim rs222 dim rs333 dim rs444 dim rs555 dim a dim b dim c dim i dim j u=0 dim rs00 set rs000=server.createobject("adodb.recordset") rs000.open "select * from user_info where id="&trim(id),c1,1,1 set rs00=server.createobject("adodb.recordset") rs00.open "select * from user_info where guanlian='"&trim(rs000("user_name"))&"'",c1,1,1 set rs111=server.createobject("adodb.recordset") set rs222=server.createobject("adodb.recordset") set rs333=server.createobject("adodb.recordset") set rs444=server.createobject("adodb.recordset") set rs555=server.createobject("adodb.recordset") rs111.open "select * from user_info where guanlian='"&trim(rs000("user_name"))&"' and datediff(d,add_time,'"&zdate&"')>=0",c1,1,1 if rs00.recordcount<10 then sjiangjin=0 else for i=1 to rs111.recordcount u=u+1 rs222.open "select * from user_info where guanlian='"&trim(rs111("user_name"))&"' and datediff(d,add_time,'"&zdate&"')>=0",c1,1,1 if rs222.recordcount<>0 then for j=1 to rs222.recordcount u=u+1 rs333.open "select * from user_info where guanlian='"&trim(rs222("user_name"))&"' and datediff(d,add_time,'"&zdate&"')>=0",c1,1,1 if rs333.recordcount<>0 then for a=1 to rs333.recordcount u=u+1 rs444.open "select * from user_info where guanlian='"&trim(rs333("user_name"))&"' and datediff(d,add_time,'"&zdate&"')>=0",c1,1,1 if rs444.recordcount<>0 then for b=1 to rs444.recordcount u=u+1 rs555.open "select * from user_info where guanlian='"&trim(rs444("user_name"))&"' and datediff(d,add_time,'"&zdate&"')>=0",c1,1,1 if rs555.recordcount<>0 then for c=1 to rs555.recordcount u=u+1 rs555.movenext next end if rs555.close rs444.movenext next end if rs444.close rs333.movenext next end if rs333.close rs222.movenext next end if rs222.close rs111.movenext next sjiangjin=u rs111.close end if set rs00=nothing set rs000=nothing set rs111=nothing set rs222=nothing set rs333=nothing set rs444=nothing set rs555=nothing end function大家帮忙看看,我想把这段代码写成存储过程,大虾请看看,谢谢各位,很着急!
测试数据表userinfo username guanlian add_time B A 2005-12-22 C B 2005-12-22 D C 2005-12-22 E D 2005-12-22 F D 2005-12-22 G F 2005-12-22算出和B直接相关和间接相关的数据条数。利用存储过程怎么去写啊!
--生成测试数据 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--执行结果 /* direct indirect -------------------- 2 4 */--删除测试数据 drop procedure sp_test drop table test go
--建立测试数据 select * into userinfo from ( select username = 'B',guanlian = 'A',add_time = '2005-12-22' union select 'C','B','2005-12-22' union select 'D','C','2005-12-22' union select 'E','D','2005-12-22' union select 'F','D','2005-12-22' union select 'G','F','2005-12-22' ) info go--建立展开存储过程 create procedure p_userinfoexpand @username varchar(20) as begin select * into #userinfo from userinfo where guanlian = @username while(exists (select b.* from #userinfo a,userinfo b where a.username = b.guanlian and not exists(select * from #userinfo c where b.username = c.username and b.guanlian = c.guanlian))) begin insert #userinfo select b.* from #userinfo a,userinfo b where a.username = b.guanlian and not exists(select * from #userinfo c where b.username = c.username and b.guanlian = c.guanlian) end select * from #userinfo end go--调用 exec p_userinfoexpand 'b'--删除测试数据 drop table userinfo drop procedure p_userinfoexpand
起始地 目的地 距离(公里)
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)
goselect * 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 行)--给个例子,仅供参考
dim rs000
dim rs111
dim rs222
dim rs333
dim rs444
dim rs555
dim a
dim b
dim c
dim i
dim j
u=0
dim rs00
set rs000=server.createobject("adodb.recordset")
rs000.open "select * from user_info where id="&trim(id),c1,1,1
set rs00=server.createobject("adodb.recordset")
rs00.open "select * from user_info where guanlian='"&trim(rs000("user_name"))&"'",c1,1,1
set rs111=server.createobject("adodb.recordset")
set rs222=server.createobject("adodb.recordset")
set rs333=server.createobject("adodb.recordset")
set rs444=server.createobject("adodb.recordset")
set rs555=server.createobject("adodb.recordset")
rs111.open "select * from user_info where guanlian='"&trim(rs000("user_name"))&"' and datediff(d,add_time,'"&zdate&"')>=0",c1,1,1
if rs00.recordcount<10 then
sjiangjin=0
else
for i=1 to rs111.recordcount
u=u+1
rs222.open "select * from user_info where guanlian='"&trim(rs111("user_name"))&"' and datediff(d,add_time,'"&zdate&"')>=0",c1,1,1
if rs222.recordcount<>0 then
for j=1 to rs222.recordcount
u=u+1
rs333.open "select * from user_info where guanlian='"&trim(rs222("user_name"))&"' and datediff(d,add_time,'"&zdate&"')>=0",c1,1,1
if rs333.recordcount<>0 then
for a=1 to rs333.recordcount
u=u+1
rs444.open "select * from user_info where guanlian='"&trim(rs333("user_name"))&"' and datediff(d,add_time,'"&zdate&"')>=0",c1,1,1
if rs444.recordcount<>0 then
for b=1 to rs444.recordcount
u=u+1
rs555.open "select * from user_info where guanlian='"&trim(rs444("user_name"))&"' and datediff(d,add_time,'"&zdate&"')>=0",c1,1,1
if rs555.recordcount<>0 then
for c=1 to rs555.recordcount
u=u+1
rs555.movenext
next
end if
rs555.close
rs444.movenext
next
end if
rs444.close
rs333.movenext
next
end if
rs333.close
rs222.movenext
next
end if
rs222.close
rs111.movenext
next
sjiangjin=u
rs111.close
end if
set rs00=nothing
set rs000=nothing
set rs111=nothing
set rs222=nothing
set rs333=nothing
set rs444=nothing
set rs555=nothing
end function大家帮忙看看,我想把这段代码写成存储过程,大虾请看看,谢谢各位,很着急!
username guanlian add_time
B A 2005-12-22
C B 2005-12-22
D C 2005-12-22
E D 2005-12-22
F D 2005-12-22
G F 2005-12-22算出和B直接相关和间接相关的数据条数。利用存储过程怎么去写啊!
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--执行结果
/*
direct indirect
--------------------
2 4
*/--删除测试数据
drop procedure sp_test
drop table test
go
select *
into userinfo
from
(
select username = 'B',guanlian = 'A',add_time = '2005-12-22'
union
select 'C','B','2005-12-22'
union
select 'D','C','2005-12-22'
union
select 'E','D','2005-12-22'
union
select 'F','D','2005-12-22'
union
select 'G','F','2005-12-22'
) info
go--建立展开存储过程
create procedure p_userinfoexpand
@username varchar(20)
as
begin
select *
into #userinfo
from userinfo
where guanlian = @username while(exists (select b.* from #userinfo a,userinfo b where a.username = b.guanlian and
not exists(select * from #userinfo c where b.username = c.username and b.guanlian = c.guanlian)))
begin
insert #userinfo
select b.* from #userinfo a,userinfo b
where a.username = b.guanlian and
not exists(select * from #userinfo c where b.username = c.username and b.guanlian = c.guanlian)
end
select * from #userinfo
end
go--调用
exec p_userinfoexpand 'b'--删除测试数据
drop table userinfo
drop procedure p_userinfoexpand