表结构如下:
FileID(int) IsPublished(bit) IsLocked(bit) ParentFileID(int,null) TreePath(nvarchar(4000),null)
1 1 0 0 -1
2 1 0 1 ,1,
3 1 0 2 ,-1,-2,
4 1 0 3 null搜索要求:
传参数FIleID=1,搜索结果:
FileID
1
2
3
4搜索条件是:以FileID为父节点的所有子节点
FileID(int) IsPublished(bit) IsLocked(bit) ParentFileID(int,null) TreePath(nvarchar(4000),null)
1 1 0 0 -1
2 1 0 1 ,1,
3 1 0 2 ,-1,-2,
4 1 0 3 null搜索要求:
传参数FIleID=1,搜索结果:
FileID
1
2
3
4搜索条件是:以FileID为父节点的所有子节点
declare @T1 table (FileID int,ParentFileID int,Path varchar(10))
insert into @T1
select 1,0,',-1,' union all
select 2,1,',-1,1,' union all
select 3,2,',-1,1,2,' union all
select 4,3,null union all
select 5,0,',-1,' union all
select 6,5,',-1,5,';with maco as
(
select * from @T1 where FileID=1
union all
select a.* from @T1 a,maco b where a.ParentFileID=b.FileID
)select * from maco
/*
FileID ParentFileID Path
----------- ------------ ----------
1 0 ,-1,
2 1 ,-1,1,
3 2 ,-1,1,2,
4 3 NULL
*/
;WITH cte (id,pid)
AS
(
SELECT fileid,ParentFileID
FROM test
WHERE fileid=@id
UNION ALL
SELECT a.id,a.pid+1
FROM cte a INNER JOIN test b ON a.pid+1=b.fileid
)
SELECT pid FROM ctepid
-----------
0
1
2
3
4(5 行受影响)