一個使用SQL語句解決的小問題

 

我們在實際中，會遇到各種各樣的SQL編寫需求。由於SQL語句的特點，一些看似在過程化語句中很困難的問題，藉助一點點SQL技巧和經驗，就可以獲得意想不到的處理。

 

一個朋友在解決一個複雜問題時，將一個步驟問題諮詢筆者。雖然在大牛們眼中不費吹灰之力，但是筆者覺得還是有必要寫出來，作為將來遇到同類問題朋友們的一點點啟發。

 

1、問題說明

 

原始問題比較複雜，這裡只是提供一個簡裝本。注意：問題要求在10g上進行實現，這也就限制了一些Oracle特性，特別是分析函式的使用。

 

原始資料如下：

 

 

SQL> select * from v$version;

 

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE        10.2.0.1.0         Production

 

SQL> select * from t;

       ID1        ID2

---------- ----------

         1          1

         2          2

         3          2

         4          2

         5          1

         6          2

         7          2

         8          2

 

8 rows selected

 

 

資料表列id1和id2，id1為sequence生成的主鍵列，顯示結果按照id1進行排序。Id1取值單向增加，但是不一定連續。Id2為分組資訊列，當按照id1進行排列的時候，取值為1的表示一組開始。順序下取值為2的資料行歸屬在相同組，直到新一個取值1開始。

 

現在要求形成資料列，標記列名稱，分別按照a，b，c…順序下去。注意，每組數量不定。要求利用一句SQL解決，形成預期結果如下：

 

 

    

 ID1        ID2

---------- ----------

         1          1       a

         2          2       a

         3          2       a

         4          2       a

         5          1       b

         6          2       b

         7          2       b

         8          2       b

 

 

2、問題分析

 

直觀的感覺和我們常見的複雜SQL有點差別。之後的分組需求很類似於oracle的分組函式或者層次查詢，但是又沒有明顯的特徵和層級關係。而且組的關係是建立在id1序列和id2共同確定的。所以，直接使用分組函式和層級函式的想法可能有點問題。

 

第二個難點是關於分組的名稱，abcd序列相差的關係，也就是組的差值1怎麼體現？

 

首先，筆者希望將組的概念體現出來，也就是將id=1體現出來。筆者試著寫一下。

 

 

SQL> select id1, id2, decode(id2,1,1,0) from t;

 

       ID1        ID2 DECODE(ID2,1,1,0)

---------- ---------- -----------------

         1          1                 1

         2          2                 0

         3          2                 0

         4          2                 0

         5          1                 1

         6          2                 0

         7          2                 0

         8          2                 0

 

8 rows selected

 

 

在第三列，每個組開頭的資料行，都被設定為1，其餘是0。之後，我們需要將不同的組分割開，此時，可以藉助分析函式的累計和方法。對第三列進行累計處理，處理之後，可以方便的分出組來。

 

 

SQL> select id1, id2, sum(decode(id2,1,1,0)) over (order by id1) from t;

 

       ID1        ID2 SUM(DECODE(ID2,1,1,0))OVER(ORD

---------- ---------- ------------------------------

         1          1                              1

         2          2                              1

         3          2                              1

         4          2                              1

         5          1                              2

         6          2                              2

         7          2                              2

         8          2                              2

 

8 rows selected

 

 

Sum over是我們最常使用的分析函式。依次累計，就可以區分出資料組來。

 

最後一步就是將這些差值變為字串a,b,c，可以使用chr函式。

 

 

SQL> select id1, id2, chr((sum(decode(id2,1,1,0)) over (order by id1))+96) res from t;

 

       ID1        ID2 RES

---------- ---------- ---

         1          1 a

         2          2 a

         3          2 a

         4          2 a

         5          1 b

         6          2 b

         7          2 b

         8          2 b

 

8 rows selected

 

 

當資料變化的時候，也是這樣。

 

 

SQL> select id1, id2, chr((sum(decode(id2,1,1,0)) over (order by id1))+96) res from t;

 

       ID1        ID2 RES

---------- ---------- ---

         1          1 a

         2          2 a

         3          2 a

         4          2 a

         5          1 b

         6          2 b

         7          2 b

         8          2 b

        10          2 b

        11          1 c

        12          2 c

 

11 rows selected

 

 

3、結論

 

Oracle SQL配合提供的函式集合是非常強大的工具集合。我們只要充分利用這個利器，很多看似複雜和不可能實現的需求就可以方便的實現。