ITPUB SQL大賽第一期

“盛拓傳媒杯”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主要是進行數值和字元的轉換，使得在進行加法和乘法的時候效率更高一些，不過這對於提升整體的效能已經意義不大了。