SQL对BOM多级展开实现

来源：互联网发布：js 除以2 编辑：程序博客网时间：2024/05/22 13:37

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

代码直接粘贴就可以使用

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

0 0