12,673
回編集
差分
ナビゲーションに移動
検索に移動
ページの作成:「==DB2 再帰で区切りリストを作成== [DB2][Database]{{category SQL}}[DB2 再帰] {{amazon|4873113156}} *WITH句を使った再帰的なクエリで実現…」
==DB2 再帰で区切りリストを作成==
[DB2][Database]{{category SQL}}[DB2 再帰]
{{amazon|4873113156}}
*WITH句を使った再帰的なクエリで実現
*UNION ALLの上側は、各従業員に対する情報を返す
*UNION ALLの下側で再帰が発生
*WITH句内のWHERE 句で再帰条件を指定
*x.len+1 は評価された従業員数、部署の人数と比較し必要以上の再帰を抑制
with x (workdept, cnt, list, empno, len) as (
select workdept, count(*) over(partition by workdept), cast(lastname as varchar(200)),empno,1
from employee
union all
select x.workdept,x.cnt, x.list || ' , ' || e.lastname, e.empno, x.len+1
from employee e, x
where e.workdept = x.workdept
and e.empno > x.empno
)
select workdept, list
from x
where len = cnt
order by workdept
=====結果=====
WORKDEPT LIST
-------- -------------------------------------------------------------------------------------------------
A00 HAAS , LUCCHESSI , O'CONNELL , HEMMINGER , ORLANDO
B01 THOMPSON
C01 KWAN , QUINTANA , NICHOLLS , NATZ
D11 STERN , ADAMSON , PIANKA , YOSHIMURA , SCOUTTEN , WALKER , BROWN , JONES , LUTZ , YAMAMOTO , JOHN
D21 PULASKI , JEFFERSON , MARINO , SMITH , JOHNSON , PEREZ , MONTEVERDE
E01 GEYER
E11 HENDERSON , SCHNEIDER , PARKER , SMITH , SETRIGHT , SCHWARTZ , SPRINGER
E21 SPENSER , MEHTA , LEE , GOUNOT , WONG , ALONZO
[DB2][Database]{{category SQL}}[DB2 再帰]
{{amazon|4873113156}}
*WITH句を使った再帰的なクエリで実現
*UNION ALLの上側は、各従業員に対する情報を返す
*UNION ALLの下側で再帰が発生
*WITH句内のWHERE 句で再帰条件を指定
*x.len+1 は評価された従業員数、部署の人数と比較し必要以上の再帰を抑制
with x (workdept, cnt, list, empno, len) as (
select workdept, count(*) over(partition by workdept), cast(lastname as varchar(200)),empno,1
from employee
union all
select x.workdept,x.cnt, x.list || ' , ' || e.lastname, e.empno, x.len+1
from employee e, x
where e.workdept = x.workdept
and e.empno > x.empno
)
select workdept, list
from x
where len = cnt
order by workdept
=====結果=====
WORKDEPT LIST
-------- -------------------------------------------------------------------------------------------------
A00 HAAS , LUCCHESSI , O'CONNELL , HEMMINGER , ORLANDO
B01 THOMPSON
C01 KWAN , QUINTANA , NICHOLLS , NATZ
D11 STERN , ADAMSON , PIANKA , YOSHIMURA , SCOUTTEN , WALKER , BROWN , JONES , LUTZ , YAMAMOTO , JOHN
D21 PULASKI , JEFFERSON , MARINO , SMITH , JOHNSON , PEREZ , MONTEVERDE
E01 GEYER
E11 HENDERSON , SCHNEIDER , PARKER , SMITH , SETRIGHT , SCHWARTZ , SPRINGER
E21 SPENSER , MEHTA , LEE , GOUNOT , WONG , ALONZO
© 2006 矢木浩人
