递归CTE问题

[zjybushiren88888]

--测试脚本
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。

[-狙击手-]

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.pid=cg.pid
)
select * from EmpsCte;

[-狙击手-]

楼上有误

[feixianxxx]

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 *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 行受影响)

[jinjazz]

select ec.pid,ec.id,ec.[name] from EmpsCte ec join dbo.category as cg
on ec.id=cg.pid

这里都应该是cg，否则无限循环了

[zjybushiren88888]

[Quote=引用 3 楼 feixianxxx 的回复:]
SQL codeIfnotobject_id('[category]')isnullDroptable[category]GoCreatetable[category]([id]int,[name]nvarchar(3),[pid]int)Insert categorySelect1,'c1',0unionallSelect2,'c2',0unionallSelect3,'c3',0unional¡­
[/Quote]

我这样写错在哪呢

[zjybushiren88888]

[Quote=引用 4 楼 jinjazz 的回复:]
selectec.pid,ec.id,ec.[name] from EmpsCte ec join dbo.category as cg
on ec.id=cg.pid

这里都应该是cg，否则无限循环了

[/Quote]

额 发现勒. 3Q.

[feixianxxx]

[Quote=引用 6 楼 zjybushiren88888 的回复:]
引用 4 楼 jinjazz 的回复:
selectec.pid,ec.id,ec.[name] from EmpsCte ec join dbo.category as cg
on ec.id=cg.pid

这里都应该是cg，否则无限循环了



额 发现勒. 3Q.
[/Quote]
就像剪剪说的。。。
无限了 死循环

[jwdream2008]

顶了！

[nalnait]

OPTION(MAXRECURSION 1000)