我们在日常的工作中,经常遇到了针对某一列的值,进行求和,求平均值,在一些特殊的业务场景下,我们需要对某一列进行求积操作,那我们该如何实现呢,下面先介绍,我
们对字符串的求和操作。
针对字符串的求和操作:
1、有分隔符的字符串:
1 SELECT STR, SUM(STR) OVER(ORDER BY LV ASC) ASRS2 FROM (SELECT REGEXP_SUBSTR('1,2,3,4,5', '[^,]+', 1, LEVEL) AS STR,3 LEVEL ASLV4 FROMDUAL5 CONNECT BY LEVEL <=
6 LENGTH(REGEXP_REPLACE('1,2,3,4,5', '[^,]', '')) + 1) COLS
结果:
2、没有分割符符号的:
1 SELECT STR, SUM(STR) OVER(ORDER BY LV ASC) ASRS2 FROM (SELECT SUBSTR('12345', LEVEL, 1) STR, LEVEL ASLV3 FROMDUAL4 CONNECT BY LEVEL <= LENGTH(12345)) COLS
结果: