id parent_id name
0 0 All
1 0 Financle Dept
2 0 Admin Dept
3 0 Business Dept
4 0 Serveice Dept
5 4 Sale Dept
6 4 MIS
7 6 UI
8 6 Soft Development
9 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.
0 0 All
1 0 Financle Dept
2 0 Admin Dept
3 0 Business Dept
4 0 Serveice Dept
5 4 Sale Dept
6 4 MIS
7 6 UI
8 6 Soft Development
9 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.
(
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)如果确认你的语句结构没问题,可以后面加个无层次限制的选项
(
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.
OPTION (MAXRECURSION 0);
用这个设置最大循环数量0代表无穷
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 8 4
(
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
order by deptLevel,id
OPTION(MAXRECURSION 0)
0 0 All
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 行受影响)第一条数据在会无限循环下去的。
(
select id, parent_id, name, 0 from Dept where id = 0
union all
select a.id, a.parent_id, a.name, b.deptLevel+1 from Dept a
inner join testTree b
on a.parent_id = b.id
)select * from testTree
所以循环一直在进行,我把它设置成null 就好了
0 NULL All
1 0 Financle Dept
2 0 Admin Dept
3 0 Business Dept
4 0 Serveice Dept
5 4 Sale Dept
6 4 MIS
7 6 UI
8 6 Soft Development
9 8 Inner Development
SQL:
with testTree (id, parent_id, name, deptLevel) as
(
select id, parent_id, name, 0 from Dept where id = 0
union all
select a.id, a.parent_id, a.name, b.deptLevel+1 from Dept a
inner join testTree b
on a.parent_id = b.id
)select * from testTree
0 NULL All 0
1 0 Financle Dept 1
2 0 Admin Dept 1
3 0 Business Dept 1
4 0 Serveice Dept 1
5 4 Sale Dept 2
6 4 MIS 2
7 6 UI 3
8 6 Soft Development 3
9 8 Inner Development 4