DB2SQL递归写法

with t1 (A,B,NUM) as (
select A,B,row_number() over (partition by A order by id) from 
(VALUES
( '1' , '1' ,'a'),
( '2' , '2' ,'b'),
( '3' , '1' ,'c'),
( '4' , '1' ,'d'),
( '5' , '3' ,'e'),
( '6' , '3' ,'f')
) AS mytest(id,A,B)
),
t2(RA,RB,NUM) as (
select A,char(B,10),NUM from t1 where num = 1
union all 
select t2.RA,rtrim(t2.rb) ||','||t1.B ,t1.num from t1,t2 where t1.num = t2.num+1 
and t1.A = t2.RA
)
select RA,RB from t2 
where NUM = (select Max(NUM) from t2 temp where temp.RA = t2.RA) order by RA

 输出结果：

RA     RB

1        a,c,d     
2        b         
3        e,f      

 

树的递归查询：

WITH TEMP(PARENTID,ID,NAME) AS
(
SELECT PARENTID,ID,NAME FROM BBS WHERE NAME= 'DB2'
UNION ALL
SELECT B.PARENTID,B.ID,B.NAME FROM BBS AS B, TEMP AS T WHERE B.PARENTID=T.ID
)
SELECT * FROM TEMP;--- 语句 4