ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ITPUB SQL大赛第一期

ITPUB SQL大赛第一期

原创 Linux操作系统 作者:yangtingkun 时间:2011-03-15 23:00:39 0 删除 编辑

“盛拓传媒杯”SQL数据库编程大赛第一期的结果已经揭晓了。

 

 

问题的解答和评委的精彩点评请参考:http://www.itpub.net/thread-1407072-1-1.html

本来有了开发版版主newkid的精彩解答和点评,本人实在没有继续献丑的必要,不过考虑到前两天提到了不管结果如何,都会和大家分享,那么还是遵循这个原则,将结果贴出来。

由于newkid版主已经全面分析了题目和结果,因此答案权威的解题方法还请参考上面的链接,我这里主要贴出最原始的版本,第一次优化后的版本以及最终提交版本,希望我的优化过程可以给大家一些启示。

with c as
(select rownum - 1 c from dual connect by rownum <= 2),
line_seed as
(select c1.c || c2.c || c3.c || c4.c || c5.c line, c1.c c1, c2.c c2, c3.c c3, c4.c c4, c5.c c5
from c c1, c c2, c c3, c c4, c c5
where c1.c + c2.c + c3.c + c4.c + c5.c = 2
order by 1 desc)
select rownum, line1.line || line2.line || line3.line || line4.line || line5.line result
from lines line1, line_seed line2, line_seed line3, line_seed line4, line_seed line5
where line1.c1 + line2.c1 + line3.c1 + line4.c1 + line5.c1 = 2
and line1.c2 + line2.c2 + line3.c2 + line4.c2 + line5.c2 = 2
and line1.c3 + line2.c3 + line3.c3 + line4.c3 + line5.c3 = 2
and line1.c4 + line2.c4 + line3.c4 + line4.c4 + line5.c4 = 2
and line1.c5 + line2.c5 + line3.c5 + line4.c5 + line5.c5 = 2
and line3.c1 + line4.c2 + line5.c3 <= 2
and line2.c1 + line3.c2 + line4.c3 + line5.c4 <= 2
and line1.c1 + line2.c2 + line3.c3 + line4.c4 + line5.c5 <= 2
and line1.c2 + line2.c3 + line3.c4 + line4.c5 <= 2
and line1.c3 + line2.c4 + line3.c5 <= 2
and line1.c3 + line2.c2 + line3.c1 <= 2
and line1.c4 + line2.c3 + line3.c2 + line4.c1 <= 2
and line1.c5 + line2.c4 + line3.c3 + line4.c2 + line5.c1 <= 2
and line2.c5 + line3.c4 + line4.c3 + line5.c2 <= 2
and line3.c5 + line4.c4 + line5.c3 <= 2;

这是最初始的版本,主要目的首先是解决问题,至于性能、扩展性和代码的优化,肯定是问题解决之后才去考虑的。

with c as
(select rownum - 1 c from dual connect by rownum <= 2),
lines as
(select c1.c || c2.c || c3.c || c4.c || c5.c line
from c c1, c c2, c c3, c c4, c c5
where c1.c + c2.c + c3.c + c4.c + c5.c = 2
order by 1 desc)
select rownum, to_line1.line || line2.line || line3.line || line4.line || line5.line result
from lines line1, lines line2, lines line3, lines line4, lines line5
where to_number(line1.line) + to_number(line2.line) + to_number(line3.line) + to_number(line4.line) + to_number(line5.line) = 22222
and ltrim(to_char(to_number(line1.line) + 10*to_number(line2.line) + 100*to_number(line3.line) + 1000*to_number(line4.line) + 10000*to_number(line5.line)), '012') is null
and ltrim(to_char(10000*to_number(line1.line) + 1000*to_number(line2.line) + 100*to_number(line3.line) + 10*to_number(line4.line) + to_number(line5.line)), '012') is null;

这是经过优化后的。主要目的是去除原始SQL中的“硬编码”,毕竟通过硬编码方式来解决列和45度斜线的问题显得十分的不专业。至于列和45度斜线的解决思路,主要来源于对具体图形的思考。有时候单纯的抽象思维是很困难的,而画出实际的图形对于思考问题会有很大的帮助。

with c as
(select rownum - 1 c from dual connect by rownum <= 2),
lines as
(select to_number(c1.c || c2.c || c3.c || c4.c || c5.c) line
from c c1, c c2, c c3, c c4, c c5
where c1.c + c2.c + c3.c + c4.c + c5.c = 2
order by 1 desc)
select rownum,
 ltrim(to_char(line1.line, '09999'))
  || ltrim(to_char(line2.line, '09999'))
  || ltrim(to_char(line3.line, '09999'))
  || ltrim(to_char(line4.line, '09999'))
  || ltrim(to_char(line5.line, '00009')) result
from lines line1, lines line2, lines line3, lines line4, lines line5
where line1.line + line2.line + line3.line + line4.line + line5.line = 22222
and ltrim(to_char(line1.line + 10*line2.line + 100*line3.line + 1000*line4.line + 10000*line5.line), '012') is null
and ltrim(to_char(10000*line1.line + 1000*line2.line + 100*line3.line + 10*line4.line + line5.line), '012') is null;

最终SQL主要是进行数值和字符的转换,使得在进行加法和乘法的时候效率更高一些,不过这对于提升整体的性能已经意义不大了。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-689549/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1954
  • 访问量
    10640702