存储过程输入一个1
返回
pid cid usage bomlevel
1 6 1 0
1 2 1 0
6 7 1 1
2 3 2 1
3 5 2 2
CREATE TABLE [dbo].[TestTb](
[pId] [int],
[cId] [int],
[usage] [int]
) ON [PRIMARY]
select * from [TestTb]
insert into [TestTb]( [pId],[cId] ,[usage] )values(1,6,1)
insert into [TestTb]( [pId],[cId] ,[usage] )values(1,2,1)
insert into [TestTb]( [pId],[cId] ,[usage] )values(2,3,2)
insert into [TestTb]( [pId],[cId] ,[usage] )values(4,2,1)
insert into [TestTb]( [pId],[cId] ,[usage] )values(3,5,2)
insert into [TestTb]( [pId],[cId] ,[usage] )values(6,7,1)
select * from [TestTb]
------解决思路----------------------
--2005以上版本
with t as
(select pid as Fid,pid,cid,usage,0 as bomlevel from TestTb where pId=1
union all
select a.Fid,b.pid,b.cid,b.usage,a.bomlevel+1 from t a,TestTb b where a.cId=b.pId)
select pId,cId,usage,bomlevel from t where Fid=1 order by bomlevel,pid
--------------------------------------------------------------------------------------------------------------------------
1 6 1 0
1 2 1 0
2 3 2 1
6 7 1 1
3 5 2 2
------解决思路----------------------
CREATE PROCEDURE GetBomInfo你参考下
@pId INT
AS
BEGIN
;WITH CTETB AS(
SELECT A.*,0 bomlevel,'-'+CAST(A.[pId]AS VARCHAR(8000))+'--'+CAST(A.[cId]AS VARCHAR(10))+'-' [PATH] FROM[TestTb]A LEFT JOIN[TestTb]B ON A.[pId]=B.[cId]WHERE B.[cId]IS NULL
UNION ALL
SELECT A.*,B.bomlevel+1,B.[PATH]+'-'+CAST(A.[cId]AS VARCHAR(10))+'-' FROM [TestTb] A JOIN CTETB B ON A.[pId]=B.[cId]
)
SELECT T1.* FROM CTETB T1 LEFT JOIN CTETB T2 ON T1.[PATH]LIKE T2.[PATH]+'%'WHERE T2.pid=@pId
END
EXEC GetBomInfo 1