SQLSERVER递归处理

创建测试数据

IF OBJECT_ID('[tb]') IS NOT NULL
    DROP TABLE [tb]
GO
CREATE TABLE [tb]
(
    [modeid]     INT,
    modename     VARCHAR(20),
    parentid     INT
)
INSERT [tb]
SELECT 100,
       '商品管理',
       0 UNION ALL
SELECT 101,
       '定单管理',
       0 UNION ALL
SELECT 102,
       '用户管理',
       0 UNION ALL
SELECT 104,
       '学院广告',
       0 UNION ALL
SELECT 105,
       '系统设置',
       0 UNION ALL
SELECT 106,
       '附件管理',
       0 UNION ALL
SELECT 107,
       '商品管理',
       100 UNION ALL
SELECT 108,
       '明细管理',
       100 UNION ALL
SELECT 109,
       '物流管理',
       100 UNION ALL
SELECT 110,
       '商品信息管理',
       107 UNION ALL
SELECT 111,
       '商品分类管理',
       107 UNION ALL
SELECT 112,
       '回收站管理',
       107 UNION ALL
SELECT 114,
       '团购管理',
       108 UNION ALL
SELECT 115,
       '拍卖管理',
       108 UNION ALL
SELECT 116,
       '优惠管理',
       108 UNION ALL
SELECT 117,
       '会员管理',
       102 UNION ALL
SELECT 118,
       '会员卡管理',
       102 UNION ALL
SELECT 119,
       '资金管理',
       102 UNION ALL
SELECT 120,
       '管理员管理',
       102 UNION ALL
SELECT 121,
       '添加管理员',
       120 UNION ALL
SELECT 122,
       '修改管理员',
       120
GO
 
                                                                                    

 

递归方法一（函数法）

IF OBJECT_ID('Func_GetSubNode') IS NOT NULL
    DROP FUNCTION Func_GetSubNode
GO
CREATE FUNCTION Func_GetSubNode
(
    @id INT
)
RETURNS @re TABLE(id INT, LEVEL INT, sort VARCHAR(100), PATH VARCHAR(500))
AS
BEGIN
    DECLARE @l INT 
    SET @l = 0 
    INSERT @re
    SELECT [modeid],
           @l,
           RIGHT('00000' + LTRIM(modeid), 5),
           modename
    FROM   tb
    WHERE  parentid = @id
    
    WHILE @@rowcount > 0
    BEGIN
        SET @l = @l + 1
        INSERT @re
        SELECT a.modeid,
               @l,
               b.sort + RIGHT('00000' + LTRIM(a.modeid), 5),
               b.path + ' - ' + a.modename
        FROM   tb      AS a,
               @re AS     b
        WHERE  b.id = a.parentid
               AND b.level = @l -1
    END
    UPDATE @re
    SET    LEVEL = LEVEL
    
    RETURN
END
GO

 
SELECT a.modeid,
       a.parentid,
       NAME = REPLICATE('  ', b.level) + '┝' + a.modename,
       b.level,
       b.sort,
       b.path
FROM   tb            a,
       Func_GetSubNode(0)     b
WHERE  a.modeid = b.id
ORDER BY
       sort                        

 

递归方法二（CTE , 仅支持SQL2005以上版本）

WITH cte AS
(
    SELECT *
    FROM   [tb]
    WHERE  modeid = '100'
    UNION ALL
    SELECT a.* 
    FROM   [tb] AS a
           INNER JOIN [cte] AS b
                ON  a.parentid = b.modeid
)

SELECT *
FROM   cte