CREATE TABLE [tt](
[a] [varchar](10) NULL,
[b] [varchar](10) NULL
) ON [PRIMARY]
insert into [tt]
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','A4'
请问各位大能 如果输入 A2,B2 如何得到 下面的结果
A2-A3-A4-B3-B2
求语句
------解决方案--------------------
楼主最后给的两条数据是不是有点矛盾，互为父子级？
------解决方案--------------------
输入 A2,B2 如何得到 下面的结果
A2-A3-A4-B3-B2
--> 请问结果是如何推算出来的?
------解决方案--------------------
看不懂，是这样么，a 是父级，输入的a='A2‘，找它的子孙=》A2-A3-A4-B3
那输入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 这里数量不定的话写函数，然后把得到的结果拼接下字符串。
------解决方案--------------------
tt 表中是无向图中的路径吧，否则select 'B2','B3'的次序要反过来的。
所以需要正反两个方向都检测。
DROP PROCEDURE sp_FindPath
GO
CREATE PROCEDURE sp_FindPath
@Start varchar(10),
@Finish varchar(10)
AS
DECLARE @Found int
DECLARE @NewCount int
DROP 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