再帰クエリ（CTE）

原创于 2010-06-15 16:25:00 发布 · 592 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#insert #null #join #table #path

このブログでは、SQLを使用して組織の階層構造を効果的に表示する方法について説明します。具体的には、CTE（共通テーブル式）を用いて再帰的に部下までの階層を取得し、それぞれの社員の位置を明確に表現します。

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