如果找不到值，如何在 MySQL 中获取 SUM 函数以返回“0"?

2021-11-20 00:00:00 function sum null mysql

假设我在 MySQL 中有一个简单的函数:

Say I have a simple function in MySQL:

SELECT SUM(Column_1)
FROM Table
WHERE Column_2 = 'Test'

如果 Column_2 中没有条目包含文本Test"，则此函数返回 NULL，而我希望它返回 0.

If no entries in Column_2 contain the text 'Test' then this function returns NULL, while I would like it to return 0.

我知道这里已经有人多次问过类似的问题，但我无法根据我的目的调整答案，因此如果您能帮助我解决这个问题，我将不胜感激.

I'm aware that a similar question has been asked a few times here, but I haven't been able to adapt the answers to my purposes, so I'd be grateful for some help to get this sorted.

推荐答案

使用 COALESCE 以避免这种结果.

Use COALESCE to avoid that outcome.

SELECT COALESCE(SUM(column),0)
FROM   table
WHERE  ...

要查看它的实际效果，请参阅此 sql fiddle:http://www.sqlfiddle.com/#!2/d1542/3/0

To see it in action, please see this sql fiddle: http://www.sqlfiddle.com/#!2/d1542/3/0

更多信息:

给定三张表(一张全数字，一张全空，一张混合):

Given three tables (one with all numbers, one with all nulls, and one with a mixture):

SQL 小提琴

MySQL 5.5.32 架构设置:

CREATE TABLE foo
(
  id    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  val   INT
);

INSERT INTO foo (val) VALUES
(null),(1),(null),(2),(null),(3),(null),(4),(null),(5),(null),(6),(null);

CREATE TABLE bar
(
  id    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  val   INT
);

INSERT INTO bar (val) VALUES
(1),(2),(3),(4),(5),(6);

CREATE TABLE baz
(
  id    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  val   INT
);

INSERT INTO baz (val) VALUES
(null),(null),(null),(null),(null),(null);

查询 1:

SELECT  'foo'                   as table_name,
        'mixed null/non-null'   as description,
        21                      as expected_sum,
        COALESCE(SUM(val), 0)   as actual_sum
FROM    foo
UNION ALL

SELECT  'bar'                   as table_name,
        'all non-null'          as description,
        21                      as expected_sum,
        COALESCE(SUM(val), 0)   as actual_sum
FROM    bar
UNION ALL

SELECT  'baz'                   as table_name,
        'all null'              as description,
        0                       as expected_sum,
        COALESCE(SUM(val), 0)   as actual_sum
FROM    baz

结果:

| TABLE_NAME |         DESCRIPTION | EXPECTED_SUM | ACTUAL_SUM |
|------------|---------------------|--------------|------------|
|        foo | mixed null/non-null |           21 |         21 |
|        bar |        all non-null |           21 |         21 |
|        baz |            all null |            0 |          0 |

相关文章