SQL递归调用层级示例

View Code

--调用方法：
--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

 

转载于:https://www.cnblogs.com/ToddLai/archive/2011/12/14/2287388.html