元宵快乐:看SQL大师们用SQL绘制的团圆

题记在多年以前,论坛活跃的时代,在ITPUB上你能看到各种新奇有趣的知识,及时新鲜的信息,出类拔萃的技巧,有很多让人多年以后还记忆犹新。

这个帖子让我忍不住在这个日子,再次发送出来,让大家一起再次体会SQL的强大和神奇能力。而写好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执行:

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

SQL如神,学习入化,动手为王,祝愿大家元宵节快乐!

还有一些神奇的文章:

无往不利:用SQL解海盗分金的利益最大化问题

资源下载

关注公众号:数据和云(OraNews)回复关键字获取

2017DTC,2017 DTC 大会 PPT

DBALIFE,“DBA 的一天”海报

DBA04,DBA 手记4 经典篇章电子书

RACV1, RAC 系列课程视频及 PPT

122ARCH,Oracle 12.2 体系结构图

2017OOW,Oracle OpenWorld 资料

PRELECTION,大讲堂讲师课程资料

云和恩墨

数据驱动,成就未来。整合业界顶尖的技术与合作伙伴资源,围绕数据及相关领域,提供解决方案和专业服务。

业务架构

电子渠道(网络销售)分析系统、数据治理

IT基础架构

分布式存储解决方案 | zData一体机 | 容灾环境建设

数据架构

Oracle DB2 MySQL NoSQL

专项服务:架构/安全/容灾/优化/整合/升级/迁移

运维服务:运维服务 代维服务

人才培养:个人认证 企业内训

软件产品:SQL审核、监控、数据恢复

应用架构

应用软件和中间件:数据建模 | SQL审核和优化 | 中间件服务

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2018-03-01

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏编程之路

教师听课评课系统设计

需求分析:管理教师安排任课教师课程,安排听课教师,听课教师到教室听课,使用手机记录学生表现、教师表现、综合表现、以及建议,告别纸质化。课后授课教师查看所有听课人...

23340
来自专栏Grace development

基于TP的每日签到代码

36530
来自专栏java学习

Oracle基础试题与答案!

表结构: create table tbEmp --职员表 ( eID number(7) primarykey, ...

386120
来自专栏码神联盟

碎片化 | 第四阶段-49-hibernate之HQL查询操作-视频

本套视频从Java基础到架构模式以及AI算法,整体视频以“碎片化”学习的模式,提供给大家 ,并配备实际项目为案例,让大家在坐车、吃饭、午休、蹲坑的时候,都可以学...

35560
来自专栏数据小魔方

MySQL入门学习笔记——七周数据分析师实战作业

本篇推送主要涉及SQL语言中较为复杂的子查询与函数嵌套。 虽然这个MySQL系列取名为MySQL基础入门,但是个人不打算做单个函数的用法总结,或者说简单罗列,...

54170
来自专栏Aloys的开发之路

SQL Server常用语句

-- 身份证验证(SQLServer) -- 主要验证SQL数据库中已输入的15位 及18位 身份证号码的位数、出生年月日是否正确,可以过滤出大部分的输入错误...

25050
来自专栏java架构学习交流

java web轻量级开发面试教程读书笔记:建索引时我们需要权衡的因素

场景一,数据表规模不大,就几千行,即使不建索引,查询语句的返回时间也不长,这时建索引的意义就不大。当然,若就几千行,索引所占的空间也不多,所以这种情况下,顶多属...

226100
来自专栏杨建荣的学习笔记

MySQL 5.6,5.7的优化器对于count(*)的处理方式

最近看了很多阿里同学的MySQL文章,阿里内核同学的文章一言不合就上代码,不光让我们看到了结果,还能有代码可读,如果碰到了类似的问题,这样的解读确实是很难...

39760
来自专栏杨建荣的学习笔记

一条"简单"的sql语句和小兔子买面包的故事 (r3笔记第25天)

有时候开发人员写sql语句的时候,接触的性能问题越多,可能对sql语句的结构,性能考虑会多一些,这也是一件好事,不过如果考虑不当,本来原本想做的的一些优化却使得...

28350
来自专栏闻道于事

数据库 105道题目整理与吐血总结

第一波题目 drop table PRODUCT cascade constraints; create table PRODUCT ( id NUMBER n...

67990

扫码关注云+社区

领取腾讯云代金券