比如一个table
name others
小A yyy#mmm#dd
怎么写可以使查询结果变成如下
小A yyy
小A mmm
小A dd
谢谢！！！
------解决方案--------------------
- SQL code
--这个WITH t1 AS (SELECT NAME, '#' || OTHERS OTHERS FROM temp)SELECT DISTINCT NAME, substr(t1.others, instr(t1.others, '#', 1, LEVEL) + 1, decode(instr(t1.others, '#', 1, LEVEL + 1), 0, length(OTHERS) + 1, instr(t1.others, '#', 1, LEVEL + 1)) - instr(t1.others, '#', 1, LEVEL) - 1) s FROM dual, t1CONNECT BY LEVEL <= (SELECT length(OTHERS) - length(REPLACE(OTHERS, '#')) FROM t1 b WHERE b.name = t1.name)ORDER BY NAME;
------解决方案--------------------
SQL> select * from ta;
NAME OTHERS
---- ----------
小A yyy#mmm#dd
小B zzz#nnn#ss
SQL>
SQL> select distinct name,
2 substr('#' || others || '#',
3 instr('#' || others || '#' , '#', 1, level) +1,
4 instr('#' || others || '#' , '#', 1, level + 1) -
5 instr('#' || others || '#' , '#', 1, level)-1) others
6 from ta
7 connect by level <= (length(others)-length(replace(others,'#')))/length('#') +1
8 order by name;
NAME OTHERS
---- ------------------------
小A dd
小A mmm
小A yyy
小B nnn
小B ss
小B zzz
6 rows selected