CTE 递归超过100次,出错了咋办?解决办法

CTE 递归超过100次,出错了咋办?

SQL code

id    parent_id    name0    0    All1    0    Financle Dept2    0    Admin Dept3    0    Business Dept4    0    Serveice Dept5    4    Sale Dept6    4    MIS7    6    UI8    6    Soft Development9    8    Inner Developmentwith testTree (id, parent_id, name, deptLevel) as(    select id, parent_id, name, 0 from Dept where id = 9    union all    select a.id, a.parent_id, a.name, b.deptLevel+1 from Dept a        inner join testTree b    on a.id = b.parent_id)select * from testTree

Msg 530, Level 16, State 1, Line 2
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.


[解决办法]

SQL code

with testTree (id, parent_id, name, deptLevel) as(    select id, parent_id, name, 0 from Dept where id = 9    union all    select a.id, a.parent_id, a.name, b.deptLevel+1 from Dept a        inner join testTree b    on a.id = b.parent_id)select * from testTreeOPTION(MAXRECURSION 0)如果确认你的语句结构没问题,可以后面加个无层次限制的选项
[解决办法]
with testTree (id, parent_id, name, deptLevel) as
(
   select id, parent_id, name, 0 from Dept where id = 9
   union all
   select a.id, a.parent_id, a.name, b.deptLevel+1 from Dept a
       inner join testTree b
   on a.id = b.parent_id
)

select * from testTree
(maxrecursion 1000)
通过这里控制嵌套次数
这里的1000就是嵌套循环的次数上限;如果你想取消限制，设置其为0.
[解决办法]
select * from testTree
OPTION (MAXRECURSION 0);

[解决办法]
SQL code
OPTION(MAXRECURSION 0)用这个设置最大循环数量0代表无穷
[解决办法]
探讨

可是我希望的结果是：（即按照level来列出记录）


SQL code

id    parent_id    level
0    0    0    
1    0    1
2    0    1
3    0    1    
4    0    1
5    4    2
6    4    2    
7    6    3
8    6    3
9 ……

[解决办法]
去掉这条数据
0    0    All

[解决办法]
SQL code
with testTree (id, parent_id, name, deptLevel) as(    select id, parent_id, name, 0 as levl from #Dept where id = 9    union all    select a.id, a.parent_id, a.name, b.deptLevel+1 from #Dept a        inner join testTree b    on a.id = b.parent_id and a.id!=0)select * from testTree          id   parent_id name                             deptLevel----------- ----------- ------------------------------ -----------          9           8 Inner Development                        0          8           6 Soft Development                         1          6           4 MIS                                      2          4           0 Serveice Dept                            3(4 行受影响)
[解决办法]
探讨

哦。 我明白了， 记录0 ALL 的parentid 也被我设置成为0了


SQL code


with testTree (id, parent_id, name, deptLevel) as
(
    select id, parent_id, name, 0 from Dept where id = 0
    union all
    select a.id, ……
 

 

查看更多 下一篇