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

题面是这样的，一个表有某一列 如： 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;

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;

• 博文量
1
• 访问量
4647