第一屆SQL大賽第一期優秀解題思路彙總

SQL大賽第一期解題方法的公佈，讓我領略了各種新穎巧妙的解題方法。精神也為之一振，就決定寫篇總結，把大家好的方法記下來。

第一期題目大意如下：
給定一個5×5的矩陣，要求：
1、每行不得超過兩個球；
2、每列不得超過兩個球；
3、每個45度斜線不得超過兩個球（包括左傾斜和右傾斜，及對角線）
問：這樣的擺法有多少種。

解答要求：
以單個SQL語句實現。
通用性（比如說，可擴充套件到三個球四個球，或者6階矩陣等）
效率。也就是執行時間。
其他要求就不一一敘述了。

按照以上這個題目及要求，大家都給出了各式奇思妙想。下面是所有答題結果中，我認為比較有代表性或創造性的思路的彙總，並不包含全部的解題方法。其中有些解題過程很長，或者都還沒格式化，比較亂，看得我比較眼痠（由此可見，評委要全部仔細的看完更是辛苦啊！），都沒仔細看。

其中IR的得分很高，我也特意留意了下他的解題方法，不過他用的遞迴with需要在11g環境下執行，但是看newkid的分析感覺挺不錯的。以後有環境再分析下。

這個題目主要要解決的是兩個方面的問題：基礎資料的構造和斜線的相加

一、基礎資料構造

***************************************************************
1、遞迴法（這是我自己的方法(GO)，所以先拿出來曬曬）
***************************************************************

這個方法利用了connect by構造資料的特性，首先構造出基礎資料：5個0和2個1.
SQL> select floor(rownum/(dim + cnt - 1)) n, rownum r, dim
  2    from dual, (select 5 dim, length('11') cnt  from dual) dims
  3  connect by rownum <= dim+cnt
  4  /
 
         N          R        DIM
---------- ---------- ----------
         0          1          5
         .................
         0          5          5
         1          6          5
         1          7          5
 
7 rows selected

構造這個資料的目的是可以透過R列，來實現不同遞迴層次的關聯而得到不同組合的01字串。但是這個方法有個缺陷，就是隻能侷限於5+2模式（即5×5矩陣，每行最多兩個球的模式），這個newkid也提到了。所以在第二個的解答中我已經將這個方法擴充套件了：
SQL> with
  2  dims as(select 5 dim, 2 cnt from dual)
  3  select decode(sign(rownum-dim),1,1,0) n, rownum r, dim
  4    from dual, dims
  5  connect by rownum <= dim+cnt
  6  /
 
         N          R        DIM
---------- ---------- ----------
         0          1          5
         ...................
         0          5          5
         1          6          5
         1          7          5
 
7 rows selected

這樣就不再侷限於5+2，而是適用於任意情況的組合了，根據情況只要修改相應的數值即可：
SQL> with
  2  dims as(select 6 dim, 2 cnt from dual)
  3  select decode(sign(rownum-dim),1,1,0) n, rownum r, dim
  4    from dual, dims
  5  connect by rownum <= dim+cnt
  6  /
 
         N          R        DIM
---------- ---------- ----------
         0          1          6
         ..................
         0          6          6
         1          7          6
         1          8          6
 
8 rows selected

構造了這個基本資料後，我們就可以再套一層層次查詢，把這些不同組合的字串取出來：
SQL> with
  2  dims as(select 5 dim, 2 cnt from dual),
  3  nums as(
  4  select n, to_number(substr(n, 1, 1)) n1,
  5         to_number(substr(n, 2, 1)) n2, to_number(substr(n, 3, 1)) n3,
  6         to_number(substr(n, 4, 1)) n4, to_number(substr(n, 5, 1)) n5
  7    from (select distinct replace(sys_connect_by_path(n, ','), ',') n
  8            from (select decode(sign(rownum-dim),1,1,0) n, rownum r, dim
  9                    from dual, dims
 10                  connect by rownum <= dim+cnt)
 11           where level = dim
 12          connect by nocycle prior r <> r and level <= dim))
 13  select * from nums
 14  /
 
N                   N1         N2         N3         N4         N5
----------- ---------- ---------- ---------- ---------- ----------
00001                0          0          0          0          1
.................................
10001                1          0          0          0          1
11000                1          1          0          0          0
 
16 rows selected


***************************************************************
2、逐個遞推法
***************************************************************

這個是LL的方法。說實話，LL的方法很巧妙，要我我怎麼也想不到那裡去。我覺得跟他得的得分一樣有點慚愧，呵呵。他的思路有點類似於我們人的推理，先在第一個位置固定放1，然後在其後的每個位置上向右逐個移動的放1，得到四種情況。
然後在第二個位置固定放1，而後在其後的位置上再逐個移動的放1.依次類推，便得到了10組。但是這個得有個前提，即必須是得事先確定，所有行都是兩個球，否則的話還少了0個和1個球的6中情況。這個是它的不足之處。
【程式碼】：
SQL> with t1 as
  2  (
  3  select iid, x, y, x + y + 25 z1, x - y z2
  4    from (select rownum iid, mod(rownum - 1, 5) + 1 x, ceil(rownum / 5) y
  5            from dual
  6          connect by rownum <= 25)
  7    )
  8  select x,y,substr('00000', 1, y - 1) || '1' ||
  9         substr('00000', y, x - y - 1) || '1' ||
 10         substr('00000', x, 5 - x) rr
 11    from t1
 12   where x > y
 13     and x != y
 14  /
 
         X          Y RR
---------- ---------- --------------------------------
         2          1 11000
         .........
         5          3 00101
         5          4 00011
 
10 rows selected

***************************************************************
3、排列組合法
***************************************************************

這個方法的思路有點類似於我們學機率論時的思路，我想，CA和VL學機率論學的比較好，所以想問題的出發點也是基於機率的。首先，我們知道，5×5的矩陣，每個位置只有0和1兩種可能，所以5×5的矩陣，只要將每個位置的兩種可能性用笛卡爾積乘起來，就能得到所有的擺法了。不過，寫的有點冗長了。
【程式碼】
(select 0 v11 from dual union all select 1 from dual) a1,
.................
(select 0 v55 from dual union all select 1 from dual) a25
這個可以先用一個with子句構造一個tmp表，然後在from用引用即可。

其中,TK和JJ用的也是類似的方法。不過他們是首先得到一行的所有擺法。然後再擴充套件到5行的所有擺法。比CA簡潔很多。
【程式碼】
SQL> with c as
  2   (select rownum - 1 c from dual connect by rownum <= 2)
  3  select to_number (c1.c || c2.c || c3.c || c4.c || c5.c) line
  4    from c c1, c c2, c c3, c c4, c c5
  5   where c1.c + c2.c + c3.c + c4.c + c5.c = 2
  6   order by 1 desc
  7  /
 
      LINE
----------
     11000
     .....
       101
        11
 
10 rows selected
 
***************************************************************
4、二進位制構造（想不出好的名字，就這麼命名了）
***************************************************************

這個方法是先不管多少個球，反正每個位置兩種可能，總共有2^5=32種情況給全部列出來。列舉的方式很明顯是基於二進位制轉換的思路，即將0-31的每個數字都轉換為二進位制，就可得到這些不同組合的字串了。
【程式碼】
SQL>  select col1 || col2 || col3 || col4 || col5 strings,
  2          col1 + col2 + col3 + col4 + col5 balls,
  3          r.*
  4     from (select mod(floor(rownum / 16), 2) col1,
  5                  mod(floor(rownum / 8), 2) col2,
  6                  mod(floor(rownum / 4), 2) col3,
  7                  mod(floor(rownum / 2), 2) col4,
  8                  mod(floor(rownum), 2) col5
  9             from dual
 10           connect by rownum <= 32) r
 11    where col1 + col2 + col3 + col4 + col5 <= 5
 12    ORDER BY 1 desc
 13  /
 
STRINGS           BALLS       COL1       COL2       COL3       COL4       COL5
------------ ---------- ---------- ---------- ---------- ---------- ----------
11111                5          1          1          1          1          1
..............
00001                1          0          0          0          0          1
00000                0          0          0          0          0          0
 
32 rows selected
 

以上列舉了構造資料的幾個主要演算法，其他的也基本上類似。
上面，我們完成了題目的第一個關鍵部分：資料構造。接下來就是斜線處理了。


二、斜線的判斷與處理

斜線處理思路更加新穎巧妙，主要那麼三類：

***************************************************************
1、座標點相加法（這個是我，也是其他很多人用的）
***************************************************************

這個稱謂看上去好像很了不起，但是實際上這是最土、最冗長、也是擴充套件性最差的辦法了。本法是基於最基本的5×5圖形的，在得到每個點的值後，將相應45度斜線的座標點加起來，就得到斜線和了。每條斜線都得這麼加，還得看清楚了別寫錯了就功虧一簣了。
【程式碼】
n15 + n24 + n33 + n42 + n51 <= 2 --對角線相加
n21 + n32 + n43 + n54 <= 2
n31 + n42 + n53 <= 2
n41 + n52 <= 2
n12 + n23 + n34 + n45 <= 2
n13 + n24 + n35 <= 2
n14 + n24 <= 2--右傾斜
n14 + n23 + n32 + n41 <= 2
n31 + n22 + n13 <= 2
n21 + n12 <= 2
n52 + n43 + n34 + n25 <= 2
n53 + n44 + n35 <= 2
n45 + n54 <= 2

***************************************************************
2、移位相加法
***************************************************************

這個方法灰常巧妙啊，包括TK和newkid用的都是這個。對此，我除了讚歎...........還是讚歎。TK僅用了17行就搞定了，而我用了43行。
言歸正傳，什麼是移位相加法呢？看下面的圖就明白了：
A B C D E
F G H I J
K L M N O
P Q R S T
U V W X Y

上面移位後：
        A B C D E
      F G H I J
    K L M N O
  P Q R S T
U V W X Y

第一列：U
第二列：P V
第三列：K Q W
第四列：F L R X
第五列：A G M S Y
第六列：B H N T
第七列：C I O
第八列：D J
第九列：E
仔細觀察一下，這些得到的列，不正是我們要取的斜線嘛。只要這些列作sum，就能得到我們想要的判斷了。

當然，別忘了右傾斜：
A B C D E
  F G H I J
    K L M N O
      P Q R S T
        U V W X Y

具體做法麼，只要給每行的數值乘以10的N倍(N為行號-1， 即=0...4)即可。
【程式碼】：
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;


這個解法的要求是每個位置的數值最大隻能是9，且斜線相加不能超過9。但是對於此題已經綽綽有餘了。


***************************************************************
3、等值分組法
***************************************************************

此法我只在LL這邊看到了，這是LL真正讓我感覺巧妙的地方。他首先列出了矩陣的所有座標點，然後對這些座標點標號。並且在給出每個座標點，橫座標和縱座標的差值。這個正是該法的精髓所在，它直接為後面的斜線相加做了鋪墊。接下來就是裁剪，去掉四個無需判斷的頂點，把這些斜線點變成行列成一個結果集。然後與構造的5+2組合進行笛卡爾積。再按照各個斜線點的座標標號到組合的字串中取出這些點，並對這些點相加，得到斜線相加的結果。然後取斜線最大點不大於2即可。
【程式碼】：
with t1 as
(--構造座標點，對座標點標號，並取每個座標點的橫豎座標差和和
select iid, x, y, x + y + 25 z1, x - y z2
  from (select rownum iid, mod(rownum - 1, 5) + 1 x, ceil(rownum / 5) y
          from dual
        connect by rownum <= 25)
  )
,t2 as (
--取出每條斜線座標點數大於2的集合
select iid, zz
  from (select iid, z1 zz, count(*) over(partition by z1) cnt
          from t1
        union all
        select iid, z2, count(*) over(partition by z2) cnt from t1)
 where cnt > 2
)
,t3 as (--構造5+2
select rr,
       to_number(substr(rr, 1, 1)) c1,
       to_number(substr(rr, 2, 1)) c2,
       to_number(substr(rr, 3, 1)) c3,
       to_number(substr(rr, 4, 1)) c4,
       to_number(substr(rr, 5, 1)) c5
  from (select substr('00000', 1, y - 1) || '1' ||
               substr('00000', y, x - y - 1) || '1' ||
               substr('00000', x, 5 - x) rr
          from t1
         where x > y
           and x != y)
)
select rownum, chr
  from (select chr
          from (select a.chr,
                       b.zz,
                       sum(to_number(substr(a.chr, b.iid, 1))) n1
                  from (select r1.rr || r2.rr || r3.rr || r4.rr || r5.rr chr
                          from t3 r1, t3 r2, t3 r3, t3 r4, t3 r5
                         where r1.c1 + r2.c1 + r3.c1 + r4.c1 + r5.c1 < 3
                           and r1.c2 + r2.c2 + r3.c2 + r4.c2 + r5.c2 < 3
                           and r1.c3 + r2.c3 + r3.c3 + r4.c3 + r5.c3 < 3
                           and r1.c4 + r2.c4 + r3.c4 + r4.c4 + r5.c4 < 3
                           and r1.c5 + r2.c5 + r3.c5 + r4.c5 + r5.c5 < 3) a,
                       t2 b
                 group by a.chr, b.zz)
         group by chr
        having max(n1) < 3
         order by chr desc);

三、 總結
        按道理，每次寫blog，都應該寫篇總結，尤其是這篇總結的總結。但是由於每次寫完blog，能夠一樣樣寫好並舉例，都感到很疲勞了，本身已經很不易，再寫個結論更累。呵呵，所以這次結論是在這篇原始版的blog寫完後幾天後才補充的。

        說了這麼多，開始寫結論吧。

        這次題目，主要思想是解決N×N矩陣中，球體擺放的問題。
        面對的最主要問題是如何用只能生成行資料的一個SQL語句來解決二維的矩陣資料的生成和條件的判斷。
        因此，我們很自然的把問題分解為兩個方面：
        一、資料的生成
        二、資料的判斷
        資料的生成，我們又可以按如下思路來考慮：
        首先，按照題目要求，是5×5的矩陣，且每行不得超過2個球，所以，每個行他的可能性只有：
C(5,2) +C(5,1) + 1 = 10 + 5 + 1 = 16種
也就是說，放兩個球有C(5,2)種可能性，放1個球有C(5,1)種可能性，沒有球，則只有一種可能性，加起來就是16種。
        於是，我們得到了構造一行所需要的16種基本資料，其他行也是這個規則。於是總的可能性就有：
16^5 = 1048576種。
         現在，我們要解決的就是如何從這100多萬種方案中篩選出題目所要求的矩陣了。於是，下一步的問題就變成了資料的判斷了。其實，資料的判斷雖然在方法上比資料的構造多種多樣，但是本質卻是一致的。就是判斷斜線的和不大於2。沿著這個思路，大家充分發揮了想象力，看到其他人尤其是幾位得分靠前的高手的答案後，我真是驚歎，大千世界，學無止境啊。
         其中的對斜線分組法及移位相加法，是我很崇拜的方法，因為其擴充套件性和效率都很好，而且也很簡潔。這個是這次大賽值得我們學習的地方。