【Oracle】NVL(SUM(a), b) 与 SUM(NVL(a, b)) 的区别

NVL(SUM(a), b) 与 SUM(NVL(a, b)) 的区别

构造一张测试表

SQL> WITH TEMP AS
  2   (SELECT 1 RN, 'SZ' PLANT, '20190321' DATESTR, 459 TOTAL
  3      FROM DUAL
  4    UNION ALL
  5    SELECT 2, 'CD', '20190321', 100
  6      FROM DUAL
  7    UNION ALL
  8    SELECT 3, 'SZ', '20190321', NULL
  9      FROM DUAL)
 10  SELECT T.* FROM TEMP T;
         RN PLANT DATESTR       TOTAL
---------- ----- -------- ----------
         1 SZ    20190321        459
         2 CD    20190321        100
         3 SZ    20190321 

1、NULL值和数字同时存在的情况

• NVL(SUM(a), b)

SELECT NVL(SUM(T.TOTAL), 0) FROM TEMP T;
NVL(SUM(T.TOTAL),0)
-------------------
                559

• SUM(NVL(a, b))

SELECT SUM(NVL(T.TOTAL, 0)) FROM TEMP T;
SUM(NVL(T.TOTAL,0))
-------------------
                559

2、全为NULL值

• SUM(a)

SELECT SUM(T.TOTAL) FROM TEMP T WHERE T.RN = 3;
SUM(T.TOTAL)
------------

• NVL(SUM(a), b)

SELECT NVL(SUM(T.TOTAL), 0) FROM TEMP T WHERE T.RN = 3;
NVL(SUM(T.TOTAL),0)
------------------
                  0

• SUM(NVL(a, b))

SELECT SUM(NVL(T.TOTAL, 0)) FROM TEMP T WHERE T.RN = 3;
SUM(NVL(T.TOTAL,0))
--------------------
                  0

3、当记录不存在时

• SUM(a)

SELECT SUM(T.TOTAL) FROM TEMP T WHERE T.RN = 4;
SUM(T.TOTAL)
------------

• NVL(SUM(a), b)

SELECT NVL(SUM(T.TOTAL), 0) FROM TEMP T WHERE T.RN = 4;
NVL(SUM(T.TOTAL),0)
-------------------
                  0

• SUM(NVL(a, b))

SELECT SUM(NVL(T.TOTAL, 0)) FROM TEMP T WHERE T.RN = 4;
SUM(NVL(T.TOTAL,0))
-------------------

总结

• SUM 求和时自动跳过所有 NULL 值（NULL 值没有-参加运算， 而不是被当做 0 ）；

• SUM(NVL(a, b)) 求和时，列中的 NULL 值被当做 b 来运算（上例中当做 0 运算）；

  • 记录不存在时。即 a 不存在时，整个 SUM(NVL(a, b)) 为 NULL， 而不是为 b；

• NVL(SUM(a), b)，先进行求和运算，如果为 NULL 值则令其为 b；

  • 记录不存在时。即 a 不存在时，SUM(a) 为 NULL， 但 NVL(SUM(a), b) 的结果为 b；

---

• NULL 值与任何数进行基本运算的结果都为 NULL；
• 基本运算会自动舍弃 NULL 值（这一点可以通过 AVG 函数来进一步验证：有 NULL 值存在时， AVG(a) 与 AVG(NVL(a, 0)) 的结果是不一样的）

---

• 一般情况下，建议使用 NVL(SUM(a), b) 的这种写法。
• 如果有特别严格的需求，甚至可以使用 NVL(SUM(NVL(a, b)), b) 这种写法来双重保险。