/* CREATE TABLE [dbo].[ts_tgroup_tb4]( [a] [varchar](10) NULL, [b] [varchar](10) NULL ) ON [PRIMARY]GO insert into [ts_tgroup_tb4] select 'A1','A2' union all select 'A2','A3' union all select 'A3','A4' union all select 'B1','B2' union all select 'B2','B3' union all select 'A4','B3' union all select 'B3','C3' go drop function fs_get_downrecord go create function fs_get_downrecord(@str varchar(20)) returns @rettb table(a varchar(10),b varchar(10),lv int) as begin ;with cte as ( select a,b,1 as lv from ts_tgroup_tb4 where a = @str union all select a.a,a.b,b.lv + 1 from ts_tgroup_tb4 a join cte b on a.a = b.b where a.a is not null ) insert into @rettb select * from cte return end go */declare @instr varchar(10) declare @sqlstr varchar(1000) set @instr = 'A2,B2' set @sqlstr = 'select * ' set @sqlstr = @sqlstr + 'from dbo.fs_get_downrecord(''' +REPLACE(@instr,',',''') union all select * from dbo.fs_get_downrecord(''') + ''')' exec(@sqlstr)/********************************a b lv ---------- ---------- ----------- A2 A3 1 A3 A4 2 A4 B3 3 B3 C3 4 B2 B3 1 B3 C3 2(6 row(s) affected) 不知道楼主要怎么个拼接的,如果 A2,B2 这里数量不定的话写函数,然后把得到的结果拼接下字符串。
tt 表中是无向图中的路径吧,否则select 'B2','B3'的次序要反过来的。 所以需要正反两个方向都检测。DROP PROCEDURE sp_FindPath GO CREATE PROCEDURE sp_FindPath @Start varchar(10), @Finish varchar(10) ASDECLARE @Found int DECLARE @NewCount intDROP TABLE Map CREATE TABLE Map( N varchar(10) NULL, Path varchar(1000) NULL )INSERT INTO Map VALUES (@Start,@Start)SET @Found = 0 WHILE (@Found = 0) BEGIN SET @NewCount = 0 INSERT INTO Map SELECT tt.b, M.Path+'-'+tt.b FROM tt JOIN Map As M ON M.N=tt.a WHERE NOT EXISTS (SELECT * FROM Map t WHERE t.N=tt.b) SET @NewCount = @NewCount + @@ROWCOUNT INSERT INTO Map SELECT tt.a, M.Path+'-'+tt.a FROM tt JOIN Map As M ON M.N=tt.b WHERE NOT EXISTS (SELECT * FROM Map t WHERE t.N=tt.a) SET @NewCount = @NewCount + @@ROWCOUNT IF (@NewCount=0) BREAK
SELECT @Found=Count(*) FROM Map WHERE N=@Finish
END SELECT Path FROM Map WHERE N=@Finish GOsp_FindPath 'A2','B2'
A2-A3-A4-B3-B2--> 请问结果是如何推算出来的?
那输入a='B2'这有事啥情况?
/*
CREATE TABLE [dbo].[ts_tgroup_tb4](
[a] [varchar](10) NULL,
[b] [varchar](10) NULL
) ON [PRIMARY]GO
insert into [ts_tgroup_tb4]
select 'A1','A2' union all
select 'A2','A3' union all
select 'A3','A4' union all
select 'B1','B2' union all
select 'B2','B3' union all
select 'A4','B3' union all
select 'B3','C3'
go
drop function fs_get_downrecord
go
create function fs_get_downrecord(@str varchar(20))
returns @rettb table(a varchar(10),b varchar(10),lv int)
as
begin
;with cte as
(
select a,b,1 as lv from ts_tgroup_tb4 where a = @str
union all
select a.a,a.b,b.lv + 1
from ts_tgroup_tb4 a join cte b on a.a = b.b
where a.a is not null
)
insert into @rettb select * from cte
return
end
go
*/declare @instr varchar(10)
declare @sqlstr varchar(1000)
set @instr = 'A2,B2'
set @sqlstr = 'select * '
set @sqlstr = @sqlstr + 'from dbo.fs_get_downrecord('''
+REPLACE(@instr,',',''') union all select * from dbo.fs_get_downrecord(''')
+ ''')'
exec(@sqlstr)/********************************a b lv
---------- ---------- -----------
A2 A3 1
A3 A4 2
A4 B3 3
B3 C3 4
B2 B3 1
B3 C3 2(6 row(s) affected)
不知道楼主要怎么个拼接的,如果 A2,B2 这里数量不定的话写函数,然后把得到的结果拼接下字符串。
所以需要正反两个方向都检测。DROP PROCEDURE sp_FindPath
GO
CREATE PROCEDURE sp_FindPath
@Start varchar(10),
@Finish varchar(10)
ASDECLARE @Found int
DECLARE @NewCount intDROP TABLE Map
CREATE TABLE Map(
N varchar(10) NULL,
Path varchar(1000) NULL
)INSERT INTO Map VALUES (@Start,@Start)SET @Found = 0
WHILE (@Found = 0)
BEGIN SET @NewCount = 0 INSERT INTO Map
SELECT tt.b, M.Path+'-'+tt.b
FROM tt
JOIN Map As M
ON M.N=tt.a
WHERE NOT EXISTS (SELECT *
FROM Map t
WHERE t.N=tt.b)
SET @NewCount = @NewCount + @@ROWCOUNT INSERT INTO Map
SELECT tt.a, M.Path+'-'+tt.a
FROM tt
JOIN Map As M
ON M.N=tt.b
WHERE NOT EXISTS (SELECT *
FROM Map t
WHERE t.N=tt.a)
SET @NewCount = @NewCount + @@ROWCOUNT IF (@NewCount=0) BREAK
SELECT @Found=Count(*)
FROM Map
WHERE N=@Finish
END SELECT Path
FROM Map
WHERE N=@Finish
GOsp_FindPath 'A2','B2'