[DB2][Database][SQL][DB2 再帰]
- 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
YAGI Hiroto (piroto@a-net.email.ne.jp)
twitter http://twitter.com/pppiroto
Copyright© 矢木 浩人 All Rights Reserved.