使用 Oracle SQL 绘制模式

Question

我正在尝试绘制两种模式，但遇到了不同的问题：

1）

******************* 
****************** 
***************** 
**************** 
*************** 
************** 
************* 
************ 
*********** 
********** 
********* 
******** 
******* 
****** 
***** 
**** 
*** 
** 
* 
NULL 

我已经编写了以下查询来获取此内容：

select CASE WHEN (r2-r1)!=0 THEN
         rpad('*',r2-r1,'*') END 
from (select rownum r1
          ,(max(level) over ()) as r2 
     from dual 
    connect by level<=20);

我面临的问题（如果您查看图片）是空在最后出现。我无法找到摆脱它的方法。

2）我试图在每颗星星之间画出相同的图案。也被困在这里..

所有的帮助都将是非常可观的。

Answer 1

SQL> select rpad('*', level, '*') as c from dual connect by level <= 20 order by 
     length(c) desc;
C
------------------------------------
********************
*******************
******************
*****************
****************
***************
**************
*************
************
***********
**********
*********
********
*******
******
*****
****
***
**
*
20 rows selected.
SQL> select rpad('*', 2*level-1, ' *') as c from dual connect by level <= 20 order by 
     length(c) desc;
C
--------------------------------------------------
* * * * * * * * * * * * * * * * * * * *
* * * * * * * * * * * * * * * * * * *
* * * * * * * * * * * * * * * * * *
* * * * * * * * * * * * * * * * *
* * * * * * * * * * * * * * * *
* * * * * * * * * * * * * * *
* * * * * * * * * * * * * *
* * * * * * * * * * * * *
* * * * * * * * * * * *
* * * * * * * * * * *
* * * * * * * * * *
* * * * * * * * *
* * * * * * * *
* * * * * * *
* * * * * *
* * * * *
* * * *
* * *
* *
*
20 rows selected.

Answer 2

只需过滤掉最大值等于 ROWNUM 的行：

SELECT
    rpad('*', r2 - r1, '*') c1,
    rpad('* ', (r2 - r1) * 2, '* ') c2
FROM
    (SELECT ROWNUM r1, max(LEVEL) OVER () AS r2 FROM dual CONNECT BY LEVEL <= 20)
WHERE
    r1 < r2