10-146 各桁の数字の合計を求める

SQLパズル

WorkTable
EMPNO  SAL
-----  ----
 7369   800
 7499  1600
 7521  1250
 7566  2975
 7654  1250
 7698  2850
 7782  2450
 7839  5000
 7844  1500
 7900   950
 7902  3000
 7934  1300

EMPNOとSALの各桁の数字の合計を求める

出力結果
EMPNO  SUM_ENO   SAL  SUM_SAL
-----  -------  ----  -------
 7369       25   800        8
 7499       29  1600        7
 7521       15  1250        8
 7566       24  2975       23
 7654       22  1250        8
 7698       30  2850       15
 7782       24  2450       11
 7839       27  5000        5
 7844       23  1500        6
 7900       16   950       14
 7902       18  3000        3
 7934       23  1300        4

データ作成スクリプト

create table WorkTable as
select 7369 as EMPNO,800 as SAL from dual
union select 7499,1600 from dual
union select 7521,1250 from dual
union select 7566,2975 from dual
union select 7654,1250 from dual
union select 7698,2850 from dual
union select 7782,2450 from dual
union select 7839,5000 from dual
union select 7844,1500 from dual
union select 7900, 950 from dual
union select 7902,3000 from dual
union select 7934,1300 from dual;

SQL

--■■■Eval関数を使う方法(10g以降)■■■
create or replace function eval(in_str varchar2) return number is
willReturn number;
begin
    execute immediate 'select ' || in_str || ' from dual' into willReturn;
    return willReturn;
end;
/

select EMPNO,eval(RegExp_Replace(to_char(EMPNO),'([0-9])','+\1')) as SUM_ENO,
SAL,eval(RegExp_Replace(to_char(SAL),'([0-9])','+\1')) as SUM_SAL
  from WorkTable;

--■■■mod関数を使う方法■■■
select EMPNO,
(mod(EMPNO,10000)-mod(EMPNO,1000))/1000+
(mod(EMPNO,1000) -mod(EMPNO,100)) /100+
(mod(EMPNO,100)  -mod(EMPNO,10))  /10+
 mod(EMPNO,10) as SUM_ENO,
SAL,
(mod(SAL,10000)-mod(SAL,1000))/1000+
(mod(SAL,1000) -mod(SAL,100)) /100+
(mod(SAL,100)  -mod(SAL,10))  /10+
 mod(SAL,10) as SUM_SAL
from WorkTable;

--■■■文字列関数を使う方法■■■
select EMPNO,
nvl(Length(
Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(
to_char(EMPNO),
'1','*'),
'2','**'),
'3','***'),
'4','****'),
'5','*****'),
'6','******'),
'7','*******'),
'8','********'),
'9','*********'),
'0')),0) as SUM_ENO,
SAL,
nvl(Length(
Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(
to_char(SAL),
'1','*'),
'2','**'),
'3','***'),
'4','****'),
'5','*****'),
'6','******'),
'7','*******'),
'8','********'),
'9','*********'),
'0')),0) as SUM_SAL
from WorkTable;

--■■■model句を使う方法(10g以降)■■■
select EMPNO,SUM_ENO,SAL,SUM_SAL
  from WorkTable
 model
 dimension by (RowNum as soeji)
 measures(EMPNO,EMPNO as wk_EMPNO,0 as SUM_ENO,SAL,SAL as wk_SAL,0 as SUM_SAL)
 rules ITERATE(20)(
 SUM_ENO[any]  = SUM_ENO[cv()] + mod(wk_EMPNO[cv()],10),
 wk_EMPNO[any] = trunc(wk_EMPNO[cv()]/10),
 SUM_SAL[any]  = SUM_SAL[cv()] + mod(wk_SAL[cv()],10),
 wk_SAL[any] = trunc(wk_SAL[cv()]/10));

解説

mod関数を使う方法が、
一番分かりやすいと思います。

ちなみに、
各桁の和を9で割った余りなら、
九去法でよく使う方法と同じとなります。

---

九去法による"禁断の計算方法"とは
豆知識「九去法」

---