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

2022-01-09 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 |

相关文章