SQL对BOM多级展开实现

注：本文代码源自网络，是我学习过程中看到的，写下来以后备用

代码直接粘贴就可以使用

SET NOCOUNT ON; 
USE tempdb; 
GO 
IF OBJECT_ID('dbo.Employees') IS NOT NULL 
DROP TABLE dbo.Employees; 
GO 
CREATE TABLE dbo.Employees 
( 
empid INT NOT NULL PRIMARY KEY, 
mgrid INT NULL REFERENCES dbo.Employees, 
empname VARCHAR(25) NOT NULL, 
salary MONEY NOT NULL, 
CHECK (empid <> mgrid) 
); 
INSERT INTO dbo.Employees(empid, mgrid, empname, salary) 
VALUES(1, NULL, 'David', $10000.00); 
INSERT INTO dbo.Employees(empid, mgrid, empname, salary) 
VALUES(2, 1, 'Eitan', $7000.00); 
INSERT INTO dbo.Employees(empid, mgrid, empname, salary) 
VALUES(3, 1, 'Ina', $7500.00); 
INSERT INTO dbo.Employees(empid, mgrid, empname, salary) 
VALUES(4, 2, 'Seraph', $5000.00); 
INSERT INTO dbo.Employees(empid, mgrid, empname, salary) 
VALUES(5, 2, 'Jiru', $5500.00); 
INSERT INTO dbo.Employees(empid, mgrid, empname, salary) 
VALUES(6, 2, 'Steve', $4500.00); 
INSERT INTO dbo.Employees(empid, mgrid, empname, salary) 
VALUES(7, 3, 'Aaron', $5000.00); 
INSERT INTO dbo.Employees(empid, mgrid, empname, salary) 
VALUES(8, 5, 'Lilach', $3500.00); 
INSERT INTO dbo.Employees(empid, mgrid, empname, salary) 
VALUES(9, 7, 'Rita', $3000.00); 
INSERT INTO dbo.Employees(empid, mgrid, empname, salary) 
VALUES(10, 5, 'Sean', $3000.00); 
INSERT INTO dbo.Employees(empid, mgrid, empname, salary) 
VALUES(11, 7, 'Gabriel', $3000.00); 
INSERT INTO dbo.Employees(empid, mgrid, empname, salary) 
VALUES(12, 9, 'Emilia' , $2000.00); 
INSERT INTO dbo.Employees(empid, mgrid, empname, salary) 
VALUES(13, 9, 'Michael', $2000.00); 
INSERT INTO dbo.Employees(empid, mgrid, empname, salary) 
VALUES(14, 9, 'Didi', $1500.00); 
--创建索引 
CREATE UNIQUE INDEX idx_unc_mgrid_empid ON dbo.Employees(mgrid, empid); 
go 
SELECT * FROM dbo.Employees;

---------------查找所有子节点------------
DECLARE @root AS INT; 
SET @root = 3; 
WITH SubsCTE 
AS 
( 
-- Anchor member returns root node 
SELECT empid, empname, 0 AS lvl 
FROM dbo.Employees 
WHERE empid = @root 

UNION ALL 

-- Recursive member returns next level of children 
SELECT C.empid, C.empname, P.lvl + 1 
FROM SubsCTE AS P 
JOIN dbo.Employees AS C 
ON C.mgrid = P.empid 
) 
SELECT * FROM SubsCTE; 


---------------限制递归的层数------------
--DECLARE @root AS INT; 
--SET @root = 3; 
DECLARE @maxlevels AS INT; 
SET @maxlevels = 5; 


WITH SubsCTE 
AS 
( 
SELECT empid, empname, 0 AS lvl 
FROM dbo.Employees 
--WHERE empid = @root 
UNION ALL 

SELECT C.empid, C.empname, P.lvl + 1
FROM SubsCTE AS P 
JOIN dbo.Employees AS C 
ON C.mgrid = P.empid 
AND P.lvl < @maxlevels -- 这里控制递归数 
) 
SELECT * FROM SubsCTE order by lvl;