元宵佳节:看Oracle技术粉们用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解一道数学题:Gauss和Poincare

新年贺礼:云和恩墨大讲堂期刊发行

2015 Oracle 十大热门文章精选

Oracle 12c ASM 防火防盗新特性揭秘

DBA入门之路:学习与进阶之经验谈

DBA入门之路:关于日常工作的建议

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

IT基础架构

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

数据架构

Oracle DB2 MySQL NoSQL

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

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

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

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

应用架构

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

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

原文发表时间:2016-02-22

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏分布式系统和大数据处理

Hive中分区和分桶的概念和操作

在使用传统的RDBMS数据库(关系数据库),例如MySql时,对于一些大表,我们通常会进行分表操作,以提升查询效率。在Hive中也提供了类似的概念和操作,本文将...

1071
来自专栏工科狗和生物喵

【计算机本科补全计划】Mysql 学习小计(4)

正文之前 昨天终于把我苦命的毕业设计审批表送出去了。结果暑假的生产实习开始对账,我这儿又开始忙活了,还要签字,我有时候都在想要不全班代签一遍算了。不然真的揪心啊...

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

闪回原理测试(二)(r11笔记第23天)

对于闪回部分,Oracle本身提供了非常多相关的特性,我个人对于闪回数据库这个特性最为喜爱,尤其是应用再Data Guard环境中,真是一大杀器。 ...

34610
来自专栏架构师之路

一分钟理解负载LoadAverage

一、什么是Load Average? 系统负载(System Load)是系统CPU繁忙程度的度量,即有多少进程在等待被CPU调度(进程等待队列的长度)。 平均...

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

数据库突然宕机的问题及分析

昨天晚上,某个环境的数据库在做一个压力测试的时候突然宕机了。这个问题比较急。马上查看日志文件。 看到了如下的一段,报了os级的linux错误。提示没有空间了。 ...

3468
来自专栏ASP.NET MVC5 后台权限管理系统

ASP.NET MVC5+EF6+EasyUI 后台管理系统(18)-权限管理系统-表数据

这一节,我们插入数据来看看数据流,让各位同学,知道这个权限表交互是怎么一个流程,免得大家后天雾里来雾里去 首先我再解释一些表,SysUser和SysRole表不...

21410
来自专栏数据和云

循序渐进:Oracle 12c新特性Sharding技术解读

引言 数据库构架设计中主要有 Shared Everthting、Shared Nothing 和 Shared Disk: Shared Everthting...

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

看似诡异的tablespace online问题(r5笔记第95天)

今天在做一些演示的时候,在虚拟机上装了两套数据库软件,10g和11g的。还是在演示普通数据文件迁移的时候还是碰到了一些意料之外的问题,从当时的情况来看感觉还是比...

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

备库报警邮件的分析案例(二) (r7笔记第15天)

备库报警邮件的分析案例(一) (r7笔记第14天) 在第一篇中分享了关于备库报警邮件的分析,发现很多问题都是一环扣一环. 起初是通过监控主库中的v$datagu...

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

一次ORA-00600问题的排查和分析(r5笔记第64、65天)

昨天处理了一起ora-00600的错误,其中也经历了各种曲折,真是雾里看花,看透了之后发现很多问题都是有原因的。 起初是开发说有一个job运行的时候报错了,数据...

4089

扫码关注云+社区