單條SQL語句實現複雜邏輯的幾個例子(5)

行列轉換，將列中字串以'/'分隔，轉換成行

記錄集如下：

CODE

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

c001/c002/c007

c001/c003

c008/c0011/c029/c023

c004

c102/c111/c112/c144/c167

c008/c029/c023

c008

a/b/c/d/e/f/g/h/i

通過SQL實現如下結果集：

CODE

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

c002

e

h

i

c0011

d

c029

g

c102

a

b

c007

c

c144

c001

c111

c167

c004

c112

c008

c003

c023

f

建表語句如下：

create table tmp5 (code varchar2(50));

insert into tmp5 values ('c001/c002/c007');

insert into tmp5 values ('c001/c003');

insert into tmp5 values ('c008/c0011/c029/c023');

insert into tmp5 values ('c004');

insert into tmp5 values ('c102/c111/c112/c144/c167');

insert into tmp5 values ('c008/c029/c023');

insert into tmp5 values ('c008');

insert into tmp5 values ('a/b/c/d/e/f/g/h/i');

Commit;

解題思路：

行列轉換不少朋友都比較熟悉了，雖然說應用的範圍和機率非常低，但這確實是比較能夠考查sql理解能力的方式，這道題與普通行轉列的最大區別是轉換後的行數不固定，看起來有點麻煩，但是如果你深入理解了第3個示例，再回過頭來看這個，你一定會有種感覺：有點眉目了！

我們這裡也藉助第3例中所說的那種方式，先構造出一個足夠行數的結果集出來：

JSSWEB> select code,rn

2 from tmp5 a,

3 (select rownum rn

4 from dual

5 connect by rownum <=

6 (select max(length(code) - length(replace(code, '/'))) + 1

7 from tmp5))

8 ;

CODE RN

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

c001/c002/c007 1

c001/c003 1

c008/c0011/c029/c023 1

c004 1

c102/c111/c112/c144/c167 1

............

c008/c029/c023 9

c008 9

a/b/c/d/e/f/g/h/i 9

72 rows selected

然後就是根據rn+'/'的位置來判斷每一行應擷取的字串，比如rn為1的時候，就擷取從0到第一個'/'的字元，rn為2時，就擷取從第rn-1到第rn個'/'字元間的位置，特別需要注意的是，一定要判斷好起始位置和結束位置，這裡呢，我們就分成兩步：

第一步decode(rn, 1, 1, instr(code, '/', 1, rn - 1) + length('/'))計算出擷取開始位置，instr(code, '/', 1, rn)結束位置

第二步執行字串擷取：substr(code,st,decode(en,0,length(code),en-st))

JSSWEB> select distinct substr(code,st,decode(en,0,length(code),en-st)) code from(

2 select a.*,

3 decode(rn, 1, 1, instr(code, '/', 1, rn - 1) + length('/')) st,

4 instr(code, '/', 1, rn) en

5 from (select code, rn

6 from tmp5,

7 (select rownum rn

8 from dual

9 connect by rownum <= (select max(length(code) -

10 length(replace(code, '/'))) + 1

11 from tmp5))) a)c

12 where instr(substr(code,st,decode(en,0,length(code),en-st)),'/')=0

13 ;

CODE

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

c002

e

h

i

c0011

d

c029

g

c102

a

b

c007

c

c144

c001

c111

c167

c004

c112

c008

c003

c023

f

23 rows selected

預設沒有排序，當然，想要實現排序也非常簡單，只要在適當位置引入rn即可輕鬆實現，怎麼樣，親自動手去試試吧:)

==================================

檢視前四例：

例4：將列值為0的列替換為距離它最近列的非0值

例3：查詢員工ID：1000的實際工作月數

例2：查詢欄位a的值連續三條以上相同的記錄

例1：按指定規則生成指定商品指定年限銷售額

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7607759/viewspace-246161/，如需轉載，請註明出處，否則將追究法律責任。

單條SQL語句實現複雜邏輯的幾個例子(5)

相關文章