sql – 解析自引用表时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     |
+-------+-------+----+--------+--------+--------+--------+--------+

解决方法

这是一个查询：

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

这里是SQLFiddle demo

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!