表结构:tbl
---------------------------------------
A (字段名) B(字段名)
he G1,P1,P2
sd G1,P1,C1我想得到这样的数据集:
C (字段名) D(字段名)
he G1
he P1
he P2
sd G1
sd P1
sd C1这样的sql如何写?
---------------------------------------
A (字段名) B(字段名)
he G1,P1,P2
sd G1,P1,C1我想得到这样的数据集:
C (字段名) D(字段名)
he G1
he P1
he P2
sd G1
sd P1
sd C1这样的sql如何写?
通过几个union all + substr + instr来得出结果
---------- ------------------------------
he G1,P1,P2
sd G1,P1,C1
sd G11
sd G11,P11SQL> select a,b from
2 (select a,b,length(b)-length(replace(b,',','')) len from tbl) t where len=0
3 union all
4 select a,substr(b,1,instr(b,',')-1) from
5 (select a,b,length(b)-length(replace(b,',','')) len from tbl) t where len>0
6 union all
7 select a,decode(len,1,substr(b,instr(b,',')+1),
8 substr(b,instr(b,',')+1,instr(b,',',1,2)-instr(b,',')-1)) from
9 (select a,b,length(b)-length(replace(b,',','')) len from tbl) t where len>0
10 union all
11 select a,substr(b,instr(b,',',-1)+1) from
12 (select a,b,length(b)-length(replace(b,',','')) len from tbl) t where len=2;A B
---------- ------------------------------------------------------------
sd G11
he G1
sd G1
sd G11
he P1
sd P1
sd P11
he P2
sd C1已选择9行。SQL>