日期:2014-05-16 浏览次数:20653 次
Create Table Tab
(
Id int ,
PId int
)
INSERT INTO dbo.Tab
( Id, PId )
SELECT 1,NULL
UNION
SELECT 2,NULL
UNION
SELECT 3,1
UNION
SELECT 4,1
UNION
SELECT 5,1
UNION
SELECT 6,3
UNION
SELECT 7,2
SELECT * FROM dbo.Tab
;with f as
(
select * from tab where id=1
union all
select a.* from tab as a inner join f as b on a.pid=b.id
)
select * from f
with ct (ChildID, ParentID, [Level]) as (
select ChildID, ParentID, 1 as [Level] from LinkTable where ParentID = 284
union all
select e.ChildID, e.ParentID, [Level] + 1 from ct t, LinkTable e
where t.ChildID = e.ParentID
)
select * from ct