SQL递归查询的有关问题()

SQL递归查询的问题(高手请进)
cidlid
10
3412
21
23
32
234
43
564
6856



cid当前id，lid上一个id,如果输入56，则得到9,8,6,4,2行(红色字体)的数据,最后一个lid必须为0

[解决办法]

SQL code

--> --> (Roy)生成測試數據 if not object_id('Tempdb..#T') is null    drop table #TGoCreate table #T([cid] int,[lid] int)Insert #Tselect 1,0 union allselect 34,12 union allselect 2,1 union allselect 2,3 union allselect 3,2 union allselect 23,4 union allselect 4,3 union allselect 56,4 union allselect 68,56Go;with bas(Select * from #T where cid=56union allselect  a.* from #T as a inner join b on a.cid=b.lid where not exists(select 1 from #T where cid=a.cid and lid<a.lid))select * from b order by cid/*cid    lid1    02    13    24    356    4*/
[解决办法]
SQL code
/*标题：SQL SERVER 2005中查询指定节点及其所有父节点的方法(表格形式显示)作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开) 时间：2010-02-02地点：新疆乌鲁木齐*/create table tb(id varchar(3) , pid varchar(3) , name nvarchar(10))insert into tb values('001' , null  , N'广东省')insert into tb values('002' , '001' , N'广州市')insert into tb values('003' , '001' , N'深圳市')insert into tb values('004' , '002' , N'天河区')insert into tb values('005' , '003' , N'罗湖区')insert into tb values('006' , '003' , N'福田区')insert into tb values('007' , '003' , N'宝安区')insert into tb values('008' , '007' , N'西乡镇')insert into tb values('009' , '007' , N'龙华镇')insert into tb values('010' , '007' , N'松岗镇')goDECLARE @ID VARCHAR(3)--查询ID = '001'的所有父节点SET @ID = '001';WITH T AS(  SELECT ID , PID , NAME   FROM TB  WHERE ID = @ID  UNION ALL  SELECT A.ID , A.PID , A.NAME   FROM TB AS A JOIN T AS B ON A.ID = B.PID)SELECT * FROM T ORDER BY ID/*ID   PID  NAME---- ---- ----------001  NULL 广东省(1 行受影响)*/--查询ID = '002'的所有父节点SET @ID = '002';WITH T AS(  SELECT ID , PID , NAME   FROM TB  WHERE ID = @ID  UNION ALL  SELECT A.ID , A.PID , A.NAME   FROM TB AS A JOIN T AS B ON A.ID = B.PID)SELECT * FROM T ORDER BY ID/*ID   PID  NAME---- ---- ----------001  NULL 广东省002  001  广州市(2 行受影响)*/--查询ID = '003'的所有父节点SET @ID = '003';WITH T AS(  SELECT ID , PID , NAME   FROM TB  WHERE ID = @ID  UNION ALL  SELECT A.ID , A.PID , A.NAME   FROM TB AS A JOIN T AS B ON A.ID = B.PID)SELECT * FROM T ORDER BY ID/*ID   PID  NAME---- ---- ----------001  NULL 广东省003  001  深圳市(2 行受影响)*/--查询ID = '009'的所有父节点SET @ID = '009';WITH T AS(  SELECT ID , PID , NAME   FROM TB  WHERE ID = @ID  UNION ALL  SELECT A.ID , A.PID , A.NAME   FROM TB AS A JOIN T AS B ON A.ID = B.PID)SELECT * FROM T ORDER BY ID/*ID   PID  NAME---- ---- ----------001  NULL 广东省003  001  深圳市007  003  宝安区009  007  龙华镇(4 行受影响)*/drop table tb--注：除ID值不一样外，四个SQL语句是一样的。
[解决办法]
要在上面做个计数器

;with b
as
(
Select *,flag=1 from #T where cid=56
union all
select a.xx,a.xxx,a.flag+1 from #T as a inner join b on a.cid=b.lid where not exists(select 1 from #T where cid=a.cid and lid<a.lid) and a.flag+1<=50
)
select * from b --option(maxrecursion 50)