通过一条SQL将 (1,2,3,4) 显示 (123,234) 的形式
题面是这样的，一个表有某一列 如： 1,2,3,4,5 ......要求查询出来的每连续三个数字用逗号分隔，显示 如：123,234,345 ；该列长度不定。
创建测试表
-
-- 1.创建一测试表
-
create table tmp(c varchar2(2000));
-
-
-- 2.插入一些测试数据
-
truncate table tmp;
-
insert into tmp values('1,2,3,4,5,6');
-
insert into tmp values('1,2,3');
-
insert into tmp values('1,2,3,4,5,6,7,8,9');
- commit;
具体实现如下 TAG~
-
-- 实现如下
-
-- 性能短板：1）.循环的次数； 2).关联形成的表数据量
-
with tab_a as
-
(select wmsys.wm_concat(tf.la) over(partition by tf.rd order by tf.la) fd,
-
tf.rd
-
from (select substr(replace(t_all.c, ','), t_all.le, 3) la,
-
t_all.rd
-
from (select t1.c, t2.le, t1.rowid rd
-
from tmp t1,
-
(select level le
-
from dual -- 通过 dual 表来实现类似循环操作
-
connect by level < (select max(lengthb(c) -
-
lengthb(translate(c,
-
'1234567890',
-
' ')))
-
from tmp)) t2) t_all
-
where length(substr(replace(t_all.c, ','), t_all.le, 3)) = 3) tf)
- select max(fd) from tab_a group by tab_a.rd;
点击(此处) -- 减少循环次数
-
with tab_a as
-
(select wmsys.wm_concat(tf.la) over(partition by tf.rd order by tf.la) fd,
-
tf.rd
-
from (select t1.c,
-
t2.le,
-
t1.rowid rd,
-
substr(replace(t1.c, ','), t2.le, 3) la
-
from tmp t1,
-
(select level le, t2.ro
-
from dual t1,
-
(select lengthb(c) -
-
lengthb(translate(c, '1234567890', ' ')) len,
-
tmp.rowid ro
-
from tmp) t2
-
connect by level < t2.len
-
group by ro, level) t2
-
where length(substr(replace(t1.c, ','), t2.le, 3)) = 3 and t1.rowid = t2.ro ) tf)
- select max(fd), tab_a.rd from tab_a group by tab_a.rd;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/23099668/viewspace-1108334/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
没有了~
下一篇：
没有了~
请登录后发表评论
登录
全部评论
