元宵快乐:看SQL大师们用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执行:

6


好吧,这是五个连环,事实上是奥运会的五环旗,在庆祝奥运期间,网友 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的解释如下:

7


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

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

比如,以下是7角形输出:

8


在一轮讨论之后,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;

原文发布时间为:2018-03-1
本文作者:Nyfor&Newkid
本文来自云栖社区合作伙伴“数据和云”,了解相关信息可以关注“数据和云”微信公众号

相关文章
|
2月前
|
SQL 数据挖掘
SQLBolt,一个练习SQL的宝藏网站
SQLBolt,一个练习SQL的宝藏网站
|
2月前
|
SQL 资源调度 分布式计算
一个SQL任务的一生
一条SQL语句的执行究竟经历了哪些过程?作者作为一个刚入职的大数据研发新人对SQL任务执行整个流程进行了整理,本文就作者学习内容和体会供大家参考。
|
5月前
|
SQL 数据库 数据库管理
《SQL必知必会》读后感(一)
《SQL必知必会》读后感(一)
36 0
|
5月前
|
SQL 关系型数据库 MySQL
零基础快速自学SQL,2天足矣
零基础快速自学SQL,2天足矣
99 0
|
SQL 移动开发 Oracle
【SQL应知应会】分析函数的点点滴滴(二)
【SQL应知应会】分析函数的点点滴滴(三)
110 0
|
SQL 移动开发 Oracle
【SQL应知应会】分析函数的点点滴滴(三)
【SQL应知应会】分析函数的点点滴滴(三)
130 0
sql做题第四天
sql语法必做练习
|
SQL 数据库 索引
热饭面试复习:【sql相关】-高级 (上)
热饭面试复习:【sql相关】-高级 (上)
热饭面试复习:【sql相关】-高级 (上)
热饭面试复习:【sql相关】-函数 (完)
热饭面试复习:【sql相关】-函数 (完)
热饭面试复习:【sql相关】-高级 (下)
热饭面试复习:【sql相关】-高级 (下)