如何在db2中使用递归查询(How to use Recursive query in db2)

我正在学习在db2中使用递归查询，在网上遇到问题打印以下模式

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

高达20级，使用以下查询在Oracle中解决了它

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

但不知道如何在db2中这样做，真的很感激任何帮助。

另外如何以相反的顺序进行，意味着如何从20级到1级打印星形金字塔？

I am learning to use recursive query in db2, got a problem online to print following pattern

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

upto 20 level, solved it in Oracle using following query

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

but got no idea how to do it in db2, would really appreciate any help.

Also how to do it in reverse order, means how to print stars pyramid from 20th to 1st level?

最满意答案

使用REPEAT而不是LPAD更正db2的查询

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

Correct query for db2, using REPEAT instead of 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如何在db2中使用递归查询(How to use Recursive query in db2)

我正在学习在db2中使用递归查询，在网上遇到问题打印以下模式

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

高达20级，使用以下查询在Oracle中解决了它

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

但不知道如何在db2中这样做，真的很感激任何帮助。

另外如何以相反的顺序进行，意味着如何从20级到1级打印星形金字塔？

I am learning to use recursive query in db2, got a problem online to print following pattern

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

upto 20 level, solved it in Oracle using following query

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

but got no idea how to do it in db2, would really appreciate any help.

Also how to do it in reverse order, means how to print stars pyramid from 20th to 1st level?

最满意答案

使用REPEAT而不是LPAD更正db2的查询

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

Correct query for db2, using REPEAT instead of 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