遞迴 CTE 結構至少必須包含一個錨點成員與一個遞迴成員。下列虛擬程式碼顯示含有單一錨點成員與單一遞迴成員的簡單遞迴 CTE 所具備的元件。
|
1 |
<span style="color: #606060" id="lnum1"> 1:</span> <span style="color: #0000ff">WITH</span> cte_name ( column_name [,...n] ) |
|
1 |
<span style="color: #606060" id="lnum2"> 2:</span> <span style="color: #0000ff">AS</span> |
|
1 |
<span style="color: #606060" id="lnum3"> 3:</span> ( |
|
1 |
<span style="color: #606060" id="lnum4"> 4:</span> CTE_query_definition –- Anchor member <span style="color: #0000ff">is</span> defined. |
|
1 |
<span style="color: #606060" id="lnum5"> 5:</span> <span style="color: #0000ff">UNION</span> <span style="color: #0000ff">ALL</span> |
|
1 |
<span style="color: #606060" id="lnum6"> 6:</span> CTE_query_definition –- <span style="color: #0000ff">Recursive</span> member <span style="color: #0000ff">is</span> defined <span style="color: #0000ff">referencing</span> cte_name. |
|
1 |
<span style="color: #606060" id="lnum7"> 7:</span> ) |
|
1 |
<span style="color: #606060" id="lnum8"> 8:</span> <span style="color: #008000">-- Statement using the CTE</span> |
|
1 |
<span style="color: #606060" id="lnum9"> 9:</span> <span style="color: #0000ff">SELECT</span> * |
|
1 |
<span style="color: #606060" id="lnum10"> 10:</span> <span style="color: #0000ff">FROM</span> cte_name |
遞迴執行的語意如下：
將 CTE 運算式分割為錨點成員與遞迴成員。
執行錨點成員以建立第一個引動過程或基底結果集 (T0)。
執行遞迴成員，以 Ti 做為輸入，而以 Ti+1 做為輸出。
重複步驟 3，直到傳回空的結果集為止。
傳回結果集。這是 T0 至 Tn 的 UNION ALL。
版上範例 :
|
1 |
<span style="color: #606060" id="lnum1"> 1:</span> <span style="color: #0000ff">with</span> directsales(up_dno,dno,lvl) |
|
1 |
<span style="color: #606060" id="lnum2"> 2:</span> <span style="color: #0000ff">as</span>( |
|
1 |
<span style="color: #606060" id="lnum3"> 3:</span> --Anchor查詢 |
|
1 |
<span style="color: #606060" id="lnum4"> 4:</span> <span style="color: #0000ff">select</span> up_dno,dno,1 <span style="color: #0000ff">as</span> lvl |
|
1 |
<span style="color: #606060" id="lnum5"> 5:</span> <span style="color: #0000ff">from</span> dbo_dsales <span style="color: #0000ff">where</span> dno=<span style="color: #006080">'A'</span> |
|
1 |
<span style="color: #606060" id="lnum6"> 6:</span> <span style="color: #0000ff">union</span> <span style="color: #0000ff">all</span> |
|
1 |
<span style="color: #606060" id="lnum7"> 7:</span> --Recursive查詢 |
|
1 |
<span style="color: #606060" id="lnum8"> 8:</span> <span style="color: #0000ff">select</span> a.up_dno,a.dno,lvl+1 |
|
1 |
<span style="color: #606060" id="lnum9"> 9:</span> <span style="color: #0000ff">from</span> dbo_dsales a,directsales b |
|
1 |
<span style="color: #606060" id="lnum10"> 10:</span> <span style="color: #0000ff">where</span> a.up_dno=b.dno |
|
1 |
<span style="color: #606060" id="lnum11"> 11:</span> ) |
|
1 |
<span style="color: #606060" id="lnum12"> 12:</span> --執行CTE查詢 |
|
1 |
<span style="color: #606060" id="lnum13"> 13:</span> <span style="color: #0000ff">select</span> up_dno <span style="color: #0000ff">as</span> 上線直銷商,dno <span style="color: #0000ff">as</span> 直銷商,lvl <span style="color: #0000ff">as</span> 階層 |
|
1 |
<span style="color: #606060" id="lnum14"> 14:</span> <span style="color: #0000ff">from</span> directsales |
所以錨點成員是
select up_dno,dno,1 as lvl from dbo_dsales where dno='A'
也就是
up_dno,dno,lvl
null Ａ １
那遞迴成員是
select a.up_dno,a.dno,lvl+1 from dbo_dsales a,directsales b where a.up_dno=b.dno
也就是
在第一次跑時,b.dno = A
得到
up_dno,dno,lvl
Ａ Ｂ ２
Ａ Ｃ ２
在第二次跑時,b.dno = B,C
得到
up_dno,dno,lvl
Ｂ Ｄ ３
Ｂ Ｅ ３
Ｃ Ｆ ３
Ｃ Ｇ ３
Ｃ Ｈ ３
在第三次跑時,b.dno = D,E,F,G,H
得到
up_dno,dno,lvl
Ｄ Ｉ ４
Ｄ Ｊ ４
Ｆ Ｋ ４
Ｇ Ｌ ４
在第四次跑時,b.dno = I,J,K,L
得到
up_dno,dno,lvl
Ｊ Ｍ ５
Ｌ Ｎ ５
在第五次跑時,b.dno = M,N
得到 無符合資料(傳回空的結果集)
所以遞迴停止
所以最後得到結果為
up_dno,dno,lvl
null Ａ １
Ａ Ｂ ２
Ａ Ｃ ２
Ｂ Ｄ ３
Ｂ Ｅ ３
Ｃ Ｆ ３
Ｃ Ｇ ３
Ｃ Ｈ ３
Ｄ Ｉ ４
Ｄ Ｊ ４
Ｆ Ｋ ４
Ｇ Ｌ ４
Ｊ Ｍ ５
Ｌ Ｎ ５
個人實作:
資料庫結構大概是:
| sys_dep_id(部門ID) | sys_dep_up(上層部門ID) |
| 201 | 101 |
| 301 | 201 |
| 302 | 201 |
| 401 | 301 |
| 402 | 301 |
| 403 | 301 |
| 501 | 401 |
|
1 |
<span style="color: #606060" id="lnum1"> 1:</span> <span style="color: #0000ff">with</span> myview (sys_dep_up,sys_dep_id ) <span style="color: #0000ff">as</span> |
|
1 |
<span style="color: #606060" id="lnum2"> 2:</span> (<span style="color: #0000ff">select</span> sys_dep_up,sys_dep_id |
|
1 |
<span style="color: #606060" id="lnum3"> 3:</span> <span style="color: #0000ff">from</span> dbo.sys_dep <span style="color: #0000ff">where</span> sys_dep_up = 301 |
|
1 |
<span style="color: #606060" id="lnum4"> 4:</span> <span style="color: #0000ff">union</span> <span style="color: #0000ff">all</span> |
|
1 |
<span style="color: #606060" id="lnum5"> 5:</span> <span style="color: #0000ff">select</span> a.sys_dep_up,a.sys_dep_id |
|
1 |
<span style="color: #606060" id="lnum6"> 6:</span> <span style="color: #0000ff">from</span> dbo.sys_dep a,myview b |
|
1 |
<span style="color: #606060" id="lnum7"> 7:</span> <span style="color: #0000ff">where</span> a.sys_dep_up=b.sys_dep_id) |
|
1 |
<span style="color: #606060" id="lnum8"> 8:</span> |
|
1 |
<span style="color: #606060" id="lnum9"> 9:</span> <span style="color: #0000ff">select</span> sys_dep_id <span style="color: #0000ff">as</span> alldepid |
|
1 |
<span style="color: #606060" id="lnum10"> 10:</span> <span style="color: #0000ff">from</span> myview |
結果為: 401,402,403,501
(Visited 57 times, 1 visits today)