--测试脚本
If not object_id('[category]') is null
Drop table [category]
Go
Create table [category]([id] int,[name] nvarchar(3),[pid] int)
Insert category
Select 1,'c1',0 union all
Select 2,'c2',0 union all
Select 3,'c3',0 union all
Select 4,'c11',1 union all
Select 5,'c21',2 union all
Select 6,'c22',2 union all
Select 7,'c31',3 union all
Select 8,'c32',3
Go
with EmpsCte as
(
select pid,id,[name] From dbo.category where id=2
union all
select ec.pid,ec.id,ec.[name] from EmpsCte ec join dbo.category as cg
on ec.id=cg.pid
)
select * from EmpsCte;逻辑上有写错吗? 报的Error: 完成执行语句前已用完最大递归 100。
If not object_id('[category]') is null
Drop table [category]
Go
Create table [category]([id] int,[name] nvarchar(3),[pid] int)
Insert category
Select 1,'c1',0 union all
Select 2,'c2',0 union all
Select 3,'c3',0 union all
Select 4,'c11',1 union all
Select 5,'c21',2 union all
Select 6,'c22',2 union all
Select 7,'c31',3 union all
Select 8,'c32',3
Go
with EmpsCte as
(
select pid,id,[name] From dbo.category where id=2
union all
select ec.pid,ec.id,ec.[name] from EmpsCte ec join dbo.category as cg
on ec.id=cg.pid
)
select * from EmpsCte;逻辑上有写错吗? 报的Error: 完成执行语句前已用完最大递归 100。
(
select pid,id,[name] From dbo.category where id=2
union all
select ec.pid,ec.id,ec.[name] from EmpsCte ec join dbo.category as cg
on ec.pid=cg.pid
)
select * from EmpsCte;
Drop table [category]
Go
Create table [category]([id] int,[name] nvarchar(3),[pid] int)
Insert category
Select 1,'c1',0 union all
Select 2,'c2',0 union all
Select 3,'c3',0 union all
Select 4,'c11',1 union all
Select 5,'c21',2 union all
Select 6,'c22',2 union all
Select 7,'c31',3 union all
Select 8,'c32',3
Go
with EmpsCte as
(
select *From dbo.category where id=2
union all
select cg.* from EmpsCte ec join dbo.category as cg
on ec.id=cg.pid
)
select * from EmpsCte;----------- ---- -----------
2 c2 0
5 c21 2
6 c22 2(3 行受影响)
on ec.id=cg.pid这里都应该是cg,否则无限循环了