SQLアタマアカデミー
第5回 SQLで木構造を扱う~入れ子集合モデル (2)入れ子集合モデルにおける検索
入れ子集合モデルにおける検索
ルートとリーフを求める
まず入れ子集合の検索で基本となる考え方を理解しましょう。それは
たとえば,
リスト1 リーフの円を求める
SELECT *
FROM OrgChart Boss
WHERE NOT EXISTS
(SELECT *
FROM OrgChart Workers
WHERE Workers.lft > Boss.lft
AND Workers.lft < Boss.rgt);
図5 リスト1の実行結果
emp | lft | rgt |
---|---|---|
猪狩 | 2 | 3 |
木島 | 6 | 7 |
大神 | 9 | 10 |
加藤 | 11 | 12 |
反対に,
このように,
リスト2 リルートの円を求める
SELECT *
FROM OrgChart Workers
WHERE NOT EXISTS
(SELECT *
FROM OrgChart Boss
WHERE Workers.lft > Boss.lft
AND Workers.lft < Boss.rgt);
図6 リスト2の実行結果
emp | lft | rgt |
---|---|---|
足立 | 1 | 14 |
ノードのレベルを求める
次に,
リスト3 木の深さを求める
SELECT Children.emp , COUNT(Parents.emp) AS level
FROM OrgChart Parents, OrgChart Children
WHERE Children.lft BETWEEN Parents.lft AND Parents.rgt
GROUP BY Children.emp;
図7 リスト3の実行結果
emp | level |
---|---|
足立 | 1 |
上田 | 2 |
猪狩 | 2 |
江崎 | 3 |
加藤 | 3 |
大神 | 3 |
木島 | 4 |
「自分」
直属の上司・部下を調べる
隣接リストモデルの場合,
まず,
リスト4 上司を軸にぶらさがる部下を得る
SELECT Boss.emp AS boss, Worker.emp AS worker
FROM OrgChart Boss
LEFT OUTER JOIN OrgChart Worker
ON Boss.lft = (SELECT MAX(lft) ←左端座標が最大
FROM OrgChart
WHERE Worker.lft > lft
AND Worker.lft < rgt);
図8 リスト4の実行結果
emp | worker |
---|---|
足立 | 猪狩 |
足立 | 上田 |
猪狩 | |
上田 | 江崎 |
上田 | 大神 |
上田 | 加藤 |
江崎 | 木島 |
大神 | |
加藤 | |
木島 |
外部結合を使っているのは,
リスト5 部下を軸に上司を表示
SELECT Worker.emp AS worker, Boss.emp AS boss
FROM OrgChart Worker
LEFT OUTER JOIN OrgChart Boss
ON Boss.lft < Worker.lft
AND Boss.lft = (SELECT MAX(lft)
FROM OrgChart
WHERE Worker.lft > lft
AND Worker.lft < rgt);
図9 リスト5の実行結果
emp | boss |
---|---|
足立 | |
猪狩 | 足立 |
上田 | 足立 |
江崎 | 上田 |
大神 | 江崎 |
加藤 | 上田 |
木島 | 上田 |
今度は,
バックナンバー
SQLアタマアカデミー
- 最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (5)集合指向と手続き型
- 最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (4)OLAP関数と集約関数を組み合わせる
- 最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (3)OLAP専用関数
- 最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (2)OLAP関数の基本構文
- 最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (1)OLAP関数とは何か
- 第10回 結合大全 (5)非等値結合
- 第10回 結合大全 (4)自己結合
- 第10回 結合大全 (3)外部結合
- 第10回 結合大全 (2)内部結合
- 第10回 結合大全 (1)クロス結合