SQL查询父节点下的所有子节点（包括子节点下的子节点，无限子节点）,该怎么解决

--2005 cte;with cte as(    select fid,fparentid from tb where fid = 3  --查询节点    union all    select a.fid,a.fparentid    from tb a join tb b on a.fparentid = b.id    where a.id is not null)select * from cte
------解决方案--------------------
SQL code
给你个 函数CREATE FUNCTION [dbo].[fn_getsubtree](@empid AS INT)     RETURNS @TREE TABLE(    empid   INT NOT NULL    ,empname VARCHAR(25) NOT NULL    ,mgrid   INT NULL    ,lvl     INT NOT NULL)ASBEGIN  WITH Employees_Subtree(empid, empname, mgrid, lvl)  AS  (     SELECT empid, empname, mgrid, 0    FROM Employees    WHERE empid = @empid    UNION all    SELECT e.empid, e.empname, e.mgrid, es.lvl+1    FROM Employees AS e      JOIN Employees_Subtree AS es        ON e.mgrid = es.empid  )  INSERT INTO @TREE    SELECT * FROM Employees_Subtree;  RETURNEND
------解决方案--------------------
递归查询
------解决方案--------------------
SQL code
-->Title:Generating test data-->Author:wufeng4552-->Date :2009-09-30 08:52:38set nocount onif object_id('tb','U')is not null drop table tbgocreate table tb(ID int, ParentID int)insert into tb select 1,0  insert into tb select 2,1  insert into tb select 3,1  insert into tb select 4,2  insert into tb select 5,3  insert into tb select 6,5  insert into tb select 7,6-->Title:查找指定節點下的子結點if object_id('Uf_GetChildID')is not null drop function Uf_GetChildIDgocreate function Uf_GetChildID(@ParentID int)returns @t table(ID int)asbegin   insert @t select ID from tb where [email protected]   while @@rowcount<>0   begin      insert @t select a.ID from tb a inner join @t b      on a.ParentID=b.id and       not exists(select 1 from @t where id=a.id)   end returnendgoselect * from dbo.Uf_GetChildID(5)/*ID-----------67*/-->Title:查找指定節點的所有父結點if object_id('Uf_GetParentID')is not null drop function Uf_GetParentIDgocreate function Uf_GetParentID(@ID int)returns @t table(ParentID int)asbegin   insert @t select ParentID from tb where [email protected]   while @@rowcount!=0   begin     insert @t select a.ParentID from tb a inner join @t b       on a.id=b.ParentID and        not exists(select 1 from @t where ParentID=a.ParentID)   end  returnendgoselect * from dbo.Uf_GetParentID(2)/*ParentID-----------10*/USE tempdbGO-- 建立演示环境CREATE TABLE Dept( id int PRIMARY KEY,  parent_id int, name nvarchar(20))INSERT DeptSELECT 0, 0, N'<全部>' UNION ALLSELECT 1, 0, N'财务部' UNION ALLSELECT 2, 0, N'行政部' UNION ALLSELECT 3, 0, N'业务部' UNION ALLSELECT 4, 0, N'业务部' UNION ALLSELECT 5, 4, N'销售部' UNION ALLSELECT 6, 4, N'MIS' UNION ALLSELECT 7, 6, N'UI' UNION ALLSELECT 8, 6, N'软件开发' UNION ALLSELECT 9, 8, N'内部开发'GO-- 查询指定部门下面的所有部门DECLARE @Dept_name nvarchar(20)SET @Dept_name = N'MIS';WITHDEPTS AS( -- 定位点成员 SELECT * FROM Dept WHERE name = @Dept_name UNION ALL -- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归 SELECT A.* FROM Dept A, DEPTS B WHERE A.parent_id = B.id)SELECT * FROM DEPTSGO-- 删除演示环境DROP TABLE Dept----CTE的综合应用USE tempdbGO-- 建立演示环境CREATE TABLE Dept( id int PRIMARY KEY,  parent_id int, name nvarchar(20))INSERT DeptSELECT 0, 0, N'<全部>' UNION ALLSELECT 1, 0, N'财务部' UNION ALLSELECT 2, 0, N'行政部' UNION ALLSELECT 3, 0, N'业务部' UNION ALLSELECT 4, 0, N'业务部' UNION ALLSELECT 5, 4, N'销售部' UNION ALLSELECT 6, 4, N'MIS' UNION ALLSELECT 7, 6, N'UI' UNION ALLSELECT 8, 6, N'软件开发' UNION ALLSELECT 9, 8, N'内部开发'GO-- 查询指定部门下面的所有部门, 并汇总各部门的下级部门数DECLARE @Dept_name nvarchar(20)SET @Dept_name = N'MIS';WITHDEPTS AS(   -- 查询指定部门及其下的所有子部门 -- 定位点成员 SELECT * FROM Dept WHERE name = @Dept_name UNION ALL -- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归 SELECT A.* FROM Dept A, DEPTS B WHERE A.parent_id = B.id),DEPTCHILD AS(  -- 引用第1个CTE,查询其每条记录对应的部门下的所有子部门 SELECT   Dept_id = P.id, C.id, C.parent_id FROM DEPTS P, Dept C WHERE P.id = C.parent_id UNION ALL SELECT   P.Dept_id, C.id, C.parent_id FROM DEPTCHILD P, Dept C WHERE P.id = C.parent_id),DEPTCHILDCNT AS( -- 引用第2个CTE, 汇总得到各部门下的子部门数 SELECT   Dept_id, Cnt = COUNT(*) FROM DEPTCHILD GROUP BY Dept_id)SELECT    -- JOIN第1,3个CTE,得到最终的查询结果 D.*, ChildDeptCount = ISNULL(DS.Cnt, 0)FROM DEPTS D LEFT JOIN DEPTCHILDCNT DS  ON D.id = DS.Dept_idGO-- 删除演示环境DROP TABLE Dept