SQL Server2005再帰クエリについて

質問

以下のような3階層のデータについて、以下の結果を得たいと思っています。再帰クエリを使用することになるかと思うのですが、どのようにクエリを作成すればよいでしょうか。アドバイスをお願いいたします。

＜データ＞
氏名　　　上司の氏名　　　階層　　　
田中　　　Null　　　　　　　　1　　
山田　　　田中　　　　　　　2
佐藤　　　山田　　　　　　　3
高田　　　山田　　　　　　　3

＜結果＞
氏名　　　部下の氏名　　　
田中　　　山田
田中　　　佐藤
田中　　　高田
山田　　　佐藤
山田　　　高田
佐藤　　　Null
高田　　　Null

※　部下の氏名は直下の部下だけでなく、直下の部下の、その下まで結果として返したい。

回答No.1

jamshid6 · ベストアンサー

通常のCTEでは直前の階層をもとに次の階層のデータを返すという繰り返しになるため、今回の目的の結果にはなりません。
また、最後の２行（部下がいない）は、CTEに外部結合が認められないので出すことができません。

したがって、すべてをアンカーメンバーとして最後の２行は後で付加する方式で考えると以下のようになると思います。

WITH 階層データ AS
(SELECT 上司の氏名,氏名,階層,上司の氏名 元の上司, 階層 元の階層
FROM データ
WHERE 上司の氏名 IS NOT NULL
UNION ALL
SELECT d.上司の氏名,d.氏名,d.階層, h.元の上司,h.階層
FROM データ d
INNER JOIN 階層データ h ON h.氏名=d.上司の氏名)

SELECT 元の上司,氏名
FROM
(SELECT 元の上司,氏名,元の階層,階層
FROM 階層データ
UNION ALL
SELECT 氏名,NULL,階層+1,階層+1
FROM データ
WHERE 氏名 NOT IN
(SELECT ISNULL(上司の氏名,'') FROM データ)
) tmp
ORDER BY 元の階層,階層

お礼

回答ありがとうございます。
クエリを理解するのに少し時間がかかりそうです。実際にクエリを実行してみて、仕組みを理解できるようにしたいと思います。取り急ぎお礼させていただきます。