ITPub博客

首页 > 数据库 > Oracle > 通过一条SQL将 (1,2,3,4) 显示 (123,234) 的形式

通过一条SQL将 (1,2,3,4) 显示 (123,234) 的形式

原创 Oracle 作者:liuxiaoxiao609 时间:2014-03-12 14:55:08 0 删除 编辑

   题面是这样的,一个表有某一列 如: 1,2,3,4,5 ......要求查询出来的每连续三个数字用逗号分隔,显示 如:123,234,345 ;该列长度不定。

创建测试表

  1. -- 1.创建一测试表
  2. create table tmp(c varchar2(2000));

  3. -- 2.插入一些测试数据
  4. truncate table tmp;
  5. insert into tmp values('1,2,3,4,5,6');
  6. insert into tmp values('1,2,3');
  7. insert into tmp values('1,2,3,4,5,6,7,8,9');
  8. commit;


具体实现如下 TAG~

  1. -- 实现如下
  2. -- 性能短板:1).循环的次数; 2).关联形成的表数据量
  3. with tab_a as
  4.  (select wmsys.wm_concat(tf.la) over(partition by tf.rd order by tf.la) fd,
  5.          tf.rd
  6.     from (select substr(replace(t_all.c, ','), t_all.le, 3) la,
  7.                  t_all.rd
  8.             from (select t1.c, t2.le, t1.rowid rd
  9.                     from tmp t1,
  10.                          (select level le
  11.                             from dual -- 通过 dual 表来实现类似循环操作
  12.                           connect by level < (select max(lengthb(c) -
  13.                                                          lengthb(translate(c,
  14.                                                                            '1234567890',
  15.                                                                            ' ')))
  16.                                                 from tmp)) t2) t_all
  17.            where length(substr(replace(t_all.c, ','), t_all.le, 3)) = 3) tf)
  18. select max(fd) from tab_a group by tab_a.rd;




点击(此处) -- 减少循环次数

  1. with tab_a as
  2.  (select wmsys.wm_concat(tf.la) over(partition by tf.rd order by tf.la) fd,
  3.          tf.rd
  4.     from (select t1.c,
  5.                  t2.le,
  6.                  t1.rowid rd,
  7.                  substr(replace(t1.c, ','), t2.le, 3) la
  8.             from tmp t1,
  9.                  (select level le, t2.ro
  10.                     from dual t1,
  11.                          (select lengthb(c) -
  12.                                  lengthb(translate(c, '1234567890', ' ')) len,
  13.                                  tmp.rowid ro
  14.                             from tmp) t2
  15.                   connect by level < t2.len
  16.                    group by ro, level) t2
  17.            where length(substr(replace(t1.c, ','), t2.le, 3)) = 3 and t1.rowid = t2.ro ) tf)
  18. select max(fd), tab_a.rd from tab_a group by tab_a.rd;





来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23099668/viewspace-1108334/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2009-12-30

  • 博文量
    1
  • 访问量
    4647