把字符串 '1,2,3,4,5'，以逗号分隔，输出为行，也就是

1

2

3

4

5

随手写了一个, Oracle 10G 以上

有表如下

SQL>select*fromt;

ID NAME

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

1 0,1,5,2,8,10

2 9,7,8

3 你好,他好,大家好

withvmaxnumas(

selectrownum ele

fromdual

connectbyrownum<=(selectmax(length(name)-length(replace(name,',',null)))+1fromt))

selectid,

decode(pos,0,substr(name,lagpos+1),substr(name,lagpos+1,pos-lagpos-1))name

from(selectid,name,ele,pos,nvl(lag(pos) over (partitionbyidorderbyele),0) lagpos

from(selectid,name,ele,

instr(name,',',1,ele) pos

from(select/*+ all_rows no_merge(v2) use_merge(t,v2) */

t.id,t.name,v2.ele

fromt,vmaxnum v2

whereele<=length(name)-length(replace(name,',',null))+1)))

orderbyid,ele;

ID NAME

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

1 0

1 1

1 5

1 2

1 8

1 10

2 9

2 7

2 8

3 你好

3 他好

3 大家好

12 rows selected.

然后客户那反馈说数据库说9i的，报错说connect by不能使用子查询。

于是再稍做修改，在确定逗号不会超过99个的前提下，直接写个常量100，可以支持9i

withvmaxnumas(

selectrownum ele

fromdual

connectbyrownum<=100)

selectid,

decode(pos,0,substr(name,lagpos+1),substr(name,lagpos+1,pos-lagpos-1))name

from(selectid,name,ele,pos,nvl(lag(pos) over (partitionbyidorderbyele),0) lagpos

from(selectid,name,ele,

instr(name,',',1,ele) pos

from(select/*+ all_rows no_merge(v2) use_merge(t,v2) */

t.*,v2.ele

fromt,vmaxnum v2

whereele<=length(name)-length(replace(name,',',null))+1)))

orderbyid,ele;

后来想想这样也不好，于是建议建立一张特定的IOT表，保存10000个数字，一般够用了。

建立一张IOT,共10000行，同样支持9i

create table tnumber(ele,constraint pk_tnumber primary key(ele)) organization index as

select rownum id from dual connect by rownum<=10000;

然后不再需要常量100，即保证准确，又保证较好的性能

create table t500 nologging as

with   vmaxnum as (

select  ele

from    tnumber

where   ele<=(select max(length(name)-length(replace(name,',',null)))+1 from t))

select id,

decode(pos,0,substr(name,lagpos+1),substr(name,lagpos+1,pos-lagpos-1)) name

from   (select id,name,ele,pos,nvl(lag(pos) over (partition by id order by ele),0) lagpos

from   (select id,name,ele,

instr(name,',',1,ele) pos

from   (select /*+ all_rows no_merge(v2) use_merge(t,v2) */

t.id,t.name,v2.ele

from   t,vmaxnum v2

where  ele<=length(name)-length(replace(name,',',null))+1)))

order  by id,ele;

ID NAME

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

1 0

1 1

1 5

1 2

1 8

1 10

2 9

2 7

2 8

3 你好

3 他好

3 大家好

12 rows selected.

下面测试一下性能：转换50万行试试，测试环境：DELL D630 用了4年半的旧笔记本，磁盘都是碎片

SQL> insert into t select rownum+4,'1,2,3,4' from dual connect by rownum<=500000;

500000 rows created.

SQL> commit;

Commit complete.

set timi on

SQL> set timi on

SQL> create table t500 nologging as

with   vmaxnum as (

select  ele

from    tnumber

where   ele<=(select max(length(name)-length(replace(name,',',null)))+1 from t))

select id,

decode(pos,0,substr(name,lagpos+1),substr(name,lagpos+1,pos-lagpos-1)) name

from   (select id,name,ele,pos,nvl(lag(pos) over (partition by id order by ele),0) lagpos

from   (select id,name,ele,

instr(name,',',1,ele) pos

from   (select /*+ all_rows no_merge(v2) use_merge(t,v2) */

t.id,t.name,v2.ele

from   t,vmaxnum v2

where  ele<=length(name)-length(replace(name,',',null))+1)))

order  by id,ele;

Table created.

Elapsed: 00:00:16.48

耗时16秒，含建表的写盘时间

SQL> select count(*) from t500;

COUNT(*)

----------

2000012

当然还有别的写法，比如简单的可以这样

select  id,substr(name,instr(name,',',1,rownum)+1,instr(name,',',1,rownum+1)-instr(name,',',1,rownum)-1) name

from    (select  id,','||name||',' name from t)

connect by rownum

或者10G以上用正则表达式也可以实现

这个方法可能不是最高效的，但也还可以。

这个SQL的性能和字符串含有的逗号的个数有关，逗号越多，也就是分隔项越多，性能越差。