正如大多數遞回 CTE 教程使用的那樣，我有一組典型的員工和相應的經理。我從教程Uri Dimant中的答案中得到它。
與深度優先搜索不同，我的目標是輸入員工 ID，然后查詢會回傳經理串列直到 root。
因此，我調整了 JOIN 陳述句以manager id從 CTE 加入到employee ID. 它應該獲取某個員工的經理姓名。
結果是一個錯誤：
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
我認為當遞回達到經理的最高級別時，它將回傳一個空結果集，指示遞回結束。
我想了解 SQL 引擎如何知道何時停止。以及如何使這個查詢按我的預期作業。
謝謝你
IF OBJECT_ID('Employees') IS NULL
BEGIN
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname nvarchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
SET NOCOUNT ON
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
END
GO
WITH EmpCTE
AS
(
-- Anchor Member (AM)
SELECT
empid,
empname,
mgrid,
0 AS level -- <------------------- SET LVL START FROM 0
FROM Employees
WHERE EMPID = 7
UNION ALL
-- Recursive Member (RM)
SELECT
e.empid,
e.empname,
e.mgrid,
e.level 1 -- <------------------- INCREMENT LVL
manager id
FROM Employees AS m
JOIN EmpCTE AS e -- <------------------- RECURSIVELY CALL EmpCTE
ON e.mgrid = m.empid
)
SELECT * FROM EmpCTE
uj5u.com熱心網友回復：
您已經設法創建了一個無限回圈。您可以插入過濾器level來除錯這些：
（也洗掉后manager id）
WITH EmpCTE
AS
(
-- Anchor Member (AM)
SELECT
empid,
empname,
mgrid,
0 AS level -- <------------------- SET LVL START FROM 0
FROM Employees
WHERE EMPID = 7
UNION ALL
-- Recursive Member (RM)
SELECT
e.empid,
e.empname,
e.mgrid,
e.level 1 -- <------------------- INCREMENT LVL
FROM Employees AS m
JOIN EmpCTE AS e -- <------------------- RECURSIVELY CALL EmpCTE
ON e.mgrid = m.empid
where level < 2
)
SELECT * FROM EmpCTE;
empid empname mgrid level
----------- ------------------------- ----------- -----------
7 Robert 3 0
7 Robert 3 1
7 Robert 3 2
EmpCTE as e這是因為您正在從而不是投影列Employees as m，因此您只是一次又一次地獲得相同的資料（加上正在增加的級別）。
WITH EmpCTE
AS
(
-- Anchor Member (AM)
SELECT
empid,
empname,
mgrid,
0 AS level -- <------------------- SET LVL START FROM 0
FROM Employees
WHERE EMPID = 7
UNION ALL
-- Recursive Member (RM)
SELECT
m.empid, -- these columns need to come from m
m.empname, -- these columns need to come from m
m.mgrid, -- these columns need to come from m
e.level 1 -- <------------------- INCREMENT LVL
FROM Employees AS m
JOIN EmpCTE AS e -- <------------------- RECURSIVELY CALL EmpCTE
ON e.mgrid = m.empid
)
SELECT * FROM EmpCTE;
empid empname mgrid level
----------- ------------------------- ----------- -----------
7 Robert 3 0
3 Janet 1 1
1 Nancy NULL 2
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/463078.html
上一篇：SQLJoin將資料從同一表中的同一列回傳到結果中的兩個不同行（星球大戰示例）
下一篇：在SQL查詢中從計算列中選擇新列