我們在實際中，會遇到各種各樣的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配合提供的函式集合是非常強大的工具集合。我們只要充分利用這個利器，很多看似複雜和不可能實現的需求就可以方便的實現。

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

相關文章