求助oracle的sql语句

Question

ID                                                             Name
1,3,4                                                         苹果,香蕉,桔子
2,1                                                            西瓜,苹果
2,4,6                                                         西瓜,桔子,梨子
5,1,4,6,2                                                   葡萄,苹果,桔子,梨子,西瓜要得到下面的列表
id           name
1            苹果
2            西瓜
3            香蕉
4            桔子
5            葡萄
6            梨子
求助各位大侠，这个sql语句怎么写？

Answer 1

这个如果可以用存储过程写入临时表的话相对而言简单点，用一条sql写出来 好像没啥思路，静候SQL大神给出代码

Answer 2

with tab1 as (
select 'r1' rowidd, '1,3,4' id, '苹果,香蕉,桔子' name from dual union all
select 'r2' rowidd, '2,4,6' id, '西瓜,桔子,梨子' name from dual 
)
select distinct
       regexp_substr(t1.id, '[^,]+', 1, level) id,
       regexp_substr(t1.name, '[^,]+', 1, level) name
  from tab1 t1
connect by prior t1.rowidd = t1.rowidd
    and prior sys_guid() is not null
    and level <= regexp_count(t1.id, ',') + 1
order by regexp_substr(t1.id, '[^,]+', 1, level)
;

Answer 3

大致就是这样，可以得出楼主要的效果，可以扩大level的级别以适应更长的字符串。
SELECT DISTINCT Id, NAME
  FROM (SELECT Regexp_Substr(a.Id, '[^,]+', 1, LEVEL) Id,
               Regexp_Substr(a.Name, '[^,]+', 1, LEVEL) NAME
          FROM (SELECT '1,3,4' Id, '苹果,香蕉,桔子' NAME
                  FROM Dual
                UNION ALL
                SELECT '2,1' Id, '西瓜,苹果' NAME
                  FROM Dual
                UNION ALL
                SELECT '2,4,6' Id, '西瓜,桔子,梨子' NAME
                  FROM Dual
                UNION ALL
                SELECT '5,1,4,6,2' Id, '葡萄,苹果,桔子,梨子,西瓜' NAME
                  FROM Dual) a
        CONNECT BY LEVEL <= 3)
 WHERE Id IS NOT NULL
 ORDER BY ID;以下语句是为了伪造数据，可以替换为实际的表
(SELECT '1,3,4' Id, '苹果,香蕉,桔子' NAME
                  FROM Dual
                UNION ALL
                SELECT '2,1' Id, '西瓜,苹果' NAME
                  FROM Dual
                UNION ALL
                SELECT '2,4,6' Id, '西瓜,桔子,梨子' NAME
                  FROM Dual
                UNION ALL
                SELECT '5,1,4,6,2' Id, '葡萄,苹果,桔子,梨子,西瓜' NAME
                  FROM Dual) a

Answer 4

查询结果错误，比如下面的语句查不到西瓜
SELECT DISTINCT Id, NAME
  FROM (SELECT Regexp_Substr(a.Id, '[^,]+', 1, LEVEL) Id,
               Regexp_Substr(a.Name, '[^,]+', 1, LEVEL) NAME
          FROM (SELECT '1,3,4' Id, '苹果,香蕉,桔子' NAME
                  FROM Dual
                UNION ALL
                SELECT '1' Id, '苹果' NAME
                  FROM Dual
                UNION ALL
                SELECT '4,6' Id, '桔子,梨子' NAME
                  FROM Dual
                UNION ALL
                SELECT '5,1,4,6,2' Id, '葡萄,苹果,桔子,梨子,西瓜' NAME
                  FROM Dual) a
        CONNECT BY LEVEL <= 3)
 WHERE Id IS NOT NULL
 ORDER BY ID;而且连接条件不全，会使中间数据过大（13条数据你查出了84条），严重时可能会查不出数据。按照你的语句，中间数据会随着level与基础数据条数成指数增长。对比下面两个查询的效率就知道了。
with tab1 as (
select 'r1' rowidd, '5,1,4,6,2' id, '葡萄,苹果,桔子,梨子,西瓜' name from dual
connect by level <= 1000
)
select distinct
       regexp_substr(t1.id, '[^,]+', 1, level) id,
       regexp_substr(t1.name, '[^,]+', 1, level) name
  from tab1 t1
connect by prior t1.rowidd = t1.rowidd
    and prior sys_guid() is not null
    and level <= regexp_count(t1.id, ',') + 1
order by regexp_substr(t1.id, '[^,]+', 1, level)
;SELECT DISTINCT Id, NAME
  FROM (SELECT Regexp_Substr(a.Id, '[^,]+', 1, LEVEL) Id,
               Regexp_Substr(a.Name, '[^,]+', 1, LEVEL) NAME
          FROM (select 'r1' rowidd, '5,1,4,6,2' id, '葡萄,苹果,桔子,梨子,西瓜' name from dual
connect by level <= 1000) a
        CONNECT BY LEVEL <= 5)
 WHERE Id IS NOT NULL
 ORDER BY ID;