高级SQL -基于表中的值，我要进行计算

Question

下面是一种情况，例如，看一下表格中的以下内容

create table cast_ex(nearly_number varchar2(20));

insert into cast_ex values('10');
insert into cast_ex values('20');
insert into cast_ex values('thirty');
insert into cast_ex values('FORTY');
insert into cast_ex values('Fifty[50])');
insert into cast_ex values('60');

commit;

我想对列nearly_number完全为整数的所有值求和。在这种情况下，我想求和10，20，60。不考虑其他行，因为它们不只包含整数值(即，它们包含0到9之间的字符)

所需输出：

   SUM_INT
----------
        90

Answer 1

使用REGEXP_LIKE()检查NEARLY_NUMBER是否从头到尾只包含数字，使用[:digit:] POSIX类过滤CAST_EX中的行，然后使用TO_NUMBER()和SUM()。

SELECT SUM(TO_NUMBER(NEARLY_NUMBER))
       FROM CAST_EX
       WHERE REGEXP_LIKE(NEARLY_NUMBER, '^[[:digit:]]+$');

Answer 2

您只需要获取那些满足integer子句的行

1)翻译和替换将删除除整数以外的所有内容

2)如果剩余部分为空，则原始字符串必须为整数

3)将其转换为数字并获得SUM。

SELECT SUM(TO_NUMBER(nearly_number)) AS SUM_INT
  FROM cast_ex
 WHERE (CASE
         WHEN REPLACE(TRANSLATE(nearly_number, '0123456789', '##########'),
                      '#',
                      '') IS NULL THEN
          'TRUE'
         ELSE
          'FALSE'
       END) = 'TRUE'

Answer 3

(需要Oracle 12.2或更高版本：)

select sum(to_number(nearly_number default 0 on conversion error)) as sum_int
from   cast_ex
where  mod(to_number(nearly_number default 0 on conversion error),1) = 0;

或者最小化显式转换的数量。

select sum(int_value) as sum_int
from   ( select to_number(nearly_number default 0 on conversion error) as int_value
         from   cast_ex )
where  mod(int_value,1) = 0;