元宵佳节：看Oracle技术粉们用SQL画团圆

Nyfor @ITPUB 2016-02-23

252

话团圆，画团圆，元宵佳节倍思亲，可是大家知道吗，万能的SQL可以帮助大家绘制团圆。

在ITPUB论坛里，一群SQL爱好者们会用SQL来描摹一切可能。请看如下这段SQL，为大家绘制了团团圆圆的五连环：

with a as (select distinct round(a.x + b.x) x,round(a.y + b.y) y from
(select (sum(x) over(order by n)) x,
round(sum(y) over(order by n)) y
from (select n, cos(n/30 * 3.1415926)*2 x,
sin(n/30 * 3.1415926) y
from (select rownum - 1 n from all_objects where rownum <= 30 +30))) a,
(select n, (sum(x) over(order by n)) x,
round(sum(y) over(order by n)) y
from (select n,
cos( m 3 * 3.1415926) * 2 * 15 x,
sin( m 3 * 3.1415926)* 15 y
from (select case when rownum <= 2 then 3
when rownum = 3 then -2 else -6 end m, rownum - 1 n
from all_objects where rownum <= 5))) b
)
select replace(sys_connect_by_path(point, '/'), '/', null) star
from (select b.y, b.x, decode(a.x, null, ' ', '*') point
from a,
(select *
from (select rownum - 1 + (select min(x) from a) x
from all_objects
where rownum <= (select max(x) - min(x) + 1 from a)),
(select rownum - 1 + (select min(y) from a) y
from all_objects
where rownum <= (select max(y) - min(y) + 1 from a))) b
where a.x(+) = b.x
and a.y(+) = b.y)
where x = (select max(x) from a)
start with x = (select min(x) from a)
connect by y = prior y
and x = prior x + 1;

这段SQL在Oracle中输出了下图，请用SQL执行：

好吧，这是五个连环，事实上是奥运会的五环旗，在庆祝奥运期间，网友 nyfor 的随手创作。

再看如下一段SQL，则是输出了一个五角星：

with a as (
select distinct round(sum(x) over(order by n)) x,
round(sum(y) over(order by n)) y
from (select n,
cos(trunc(n 20) * (1-1/5) * 3.1415926) * 2 x,
sin(trunc(n 20) * (1-1/5) * 3.1415926) y
from (select rownum - 1 n from all_objects where rownum <= 20 * 5))
)
select replace(sys_connect_by_path(point, '/'), '/', null) star
from (select b.y, b.x, decode(a.x, null, ' ', '*') point
from a,
(select *
from (select rownum - 1 + (select min(x) from a) x
from all_objects
where rownum <= (select max(x) - min(x) + 1 from a)),
(select rownum - 1 + (select min(y) from a) y
from all_objects
where rownum <= (select max(y) - min(y) + 1 from a))) b
where a.x(+) = b.x
and a.y(+) = b.y)
where x = (select max(x) from a)
start with x = (select min(x) from a)
connect by y = prior y
and x = prior x + 1;

这个SQL的解释如下：

其中数字20表示五角星每一条边上的点的个数(你也可以设置的大一些或小一些), 其中的数字5表示五角星的边数, 其中的数字2是为了调整横向字符间距与纵向行距之间的差异而设置的, 你也可以不乘以这个2, 这里只是为了输出稍微好看一些.

调整期中数字5, 你还可以输出7角星, 9角星.... 注意我的SQL不能输出6角星,8角星，因为我的SQL算法中是以一笔画能够画成的星为基础设计的算法的.

比如，以下是7角形输出：

在一轮讨论之后，newkid 大神给出了一个系列的SQL改写，小编就列举如下。

SQL一：

with a as ( select distinct round(sum(x) over(order by n)) x,
round(sum(y) over(order by n)) y
from (select n,
cos(trunc(n 20) * (1-1/5) * 3.1415926) * 2 x,
sin(trunc(n 20) * (1-1/5) * 3.1415926) y
from (select rownum - 1 n from DUAL CONNECT BY rownum <= 20 * 5))
)
SELECT LPAD(REPLACE(SUM(POWER(10,x-1)),'0',' '),(SELECT MAX(x) FROM a)) AS star
FROM a
GROUP BY y
ORDER BY y;

SQL二：

with a as ( select distinct round(sum(x) over(order by n)) x,
round(sum(y) over(order by n)) y
from (select n,
cos(trunc(n 20) * (1-1/5) * 3.1415926) x,
sin(trunc(n / 20) * (1-1/5) * 3.1415926) y
from (select rownum - 1 n from DUAL CONNECT BY rownum <= 20 * 5))
)
SELECT LPAD(REPLACE(SUM(POWER(10,x)),'0',' '),(SELECT MAX(x)+1 FROM a)) AS star
FROM a
GROUP BY y
ORDER BY y;

SQL三：

with a as ( select distinct round(sum(x) over(order by n)) x,
round(sum(y) over(order by n)) y
from (select n,
cos(trunc(n / 20) * (1-1/5) * 3.1415926) * 2 x,
sin(trunc(n / 20) * (1-1/5) * 3.1415926) y
from (select rownum - 1 n from DUAL CONNECT BY rownum <= 20 * 5))
)
SELECT TRANSLATE(LPAD(NVL(SUM(POWER(10,CASE WHEN x>=40 THEN x-40 END)),0),(SELECT MAX(x)-39 FROM a WHERE x>=40))
||LPAD(SUM(POWER(10,CASE WHEN x<40 THEN x END)),40)
,'01',' *'
)
AS star
FROM a
GROUP BY y
ORDER BY y;

SQL四：

with a as (SELECT x,y
,ROW_NUMBER() OVER(PARTITION BY y ORDER BY x) rn
,MAX(x) OVER(PARTITION BY y) maxx
FROM (select distinct round(sum(x) over(order by n)) x,
round(sum(y) over(order by n)) y
from (select n,
cos(trunc(n / 20) * (1-1/5) * 3.1415926) * 2 x,
sin(trunc(n / 20) * (1-1/5) * 3.1415926) y
from (select rownum - 1 n from DUAL CONNECT BY rownum <= 20 * 5)
)
)
)
,t(rn,x,y,str,maxx) AS (
SELECT 1,x,y,LPAD('*',x+1),maxx FROM a WHERE rn=1
UNION ALL
SELECT a.rn,a.x,t.y,str||RPAD(' ',a.x-t.x-1)||'*',t.maxx
FROM t,a
WHERE t.rn=a.rn-1 AND t.y=a.y
) CYCLE x,y SET cycle_flag TO 'Y' DEFAULT 'N'
SELECT str FROM t WHERE x=maxx ORDER BY y;

SQL五：

VAR SCALE NUMBER;
EXEC :SCALE :=3;

with a as (SELECT x,y
,ROW_NUMBER() OVER(PARTITION BY y ORDER BY x) rn
,MAX(x) OVER(PARTITION BY y) maxx
FROM (select distinct round(sum(x) over(order by n)) x,
round(sum(y) over(order by n)) y
from (select n,
cos(trunc(n / (10*:SCALE)) * (1-1/5) * 3.1415926) * 2 x,
sin(trunc(n / (10*:SCALE)) * (1-1/5) * 3.1415926) y
from (select rownum - 1 n from DUAL CONNECT BY rownum <= 10*:SCALE * 5)
)
)
)
,t(rn,x,y,str,maxx) AS (
SELECT 1,x,y,LPAD('*',x+1),maxx FROM a WHERE rn=1
UNION ALL
SELECT a.rn,a.x,t.y,str||RPAD(' ',a.x-t.x-1)||'*',t.maxx
FROM t,a
WHERE t.rn=a.rn-1 AND t.y=a.y
) CYCLE x,y SET cycle_flag TO 'Y' DEFAULT 'N'
SELECT str FROM t WHERE x=maxx ORDER BY y;

SQL六 - 利用wmsys.wm_concat的写法其实更简单：

with a as (SELECT x,y
,LAG(x,1,0) OVER(PARTITION BY y ORDER BY x) last_x
FROM (select distinct round(sum(x) over(order by n)) x,
round(sum(y) over(order by n)) y
from (select n,
cos(trunc(n / (10*:SCALE)) * (1-1/5) * 3.1415926) * 2 x,
sin(trunc(n / (10*:SCALE)) * (1-1/5) * 3.1415926) y
from (select rownum - 1 n from DUAL CONNECT BY rownum <= 10*:SCALE * 5)
)
)
)
SELECT REPLACE(MAX(str),',') STR
FROM (SELECT y,wmsys.wm_concat(LPAD('*',x-last_x)) OVER(PARTITION BY y ORDER BY x) str
FROM a
)
GROUP BY y
ORDER BY y;

SQL之七 - wmsys.wm_concat的connect by替代写法：

with a as (SELECT x,y
,LAG(x,1,0) OVER(PARTITION BY y ORDER BY x) last_x
,ROW_NUMBER() OVER(PARTITION BY y ORDER BY x) rn
FROM (select distinct round(sum(x) over(order by n)) x,
round(sum(y) over(order by n)) y
from (select n,
cos(trunc(n / (10*:SCALE)) * (1-1/5) * 3.1415926) * 2 x,
sin(trunc(n / (10*:SCALE)) * (1-1/5) * 3.1415926) y
from (select rownum - 1 n from DUAL CONNECT BY rownum <= 10*:SCALE * 5)
)
)
)
SELECT REPLACE(MAX(str),',') STR
FROM (SELECT y,SYS_CONNECT_BY_PATH(LPAD('*',x-last_x),',') str
FROM a
START WITH rn=1
CONNECT BY y=PRIOR y AND rn=PRIOR rn+1
)
GROUP BY y
ORDER BY y;