前書き
HR.EMPLOYEESテーブルを再帰with句を使用して検索
- EMPLOYEESテーブルを再帰with句でツリー形式の組織図を検索してみます.
- Oracle Live SQLにアクセスします.Oracle Accountが必要です.
Oracle Live SQL - SQLを実行します.
with p (employee_id, manager_id, depth) as (
/* 初期集合を指定する*/
select employee_id
, manager_id
, 1
from hr.employees
where manager_id is null /* 最上位 */
-- where employee_id = 148 /* 中間層のIDをルートとして指定する場合 */
union all
/* 親子関係を特定する条件を指定する */
select c.employee_id
, c.manager_id
, p.depth + 1
from p
inner join hr.employees c
on p.employee_id = c.manager_id /* 親のidが子の親idと一致する */
-- and p.depth + 1 <= 2 /* ノードの深さを3以下に制限する場合 */
)
search depth first by employee_id set sort_key
--search breadth first by employee_id set sort_key
select t.EMPLOYEE_ID
, p.depth
, LPAD('> ', (p.depth - 1) * 4, '-') || FIRST_NAME || ' ' || LAST_NAME AS NAME
-- , FIRST_NAME || ' ' || LAST_NAME AS NAME
, p.sort_key
from hr.employees t
inner join p
on t.employee_id = p.employee_id
order by sort_key
;
- 実行結果
メニュー表示用のデータを取り出すとき等に利用できそうです.
| EMPLOYEE_ID | DEPTH | NAME | SORT_KEY |
|---|---|---|---|
| 100 | 1 | Steven King | 1 |
| 101 | 2 | -> Neena Kochhar | 2 |
| 108 | 3 | -----> Nancy Greenberg | 3 |
| 109 | 4 | ---------> Daniel Faviet | 4 |
| 110 | 4 | ---------> John Chen | 5 |
| 111 | 4 | ---------> Ismael Sciarra | 6 |
| 112 | 4 | ---------> Jose Manuel Urman | 7 |
| 113 | 4 | ---------> Luis Popp | 8 |
| 200 | 3 | -----> Jennifer Whalen | 9 |
| 203 | 3 | -----> Susan Mavris | 10 |
| 204 | 3 | -----> Hermann Baer | 11 |
| 205 | 3 | -----> Shelley Higgins | 12 |
| 206 | 4 | ---------> William Gietz | 13 |
| 102 | 2 | -> Lex De Haan | 14 |
| 103 | 3 | -----> Alexander Hunold | 15 |
| 104 | 4 | ---------> Bruce Ernst | 16 |
| 105 | 4 | ---------> David Austin | 17 |
| 106 | 4 | ---------> Valli Pataballa | 18 |
| 107 | 4 | ---------> Diana Lorentz | 19 |
| 114 | 2 | -> Den Raphaely | 20 |
| 115 | 3 | -----> Alexander Khoo | 21 |
| 116 | 3 | -----> Shelli Baida | 22 |
| 117 | 3 | -----> Sigal Tobias | 23 |
| 118 | 3 | -----> Guy Himuro | 24 |
| 119 | 3 | -----> Karen Colmenares | 25 |
| 120 | 2 | -> Matthew Weiss | 26 |
| 125 | 3 | -----> Julia Nayer | 27 |
| 126 | 3 | -----> Irene Mikkilineni | 28 |
| 127 | 3 | -----> James Landry | 29 |
| 128 | 3 | -----> Steven Markle | 30 |
| 180 | 3 | -----> Winston Taylor | 31 |
| 181 | 3 | -----> Jean Fleaur | 32 |
| 182 | 3 | -----> Martha Sullivan | 33 |
| 183 | 3 | -----> Girard Geoni | 34 |
| 121 | 2 | -> Adam Fripp | 35 |
| 129 | 3 | -----> Laura Bissot | 36 |
| 130 | 3 | -----> Mozhe Atkinson | 37 |
| 131 | 3 | -----> James Marlow | 38 |
| 132 | 3 | -----> TJ Olson | 39 |
| 184 | 3 | -----> Nandita Sarchand | 40 |
| 185 | 3 | -----> Alexis Bull | 41 |
| 186 | 3 | -----> Julia Dellinger | 42 |
| 187 | 3 | -----> Anthony Cabrio | 43 |
| 122 | 2 | -> Payam Kaufling | 44 |
| 133 | 3 | -----> Jason Mallin | 45 |
| 134 | 3 | -----> Michael Rogers | 46 |
| 135 | 3 | -----> Ki Gee | 47 |
| 136 | 3 | -----> Hazel Philtanker | 48 |
| 188 | 3 | -----> Kelly Chung | 49 |
| 189 | 3 | -----> Jennifer Dilly | 50 |
| 190 | 3 | -----> Timothy Gates | 51 |
| 191 | 3 | -----> Randall Perkins | 52 |
| 123 | 2 | -> Shanta Vollman | 53 |
| 137 | 3 | -----> Renske Ladwig | 54 |
| 138 | 3 | -----> Stephen Stiles | 55 |
| 139 | 3 | -----> John Seo | 56 |
| 140 | 3 | -----> Joshua Patel | 57 |
| 192 | 3 | -----> Sarah Bell | 58 |
| 193 | 3 | -----> Britney Everett | 59 |
| 194 | 3 | -----> Samuel McCain | 60 |
| 195 | 3 | -----> Vance Jones | 61 |
| 124 | 2 | -> Kevin Mourgos | 62 |
| 141 | 3 | -----> Trenna Rajs | 63 |
| 142 | 3 | -----> Curtis Davies | 64 |
| 143 | 3 | -----> Randall Matos | 65 |
| 144 | 3 | -----> Peter Vargas | 66 |
| 196 | 3 | -----> Alana Walsh | 67 |
| 197 | 3 | -----> Kevin Feeney | 68 |
| 198 | 3 | -----> Donald OConnell | 69 |
| 199 | 3 | -----> Douglas Grant | 70 |
| 145 | 2 | -> John Russell | 71 |
| 150 | 3 | -----> Peter Tucker | 72 |
| 151 | 3 | -----> David Bernstein | 73 |
| 152 | 3 | -----> Peter Hall | 74 |
| 153 | 3 | -----> Christopher Olsen | 75 |
| 154 | 3 | -----> Nanette Cambrault | 76 |
| 155 | 3 | -----> Oliver Tuvault | 77 |
| 146 | 2 | -> Karen Partners | 78 |
| 156 | 3 | -----> Janette King | 79 |
| 157 | 3 | -----> Patrick Sully | 80 |
| 158 | 3 | -----> Allan McEwen | 81 |
| 159 | 3 | -----> Lindsey Smith | 82 |
| 160 | 3 | -----> Louise Doran | 83 |
| 161 | 3 | -----> Sarath Sewall | 84 |
| 147 | 2 | -> Alberto Errazuriz | 85 |
| 162 | 3 | -----> Clara Vishney | 86 |
| 163 | 3 | -----> Danielle Greene | 87 |
| 164 | 3 | -----> Mattea Marvins | 88 |
| 165 | 3 | -----> David Lee | 89 |
| 166 | 3 | -----> Sundar Ande | 90 |
| 167 | 3 | -----> Amit Banda | 91 |
| 148 | 2 | -> Gerald Cambrault | 92 |
| 168 | 3 | -----> Lisa Ozer | 93 |
| 169 | 3 | -----> Harrison Bloom | 94 |
| 170 | 3 | -----> Tayler Fox | 95 |
| 171 | 3 | -----> William Smith | 96 |
| 172 | 3 | -----> Elizabeth Bates | 97 |
| 173 | 3 | -----> Sundita Kumar | 98 |
| 149 | 2 | -> Eleni Zlotkey | 99 |
| 174 | 3 | -----> Ellen Abel | 100 |
| 175 | 3 | -----> Alyssa Hutton | 101 |
| 176 | 3 | -----> Jonathon Taylor | 102 |
| 177 | 3 | -----> Jack Livingston | 103 |
| 178 | 3 | -----> Kimberely Grant | 104 |
| 179 | 3 | -----> Charles Johnson | 105 |
| 201 | 2 | -> Michael Hartstein | 106 |
| 202 | 3 | -----> Pat Fay | 107 |