T-SQL查询学习笔记——求下属和祖先的算法

构建试验环境：
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

[color=blue]一、下属:[/color]
[color=green]方案一：无级数限制[/color]

CREATE FUNCTION dbo.fn_subordinates1(@root AS INT) RETURNS @Subs Table
(
empid INT NOT NULL PRIMARY KEY NONCLUSTERED,
lvl INT NOT NULL,
UNIQUE CLUSTERED(lvl, empid) -- Index will be used to filter level
)
AS
BEGIN
DECLARE @lvl AS INT;
SET @lvl = 0; -- Initialize level counter with 0

-- Insert root node to @Subs
INSERT INTO @Subs(empid, lvl)
SELECT empid, @lvl FROM dbo.Employees WHERE empid = @root;

WHILE @@rowcount > 0 -- while previous level had rows
BEGIN
SET @lvl = @lvl + 1; -- Increment level counter

-- Insert next level of subordinates to @Subs
INSERT INTO @Subs(empid, lvl)
SELECT C.empid, @lvl
FROM @Subs AS P -- P = Parent
JOIN dbo.Employees AS C -- C = Child
ON P.lvl = @lvl - 1 -- Filter parents from previous level
AND C.mgrid = P.empid;
END

RETURN;
END
GO

-- Node ids of descendants of a given node
SELECT empid, lvl FROM dbo.fn_subordinates1(3) AS S;

[color=green]方案二：有级数限制[/color]

CREATE FUNCTION dbo.fn_subordinates2
(@root AS INT, @maxlevels AS INT = NULL) RETURNS @Subs TABLE
(
empid INT NOT NULL PRIMARY KEY NONCLUSTERED,
lvl INT NOT NULL,
UNIQUE CLUSTERED(lvl, empid) -- Index will be used to filter level
)
AS
BEGIN
DECLARE @lvl AS INT;
SET @lvl = 0; -- Initialize level counter with 0
-- If input @maxlevels is NULL, set it to maximum integer
-- to virtually have no limit on levels
SET @maxlevels = COALESCE(@maxlevels, 2147483647);

-- Insert root node to @Subs
INSERT INTO @Subs(empid, lvl)
SELECT empid, @lvl FROM dbo.Employees WHERE empid = @root;

WHILE @@rowcount > 0 -- while previous level had rows
AND @lvl < @maxlevels -- and previous level < @maxlevels
BEGIN
SET @lvl = @lvl + 1; -- Increment level counter

-- Insert next level of subordinates to @Subs
INSERT INTO @Subs(empid, lvl)
SELECT C.empid, @lvl
FROM @Subs AS P -- P = Parent
JOIN dbo.Employees AS C -- C = Child
ON P.lvl = @lvl - 1 -- Filter parents from previous level
AND C.mgrid = P.empid;
END

RETURN;
END
GO

-- Descendants of a given node, no limit on levels
SELECT empid, lvl
FROM dbo.fn_subordinates2(3, NULL) AS S;

[color=green]解决方案三：使用cte无级数限制[/color]

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;

[color=green]解决方案四：有级数限制的子数，CTE解决方案[/color]

DECLARE @root AS INT, @maxlevels AS INT;
SET @root = 3;
SET @maxlevels = 2;

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 -- limit parent's level
)
SELECT * FROM SubsCTE;


[color=blue]二、祖先[/color]

[color=green]解决方案一：[/color]

CREATE FUNCTION dbo.fn_managers
(@empid AS INT, @maxlevels AS INT = NULL) RETURNS @Mgrs TABLE
(
empid INT NOT NULL PRIMARY KEY,
lvl INT NOT NULL
)
AS
BEGIN
IF NOT EXISTS(SELECT * FROM dbo.Employees WHERE empid = @empid)
RETURN;

DECLARE @lvl AS INT;
SET @lvl = 0; -- Initialize level counter with 0
-- If input @maxlevels is NULL, set it to maximum integer
-- to virtually have no limit on levels
SET @maxlevels = COALESCE(@maxlevels, 2147483647);

WHILE @empid IS NOT NULL -- while current employee has a manager
AND @lvl <= @maxlevels -- and previous level < @maxlevels
BEGIN
-- Insert current manager to @Mgrs
INSERT INTO @Mgrs(empid, lvl) VALUES(@empid, @lvl);
SET @lvl = @lvl + 1; -- Increment level counter
-- Get next level manager
SET @empid = (SELECT mgrid FROM dbo.Employees WHERE empid = @empid);
END

RETURN;
END
GO

-- Ancestors of a given node, no limit on levels
SELECT empid, lvl
FROM dbo.fn_managers(8, NULL) AS M;

[color=green]解决方案二：[/color]
DECLARE @empid AS INT, @maxlevels AS INT;
SET @empid = 8;
SET @maxlevels = 2;

WITH MgrsCTE
AS
(
SELECT empid, mgrid, empname, 0 AS lvl
FROM dbo.Employees
WHERE empid = @empid

UNION ALL

SELECT P.empid, P.mgrid, P.empname, C.lvl + 1
FROM MgrsCTE AS C
JOIN dbo.Employees AS P
ON C.mgrid = P.empid
AND C.lvl < @maxlevels -- limit child's level
)
SELECT * FROM MgrsCTE;


[color=blue]三、带有路径枚举的子图/字树[/color]

[color=green]解决方案一：通用解决方案[/color]

CREATE FUNCTION dbo.fn_subordinates3
(@root AS INT, @maxlevels AS INT = NULL) RETURNS @Subs TABLE
(
empid INT NOT NULL PRIMARY KEY NONCLUSTERED,
lvl INT NOT NULL,
path VARCHAR(900) NOT NULL
UNIQUE CLUSTERED(lvl, empid) -- Index will be used to filter level
)
AS
BEGIN
DECLARE @lvl AS INT;
SET @lvl = 0; -- Initialize level counter with 0
-- If input @maxlevels is NULL, set it to maximum integer
-- to virtually have no limit on levels
SET @maxlevels = COALESCE(@maxlevels, 2147483647);

-- Insert root node to @Subs
INSERT INTO @Subs(empid, lvl, path)
SELECT empid, @lvl, '.' + CAST(empid AS VARCHAR(10)) + '.'
FROM dbo.Employees WHERE empid = @root;

WHILE @@rowcount > 0 -- while previous level had rows
AND @lvl < @maxlevels -- and previous level < @maxlevels
BEGIN
SET @lvl = @lvl + 1; -- Increment level counter

-- Insert next level of subordinates to @Subs
INSERT INTO @Subs(empid, lvl, path)
SELECT C.empid, @lvl,
P.path + CAST(C.empid AS VARCHAR(10)) + '.'
FROM @Subs AS P -- P = Parent
JOIN dbo.Employees AS C -- C = Child
ON P.lvl = @lvl - 1 -- Filter parents from previous level
AND C.mgrid = P.empid;
END

RETURN;
END
GO

-- Return descendants of a given node, along with a materialized path
SELECT empid, lvl, path
FROM dbo.fn_subordinates3(1, NULL) AS S;

-- Return descendants of a given node, sorted and indented
SELECT E.empid, REPLICATE(' | ', lvl) + empname AS empname
FROM dbo.fn_subordinates3(1, NULL) AS S
JOIN dbo.Employees AS E
ON E.empid = S.empid
ORDER BY path;

[color=green]解决方案二：CTE解决方案[/color]

-- Descendants of a given node, with Materialized Path, CTE Solution
DECLARE @root AS INT;
SET @root = 1;

WITH SubsCTE
AS
(
SELECT empid, empname, 0 AS lvl,
-- Path of root = '.' + empid + '.'
CAST('.' + CAST(empid AS VARCHAR(10)) + '.'
AS VARCHAR(MAX)) AS path
FROM dbo.Employees
WHERE empid = @root

UNION ALL

SELECT C.empid, C.empname, P.lvl + 1,
-- Path of child = parent's path + child empid + '.'
CAST(P.path + CAST(C.empid AS VARCHAR(10)) + '.'
AS VARCHAR(MAX)) AS path
FROM SubsCTE AS P
JOIN dbo.Employees AS C
ON C.mgrid = P.empid
)
SELECT empid, REPLICATE(' | ', lvl) + empname AS empname
FROM SubsCTE
ORDER BY path;