我使用以下通用表表达式来解析自引用表.但CTE不起作用,产生和无限循环并产生错误：
Msg 530,Level 16,State 1,Line 1 The statement terminated. The
maximum recursion 100 has been exhausted before statement completion.
如何修改此CTE以使其正常工作？
SET NOCOUNT ON;
USE tempdb;
IF OBJECT_ID('dbo.Employees','U') IS NOT NULL DROP TABLE dbo.Employees;
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 > 0)
);
INSERT INTO dbo.Employees(empid,mgrid,empname,salary) VALUES
(1,1,'David',$10000.00),(2,'Eitan',$7000.00),(3,'Ina',$7500.00),(4,2,'Seraph',$5000.00),(5,'Jiru',$5500.00),(6,'Steve',$4500.00),(7,3,'Aaron',(8,5,'Lilach',$3500.00),(9,7,'Rita',$3000.00),(10,'Sean',(11,'Gabriel',(12,9,'Emilia',$2000.00),(13,'Michael',(14,'Didi',$1500.00);
; with Tree as
(
SELECT empid,1 as lv,1 as level1,null as level2,null as level3,null as level4,null as level5
FROM Employees
WHERE empid = 1 and mgrid = 1
UNION ALL
SELECT E.empid,E.mgrid,T.lv + 1,T.level1,case when T.lv = 1 then E.empid else t.level2 end,case when T.lv = 2 then E.empid else t.level3 end,case when T.lv = 3 then E.empid else t.level4 end,case when T.lv = 4 then E.empid else t.level5 end
FROM Employees AS E
JOIN Tree T
ON E.mgrid = T.empid
)
select *
from Tree
order by empid
首选输出是
+-------+-------+----+--------+--------+--------+--------+--------+ | empid | mgrid | lv | level1 | level2 | level3 | level4 | level5 | +-------+-------+----+--------+--------+--------+--------+--------+ | 1 | 1 | 1 | 1 | NULL | NULL | NULL | NULL | | 2 | 1 | 2 | 1 | 2 | NULL | NULL | NULL | | 3 | 1 | 2 | 1 | 3 | NULL | NULL | NULL | | 4 | 2 | 3 | 1 | 2 | 4 | NULL | NULL | | 5 | 2 | 3 | 1 | 2 | 5 | NULL | NULL | | 6 | 2 | 3 | 1 | 2 | 6 | NULL | NULL | | 7 | 3 | 3 | 1 | 3 | 7 | NULL | NULL | | 8 | 5 | 4 | 1 | 2 | 5 | 8 | NULL | | 9 | 7 | 4 | 1 | 3 | 7 | 9 | NULL | | 10 | 5 | 4 | 1 | 2 | 5 | 10 | NULL | | 11 | 7 | 4 | 1 | 3 | 7 | 11 | NULL | | 12 | 9 | 5 | 1 | 3 | 7 | 9 | 12 | | 13 | 9 | 5 | 1 | 3 | 7 | 9 | 13 | | 14 | 9 | 5 | 1 | 3 | 7 | 9 | 14 | +-------+-------+----+--------+--------+--------+--------+--------+
解决方法
无限循环的原因是empid = mgrid的第一个记录.为了处理这个问题,你应该包括一个累积字段(在这个例子中的级别)来存储你已经处理的mgrid,并检查emid是否已经在这个列表中,以避免循环.
这是一个查询：
with Tree as
(
SELECT empid,null as level5,cast(mgrid as varchar(max)) levels
FROM Employees
WHERE empid = 1 and mgrid = 1
UNION ALL
SELECT E.empid,case when T.lv = 4 then E.empid else t.level5 end,T.levels+','+cast(E.mgrid as varchar(max)) levels
FROM Employees AS E
JOIN Tree T
ON E.mgrid = T.empid
and (','+T.levels+','
not like
'%,'+cast(E.empid as varchar(max))+',%')
)
select *
from Tree
order by empid
