oracle sum null值相关 null 和 空字符串('')

组函数不计算null值
 


SELECT SUM(a) FROM (
SELECT 1 a FROM dual
UNION
SELECT 2 a FROM dual
UNION
SELECT 3 a FROM dual
);
结果是6
 
　　
 
 

SELECT SUM(a) FROM (
SELECT 1 a FROM dual
UNION
SELECT 2 a FROM dual
UNION
SELECT 3 a FROM dual
UNION
SELECT NULL a FROM dual
);
　　结果还是6
　　
 
 
例如：http://blog.itpub.net/23650854/viewspace-684335
select rownum,a from t_null;
    ROWNUM          A
---------- ----------
         1
         2
         3
z@test10gr2> select sum(a) from t_null;
    SUM(A)
----------
    NULL
 

select rownum,a from t_null;
ROWNUM          A
---------- ----------
1
2
3
4         77
select sum(a) from t_null;
SUM(A)
----------
77
 
 
 
 
 
 
 
http://edgenhuang.iyunv.com/blog/975567
 
-- oracle 将 空字符串即''当成null，测试脚本如下：
 
select nvl(null,-1) from dual;
select nvl('','-1') from dual;
select nvl(nvl(null,''),-1) from dual;
select nvl(trim('  '),'-1') from dual;
 
-- 但是要记住，null 与任何值做逻辑运算得结果都为 false，包括和null本身：
 
select nvl(max('1'),-1) from dual where null = '';
select nvl(max('1'),-1) from dual where null <> '';
select nvl(max('1'),-1) from dual where null = '-1';
select nvl(max('1'),-1) from dual where null <> '-1';
select nvl(max('1'),-1) from dual where null = null;
select nvl(max('1'),-1) from dual where null <> null;
 
-- 不过，用 is null 判断时，空字符串和 null 都 is null：
 
select nvl(max('1'),-1) from dual where '' is null;
select nvl(max('1'),-1) from dual where null is null;
 
--------------------------------------------------------------------------------------------------
 
-- 还要记住，null 和任何数值进行数学运算，结果都为 null：
 
select nvl(null + 0,-1) from dual;
 
-- 不过，可以使用 || 将空字符串或 null 和字符串连接
 
select 'a' || null || 'b' from dual; 
 
--------------------------------------------------------------------------------------------------
 
-- 另，作为以下的 Oracle 内置的函数的参数时，结果也为 null：
select nvl(length(null),-1) from dual;
select nvl(trim(null),-1) from dual;
select nvl(ltrim(null),'-1') from dual;
select nvl(rtrim(null),'-1') from dual;
select nvl(rtrim(null,' '),'-1') from dual;
select nvl(soundex(null),'-1') from dual;
select nvl(SubStr(null,1),-1) from dual;
select nvl(InStr(null,1),-1) from dual;
select nvl(replace(null,'a','b'),'-1') from dual;
 
select nvl(min(null),'-1') from dual;
select nvl(max(null),'-1') from dual;
select nvl(sum(null),'-1') from dual;
select nvl(avg(null),'-1') from dual;
select nvl(sum(null),'-1') from dual;
 
 
-- 不过，作为以下的 Oracle 内置的函数的参数时，结果不为null：
select concat('a', null) from dual;
select concat(null, 'a') from dual;

select count(null) from dual;
 
-- 其他函数用的时候，也可以使用以上的方法测试