oracle简单按分隔符单行转多行sql

       今天看到篇博客，http://www.cnblogs.com/hankuikui/p/3523013.html，说的是按分隔符把一列转成多行，原文给出的方法是：

      

with tmp_t as
 (select '1001' as userId, '10,12,15' as workgroups
    from dual
  union
  select '1002' as userId, '2,4,5' as workgroups from dual)
select userid,
       substr(tempgroups,
              instr(tempgroups, ',', 1, lv) + 1,
              instr(tempgroups, ',', 1, lv + 1) -
              (instr(tempgroups, ',', 1, lv) + 1))
  from (select userid,
               ',' || workgroups || ',' AS tempgroups,
               length(workgroups || ',') -
               nvl(length(replace(workgroups, ',')) ， 0) AS groupcount
          FROM tmp_t) a,
       (select LEVEL lv from dual CONNECT BY LEVEL <= 5) b
 where b.lv <= a.groupcount
 order by userid, lv

    结果为：

   

     使用了CONNECT BY 构造1-5的lv值和instr截取，详细分析见原文。

 

     个人看到connect by level <=5总想这替换掉5，只要得到表中最大的,个数就可以了，如下：

    

with tmp_t as
 (select '1001' as userid, '10,12,15' as workgroups
    from dual
  union
  select '1002' as userid, '2,4,5' as workgroups from dual)
select userid,
       substr(tempgroups,
              instr(tempgroups, ',', 1, lv) + 1,
              instr(tempgroups, ',', 1, lv + 1) -
              (instr(tempgroups, ',', 1, lv) + 1))
  from (select userid,
               ',' || workgroups || ',' as tempgroups,
               length(workgroups || ',') -
               nvl(length(replace(workgroups, ',')) ， 0) as groupcount
          from tmp_t) a,
       (select level lv
          from dual
        connect by level <= (select max(length(workgroups) -
                                        length(replace(workgroups, ',', ''))) + 1
                               from tmp_t)) b
 where b.lv <= a.groupcount
 order by userid, lv

   

    上面的写法简单易懂，恰巧昨天看到篇文章，里面有更好的写法，原文链接：http://www.dewen.org/q/16184/%E4%BB%A5%E5%A4%8D%E6%9D%82%E6%95%B0%E6%8D%AE%E5%AD%97%E6%AE%B5%E5%88%86%E7%BB%84%E6%9F%A5%E8%AF%A2
   

    更好的写法如下：

   

with tmp_t as
 (select '1001' as userid, '10,12,15' as workgroups
    from dual
  union
  select '1002' as userid, '2,4,5' as workgroups from dual)
select userid,
       --regexp_substr(str,reg,起始位置 第几次)
       regexp_substr(workgroups, '[^,]+', 1, level) clr
  from tmp_t
connect by level <= regexp_count(workgroups, ',') + 1
       --regexp_count(workgroups, ',') 统计字符串中,的数量
       --用法参考http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions135.htm#SQLRF51665
       and userid = prior userid
       and prior dbms_random.value is not null

    相当简洁，很不错的写法，结果和上面的一样。

 

    全文完。