--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#T') is null
    drop table #T
Go
Create table #T([cid] int,[lid] int)
Insert #T
select 1,0 union all
select 34,12 union all
select 2,1 union all
select 2,3 union all
select 3,2 union all
select 23,4 union all
select 4,3 union all
select 56,4 union all
select 68,56
Go

;with b
as
(
Select * from #T where cid=56
union all
select  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    lid
1    0
2    1
3    2
4    3
56    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'松岗镇')
go

DECLARE @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)