DB2 再帰で区切りリストを作成

DB2 再帰で区切りリストを作成

[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