SQL递归调用层级示例

--调用方法：
--select * from GetChild('24')
--select id from GetChild('24')
--select * from KuCun where ProductType in(select id from GetChild('24'))

CREATE FUNCTION [dbo].[GetChild] ( @ID VARCHAR(10) )
RETURNS @t TABLE
    (
      ID VARCHAR(10) ,
      ParentID VARCHAR(10) ,
      Level INT
    )
AS 
    BEGIN
        DECLARE @i INT
        SET @i = 1
        INSERT  INTO @t
                SELECT  @ID, @ID, 0 --当前级，本级，如果不要的话可以注释掉或再加个参数来选择操作
        INSERT  INTO @t
                SELECT  ID, ParentID, @i
                FROM    Dept
                WHERE   ParentID = @ID

        WHILE @@rowcount <> 0 
            BEGIN
                SET @i = @i + 1
                INSERT  INTO @t
                        SELECT  a.ID, a.ParentID, @i
                        FROM    Dept a ,
                                @t b
                        WHERE   a.ParentID = b.ID
                                AND b.Level = @i - 1
            END
        RETURN
    END

--------------------------------------------------------------------------------
--在SQL Server2005中其实提供了CTE[公共表表达式]来实现递归: 
DECLARE @Id INT 
SET @Id = 24 ;
    ---在此修改父节点 

WITH    RootNodeCTE ( Id, ParentId )
          AS ( SELECT   Id, ParentId
               FROM     Dept
               WHERE    ParentId IN ( @Id )
               UNION ALL
               SELECT   Dept.Id, Dept.ParentId
               FROM     RootNodeCTE
                        INNER JOIN Dept ON RootNodeCTE.Id = Dept.ParentId
             )
    SELECT  *
    FROM    RootNodeCTE

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

--生成测试数据
CREATE TABLE Dept
    (
      ID INT ,
      ParentID INT ,
      msg VARCHAR(20)
    )
INSERT  INTO Dept
        SELECT  1, 0, NULL
INSERT  INTO Dept
        SELECT  2, 1, NULL
INSERT  INTO Dept
        SELECT  3, 1, NULL
INSERT  INTO Dept
        SELECT  4, 2, NULL
INSERT  INTO Dept
        SELECT  5, 3, NULL
INSERT  INTO Dept
        SELECT  6, 5, NULL
INSERT  INTO Dept
        SELECT  7, 6, NULL
go


--创建用户定义函数
CREATE FUNCTION [dbo].[GetChild] ( @ID VARCHAR(10) )
RETURNS @t TABLE
    (
      ID VARCHAR(10) ,
      ParentID VARCHAR(10) ,
      Level INT
    )
AS 
    BEGIN
        DECLARE @i INT
        SET @i = 1
        INSERT  INTO @t
                SELECT  @ID, @ID, 0 --当前级，本级，如果不要的话可以注释掉或再加个参数来选择操作
        INSERT  INTO @t
                SELECT  ID, ParentID, @i
                FROM    Dept
                WHERE   ParentID = @ID

        WHILE @@rowcount <> 0 
            BEGIN
                SET @i = @i + 1
                INSERT  INTO @t
                        SELECT  a.ID, a.ParentID, @i
                        FROM    Dept a ,
                                @t b
                        WHERE   a.ParentID = b.ID
                                AND b.Level = @i - 1
            END
        RETURN
    END


--执行查询
SELECT  ID
FROM    dbo.GetChild(3)
go

--输出结果
/*
3
5
6
7
*/

--删除测试数据
DROP FUNCTION GetChild
DROP TABLE Dept