話(huà)團(tuán)圓，畫(huà)團(tuán)圓，元宵佳節(jié)倍思親，可是大家知道嗎，萬(wàn)能的SQL可以幫助大家繪制團(tuán)圓。

在ITPUB論壇里，一群SQL愛(ài)好者們會(huì)用SQL來(lái)描摹一切可能。請(qǐng)看如下這段SQL，為大家繪制了團(tuán)團(tuán)圓圓的五連環(huán)：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中輸出了下圖，請(qǐng)用SQL執(zhí)行：

好吧，這是五個(gè)連環(huán)，事實(shí)上是奧運(yùn)會(huì)的五環(huán)旗，在慶祝奧運(yùn)期間，網(wǎng)友 nyfor 的隨手創(chuàng)作。

再看如下一段SQL，則是輸出了一個(gè)五角星：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;

這個(gè)SQL的解釋如下：

其中數(shù)字20表示五角星每一條邊上的點(diǎn)的個(gè)數(shù)(你也可以設(shè)置的大一些或小一些), 其中的數(shù)字5表示五角星的邊數(shù), 其中的數(shù)字2是為了調(diào)整橫向字符間距與縱向行距之間的差異而設(shè)置的, 你也可以不乘以這個(gè)2, 這里只是為了輸出稍微好看一些.

調(diào)整期中數(shù)字5, 你還可以輸出7角星, 9角星.... 注意我的SQL不能輸出6角星,8角星，因?yàn)槲业腟QL算法中是以一筆畫(huà)能夠畫(huà)成的星為基礎(chǔ)設(shè)計(jì)的算法的.

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

在一輪討論之后，newkid 大神給出了一個(gè)系列的SQL改寫(xiě)，小編就列舉如下。

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的寫(xiě)法其實(shí)更簡(jiǎn)單：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替代寫(xiě)法：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;

SQL如神，學(xué)習(xí)入化，動(dòng)手為王，祝愿大家元宵節(jié)快樂(lè)！如何加入"云和恩墨大講堂"微信群

總結(jié)

以上是生活随笔為你收集整理的oracle画圆,元宵佳节：看Oracle技术粉们用SQL画团圆的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。