Oracle根据某字段分隔符将一条记录拆分为多条记录_William

个人资料

William

微博

正文字体大小：大中小

Oracle根据某字段分隔符将一条记录拆分为多条记录

(2016-08-31 10:38:56)

标签：

分类： MY-ORACLE

直接套用，修改表名和字段名。

(SELECT names,

count1,

substr(replace(contents,'◆',','),l,instr(replace(contents,'◆',',')||',',',',l)-l) as ExamItem

from

(select aaa.ExamItem AS contents,COUNT(1) as count1,aaa.name as names from

aaa having count(1)>=1 group by aaa.ExamItem,aaa.name )A

,((SELECT LEVEL l FROM DUAL CONNECT BY LEVEL <=(select max((length(name)) - LENGTH(REGEXP_REPLACE(REPLACE(name, '◆', '@'), '[^@]+', ''))) from aaa)))

WHERE substr(','||replace(contents,'◆',','),l,1)=',')

实际表

EXAMITEM ID NAME

1 aaa◆bbb 1 aaa◆bbb

2 ccc 2 ccc

3 aaa◆bbb◆ccc 3 aaa◆bbb◆ccc

4 ccc 4 ccc

效果

NAMES COUNT1 EXAMITEM

1 ccc 2 ccc

2 aaa◆bbb 1 aaa

3 aaa◆bbb◆ccc 1 aaa

4 aaa◆bbb 1 bbb

5 aaa◆bbb◆ccc 1 bbb

6 aaa◆bbb◆ccc 1 ccc

阅读┊ 收藏 ┊ 喜欢 ▼ ┊打印┊举报/Report