sql - 如何查询给定数字中所有数字的总和？

Question

我可以查询给定数值中所有数字的总和吗？

输入值示例:

  EMPNO 
  -----  
  7369

我想得到这个结果:

 7+3+6+9 = 25

我想过这个解决方案，但我认为它效率不高:

SELECT SUBSTR('12345',1,1) + SUBSTR('12345',2,1)
FROM DUAL;

Answer 1

这是解决方案:

1. 简单查询

SELECT id,sum(SUBSTR(empno, LEVEL, 1 )) sums
FROM   (select 1 as id, 7369 as empno from DUAL)
      CONNECT BY LEVEL <= LENGTH(empno)
group by id

2. 使用交叉连接

SELECT sum(column_value)
FROM   (
  SELECT 7369 val,
         ROWNUM AS id
  FROM dual
) t
CROSS JOIN
TABLE(
  CAST(
    MULTISET(
      SELECT SUBSTR( t.val, LEVEL, 1 )
      FROM   DUAL
      CONNECT BY LEVEL <= LENGTH( t.val )
    )
    AS SYS.ODCIVARCHAR2LIST
  )
) c

输出:

SUM(COLUMN_VALUE)
25