ITPUB SQL大賽第二期（一）

貼一下第二期的第一道題。

第二期題目參考：http://www.itpub.net/thread-1403356-1-1.html

版主newkid點評參考：http://www.itpub.net/thread-1411980-1-1.html

簡單描述一下問題：

問題1(80分)：

在一個6*6的棋盤中，放置12個球，每行、每列、每個正負45度的斜線上最多放置2個球，請用一個SQL語句（不可以用PL/SQL或T-SQL匿名塊、過程或函式，也不可以用Java等外部語言）求出不“重複”的擺法的個數(剔除上下對稱、左右對稱、中心對稱,沿中心點旋轉等各種變形)

對於這個問題，其實就是在第一題的基礎上加大了難度。個人認為最困難地方是搞清楚存在多少種變形的方法，這並沒有什麼太好的辦法，不過好在有第一題的基礎，我們可以在第一題結果上，透過手工畫圖的方式來搞清楚各種變形和原圖形的關係。當然後來題目給出了進一步提示，明確了8種等價的變形，這相當於降低了一部分難度。

SQL>  with c as
  2  (select rownum - 1 c from dual connect by rownum <= 2),
  3  lines as
  4  (select to_number(c1.c || c2.c || c3.c || c4.c || c5.c || c6.c) line, c1.c c1, c2.c c2, c3.c c3, c4.c c4, c5.c c5, c6.c c6
  5  from c c1, c c2, c c3, c c4, c c5, c c6
  6  where c1.c + c2.c + c3.c + c4.c + c5.c + c6.c = 2
  7  order by 1 desc),
  8  result as (select rownum,
  9     ltrim(to_char(line1.line, '099999'))
 10             || chr(10) || ltrim(to_char(line2.line, '099999'))
 11             || chr(10) || ltrim(to_char(line3.line, '099999'))
 12             || chr(10) || ltrim(to_char(line4.line, '099999'))
 13             || chr(10) || ltrim(to_char(line5.line, '099999'))
 14             || chr(10) || ltrim(to_char(line6.line, '099999')) result1,
 15     reverse(ltrim(to_char(line1.line, '099999')))
 16             || chr(10) || reverse(ltrim(to_char(line2.line, '099999')))
 17             || chr(10) || reverse(ltrim(to_char(line3.line, '099999')))
 18             || chr(10) || reverse(ltrim(to_char(line4.line, '099999')))
 19             || chr(10) || reverse(ltrim(to_char(line5.line, '099999')))
 20             || chr(10) || reverse(ltrim(to_char(line6.line, '099999'))) result2,
 21     ltrim(to_char(line6.line, '099999'))
 22             || chr(10) || ltrim(to_char(line5.line, '099999'))
 23             || chr(10) || ltrim(to_char(line4.line, '099999'))
 24             || chr(10) || ltrim(to_char(line3.line, '099999'))
 25             || chr(10) || ltrim(to_char(line2.line, '099999'))
 26             || chr(10) || ltrim(to_char(line1.line, '099999')) result3,
 27     reverse(ltrim(to_char(line6.line, '099999')))
 28             || chr(10) || reverse(ltrim(to_char(line5.line, '099999')))
 29             || chr(10) || reverse(ltrim(to_char(line4.line, '099999')))
 30             || chr(10) || reverse(ltrim(to_char(line3.line, '099999')))
 31             || chr(10) || reverse(ltrim(to_char(line2.line, '099999')))
 32             || chr(10) || reverse(ltrim(to_char(line1.line, '099999'))) result4,
 33     line1.c1 || line2.c1 || line3.c1 || line4.c1 || line5.c1  || line6.c1
 34             || chr(10) || line1.c2 || line2.c2 || line3.c2 || line4.c2 || line5.c2 || line6.c2
 35             || chr(10) || line1.c3 || line2.c3 || line3.c3 || line4.c3 || line5.c3 || line6.c3
 36             || chr(10) || line1.c4 || line2.c4 || line3.c4 || line4.c4 || line5.c4 || line6.c4
 37             || chr(10) || line1.c5 || line2.c5 || line3.c5 || line4.c5 || line5.c5 || line6.c5
 38             || chr(10) || line1.c6 || line2.c6 || line3.c6 || line4.c6 || line5.c6 || line6.c6 result5,
 39     line6.c1 || line5.c1 || line4.c1 || line3.c1 || line2.c1 || line1.c1
 40             || chr(10) || line6.c2 || line5.c2 || line4.c2 || line3.c2 || line2.c2 || line1.c2
 41             || chr(10) || line6.c3 || line5.c3 || line4.c3 || line3.c3 || line2.c3 || line1.c3
 42             || chr(10) || line6.c4 || line5.c4 || line4.c4 || line3.c4 || line2.c4 || line1.c4
 43             || chr(10) || line6.c5 || line5.c5 || line4.c5 || line3.c5 || line2.c5 || line1.c5
 44             || chr(10) || line6.c6 || line5.c6 || line4.c6 || line3.c6 || line2.c6 || line1.c6 result6,
 45     line1.c6 || line2.c6 || line3.c6 || line4.c6 || line5.c6 || line6.c6
 46             || chr(10) || line1.c5 || line2.c5 || line3.c5 || line4.c5 || line5.c5 || line6.c5
 47             || chr(10) || line1.c4 || line2.c4 || line3.c4 || line4.c4 || line5.c4 || line6.c4
 48             || chr(10) || line1.c3 || line2.c3 || line3.c3 || line4.c3 || line5.c3 || line6.c3
 49             || chr(10) || line1.c2 || line2.c2 || line3.c2 || line4.c2 || line5.c2 || line6.c2
 50             || chr(10) || line1.c1 || line2.c1 || line3.c1 || line4.c1 || line5.c1 || line6.c1 result7,
 51     line6.c6 || line5.c6 || line4.c6 || line3.c6 || line2.c6 || line1.c6
 52             || chr(10) || line6.c5 || line5.c5 || line4.c5 || line3.c5 || line2.c5 || line1.c5
 53             || chr(10) || line6.c4 || line5.c4 || line4.c4 || line3.c4 || line2.c4 || line1.c4
 54             || chr(10) || line6.c3 || line5.c3 || line4.c3 || line3.c3 || line2.c3 || line1.c3
 55             || chr(10) || line6.c2 || line5.c2 || line4.c2 || line3.c2 || line2.c2 || line1.c2
 56             || chr(10) || line6.c1 || line5.c1 || line4.c1 || line3.c1 || line2.c1 || line1.c1 result8
 57  from lines line1, lines line2, lines line3, lines line4, lines line5, lines line6
 58  where line1.line + line2.line + line3.line + line4.line + line5.line + line6.line = 222222
 59  and ltrim(to_char(line1.line + 10*line2.line + 100*line3.line + 1000*line4.line + 10000*line5.line + 100000*line6.line), '012') is null
 60  and ltrim(to_char(100000*line1.line + 10000*line2.line + 1000*line3.line + 100*line4.line + 10*line5.line + line6.line), '012') is null
 61  )
 62  select count(distinct greatest(result1, result2, result3, result4, result5, result6, result7, result8)) cn
 63  from result;

        CN
----------
       155

已用時間:  00: 00: 18.65

最先完成的SQL無非就是第一題的基礎上將5*5變成了6*6，然後根據各種變形的規則生成對應的結果。由於8種變形之間可以相互轉換，因此這8種等價的變形的最大值是同一個圖形，這樣透過COUNT DISTINCT的方式，就獲得了去重的解。

雖然結果得到了，但是這個SQL執行速度較慢，需要進一步的最佳化。

SQL>  with c as
  2  (select rownum - 1 c from dual connect by rownum <= 2),
  3  lines as
  4  (select to_number(c1.c || c2.c || c3.c || c4.c || c5.c || c6.c) line, c1.c c1, c2.c c2, c3.c c3, c4.c c4, c5.c c5, c6.c c6
  5  from c c1, c c2, c c3, c c4, c c5, c c6
  6  where c1.c + c2.c + c3.c + c4.c + c5.c + c6.c = 2
  7  ),
  8  result as (select /*+ ordered */
  9     ltrim(to_char(line1.line, '099999'))
 10             || ltrim(to_char(line2.line, '099999'))
 11             || ltrim(to_char(line3.line, '099999'))
 12             || ltrim(to_char(line4.line, '099999'))
 13             || ltrim(to_char(line5.line, '099999'))
 14             || ltrim(to_char(line6.line, '099999')) result1,
 15     reverse(ltrim(to_char(line1.line, '099999')))
 16             || reverse(ltrim(to_char(line2.line, '099999')))
 17             || reverse(ltrim(to_char(line3.line, '099999')))
 18             || reverse(ltrim(to_char(line4.line, '099999')))
 19             || reverse(ltrim(to_char(line5.line, '099999')))
 20             || reverse(ltrim(to_char(line6.line, '099999'))) result2,
 21     ltrim(to_char(line6.line, '099999'))
 22             || ltrim(to_char(line5.line, '099999'))
 23             || ltrim(to_char(line4.line, '099999'))
 24             || ltrim(to_char(line3.line, '099999'))
 25             || ltrim(to_char(line2.line, '099999'))
 26             || ltrim(to_char(line1.line, '099999')) result3,
 27     reverse(ltrim(to_char(line6.line, '099999')))
 28             || reverse(ltrim(to_char(line5.line, '099999')))
 29             || reverse(ltrim(to_char(line4.line, '099999')))
 30             || reverse(ltrim(to_char(line3.line, '099999')))
 31             || reverse(ltrim(to_char(line2.line, '099999')))
 32             || reverse(ltrim(to_char(line1.line, '099999'))) result4,
 33     line1.c1 || line2.c1 || line3.c1 || line4.c1 || line5.c1  || line6.c1
 34             || line1.c2 || line2.c2 || line3.c2 || line4.c2 || line5.c2 || line6.c2
 35             || line1.c3 || line2.c3 || line3.c3 || line4.c3 || line5.c3 || line6.c3
 36             || line1.c4 || line2.c4 || line3.c4 || line4.c4 || line5.c4 || line6.c4
 37             || line1.c5 || line2.c5 || line3.c5 || line4.c5 || line5.c5 || line6.c5
 38             || line1.c6 || line2.c6 || line3.c6 || line4.c6 || line5.c6 || line6.c6 result5,
 39     line6.c1 || line5.c1 || line4.c1 || line3.c1 || line2.c1 || line1.c1
 40             || line6.c2 || line5.c2 || line4.c2 || line3.c2 || line2.c2 || line1.c2
 41             || line6.c3 || line5.c3 || line4.c3 || line3.c3 || line2.c3 || line1.c3
 42             || line6.c4 || line5.c4 || line4.c4 || line3.c4 || line2.c4 || line1.c4
 43             || line6.c5 || line5.c5 || line4.c5 || line3.c5 || line2.c5 || line1.c5
 44             || line6.c6 || line5.c6 || line4.c6 || line3.c6 || line2.c6 || line1.c6 result6,
 45     line1.c6 || line2.c6 || line3.c6 || line4.c6 || line5.c6 || line6.c6
 46             || line1.c5 || line2.c5 || line3.c5 || line4.c5 || line5.c5 || line6.c5
 47             || line1.c4 || line2.c4 || line3.c4 || line4.c4 || line5.c4 || line6.c4
 48             || line1.c3 || line2.c3 || line3.c3 || line4.c3 || line5.c3 || line6.c3
 49             || line1.c2 || line2.c2 || line3.c2 || line4.c2 || line5.c2 || line6.c2
 50             || line1.c1 || line2.c1 || line3.c1 || line4.c1 || line5.c1 || line6.c1 result7,
 51     line6.c6 || line5.c6 || line4.c6 || line3.c6 || line2.c6 || line1.c6
 52             || line6.c5 || line5.c5 || line4.c5 || line3.c5 || line2.c5 || line1.c5
 53             || line6.c4 || line5.c4 || line4.c4 || line3.c4 || line2.c4 || line1.c4
 54             || line6.c3 || line5.c3 || line4.c3 || line3.c3 || line2.c3 || line1.c3
 55             || line6.c2 || line5.c2 || line4.c2 || line3.c2 || line2.c2 || line1.c2
 56             || line6.c1 || line5.c1 || line4.c1 || line3.c1 || line2.c1 || line1.c1 result8
 57  from lines line1, lines line2, lines line3, lines line4, lines line5, lines line6
 58  where line1.line + line2.line + line3.line + line4.line + line5.line + line6.line = 222222
 59  and ltrim(line1.line + line2.line + line3.line, '012') is null
 60  and ltrim(line1.line + line2.line + line3.line + line4.line, '012') is null
 61  and ltrim(line1.line + line2.line + line3.line + line4.line + line5.line, '012') is null
 62  and ltrim(to_char(line1.line + 10*line2.line + 100*line3.line + 1000*line4.line + 10000*line5.line + 100000*line6.line), '012') is null
 63  and ltrim(to_char(line1.line + 10*line2.line + 100*line3.line), '012') is null
 64  and ltrim(to_char(line1.line + 10*line2.line + 100*line3.line + 1000*line4.line), '012') is null
 65  and ltrim(to_char(line1.line + 10*line2.line + 100*line3.line + 1000*line4.line + 10000*line5.line), '012') is null
 66  and ltrim(to_char(100000*line1.line + 10000*line2.line + 1000*line3.line + 100*line4.line + 10*line5.line + line6.line), '012') is null
 67  and ltrim(to_char(100*line1.line + 10*line2.line + line3.line), '012') is null
 68  and ltrim(to_char(1000*line1.line + 100*line2.line + 10*line3.line + line4.line), '012') is null
 69  and ltrim(to_char(10000*line1.line + 1000*line2.line + 100*line3.line + 10*line4.line + line5.line), '012') is null
 70  )
 71  select count(distinct greatest(result1, result2, result3, result4, result5, result6, result7, result8))
 72  from result;

COUNT(DISTINCTGREATEST(RESULT1,RESULT2,RESULT3,RESULT4,RESULT5,RESULT6,RESULT7,RESULT8))
----------------------------------------------------------------------------------------
                                                                                     155

已用時間:  00: 00: 00.64

這裡最佳化的主要思路是，在連線過程中，如果發現當前的組合已經不滿足題目的要求，那麼就儘早過濾掉。比如如果將前三行各個列相加，發現某列的結果已經出現了3，那麼這種變形顯然不可能滿足題目要求，因此就可以被捨棄。所以這裡新增了判斷，當前3行結果的所有變形獲得後，過濾掉列和兩個斜線上出現大於2的情況。同樣在前4行和前5行結果中進行相同的過濾，這使得大量不符合條件的結果沒有參加到後續的連線中，從而極大的提高了效能。

另外之所以新增了/*+ ORDERED */這個提示，是因為在存在6張表連線時，Oracle打亂了連線順序，先從line6開始進行連線，這會導致兩個結果，一是5×5矩陣中正確的排序被打亂，二是前面的最佳化手段將不起作用，因為我們認為連線從line1開始。加上提示後，確保Oracle按照指定的順序進行表連線，從而達到最佳化目的。