如何在db2中使用递归查询

Question

我正在学习如何在db2中使用递归查询，网上有一个问题要打印如下模式

 * 
 ** 
 *** 
 **** 
 ***** 
 ****** 
 ******* 
 ******** 
 ********* 
 ********** 
 *********** 

多达20层，使用以下查询在Oracle中解决它

select lpad('*', level, '*') result  from dual  connect by level <= 20 

但是不知道如何在db2中做这件事，真的很感激你的帮助。

另外，如何做到倒序，即如何打印从20层到1层的恒星金字塔？

Answer 1

正确查询db2，使用REPEAT而不是LPAD

with x(id,val) as 
(
 select 1, REPEAT('*',1) from sysibm.sysdummy1
 union all
 select id+1, REPEAT('*',id+1) from x where id < 20
)
select val from x

with x(id,val) as 
(
 select 20, REPEAT('*',20) from sysibm.sysdummy1
 union all
 select id-1, REPEAT('*',id-1) from x where id > 1
)
select val from x

Answer 2

将dual替换为sysibm.sysdummy1

select lpad('*', level, '*') result  
from sysibm.sysdummy1   
connect by level <= 20                                                           

Answer 3

打印1到20 *s的递归查询

with x(id,val) as 
(
   select 1 as id,lpad('*',1,'*') as val from sysibm.sysdummy1
   union all
   select id+1,lpad('*',id+1,'*') from x where id < 20
)
select val from x

打印20到1 *s的递归查询

with x(id,val) as 
(
 select lpad('*',20,'*') as val,20 as id from sysibm.sysdummy1
 union all
 select lpad('*',id-1,'*'),id-1 from x where id > 1
)
select val from x