再帰クエリ（CTE）

来源：互联网发布：苏州软件实施招聘信息编辑：程序博客网时间：2024/06/05 09:26

CREATE TABLE 社員 ( 社員番号 int NOT NULL, 社員名 varchar(40) NULL, 上司社員番号 int NULL, 性別 char(4) NULL )

INSERT INTO 社員 VALUES (1001, '山田太郎', NULL, '男性')
INSERT INTO 社員 VALUES (1002, '鈴木一郎', NULL, '男性')
INSERT INTO 社員 VALUES (1003, '岡田明子', 1001, '女性')
INSERT INTO 社員 VALUES (1004, '若旅素子', 1002, '女性')
INSERT INTO 社員 VALUES (1005, '佐藤啓太', 1001, '男性')
INSERT INTO 社員 VALUES (1006, '川崎太郎', 1003, '男性')

WITH cte1 (社員番号, 社員名, 上司社員番号, 階層)
AS (
    -- 上司
    SELECT 社員番号, 社員名, 上司社員番号, 0
    FROM 社員
    WHERE 社員番号 = 1001

    UNION ALL

    -- 部下（再帰）
    SELECT e.社員番号, e.社員名, e.上司社員番号, cte1.階層 + 1
    FROM 社員 AS e INNER JOIN cte1 ON e.上司社員番号 = cte1.社員番号
)

SELECT * FROM cte1

WITH cte1 (path, 社員番号, 社員名, 上司社員番号, 階層)
AS (
    -- 上司
    SELECT HierarchyID::GetRoot() AS root , 社員番号, 社員名, 上司社員番号, 0
    FROM 社員
    WHERE 社員番号 = 1001

    UNION ALL

    -- 部下（再帰）
    SELECT CAST( cte1.path.ToString() + CAST(e.社員番号 AS varchar(30)) + '/' AS HierarchyID ) ,e.社員番号, e.社員名, e.上司社員番号, cte1.階層+ 1
    FROM 社員 AS e INNER JOIN cte1 ON e.上司社員番号 = cte1.社員番号
)

SELECT path.ToString(), * FROM cte1