如果找不到值，如何在 MySQL 中获取 SUM 函数以返回“0”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7602271/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I get SUM function in MySQL to return '0' if no values are found?
提问by Nick
Say I have a simple function in MySQL:
假设我在 MySQL 中有一个简单的函数：
SELECT SUM(Column_1)
FROM Table
WHERE Column_2 = 'Test'
If no entries in Column_2 contain the text 'Test' then this function returns NULL, while I would like it to return 0.
如果Column_2 中没有条目包含文本“Test”，则此函数返回NULL，而我希望它返回 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.
我知道这里已经多次问过类似的问题，但我无法根据我的目的调整答案，因此如果您能帮助我解决这个问题，我将不胜感激。
回答by Brad Christie
Use COALESCEto avoid that outcome.
使用COALESCE以避免这种结果。
SELECT COALESCE(SUM(column),0)
FROM table
WHERE ...
To see it in action, please see this sql fiddle: http://www.sqlfiddle.com/#!2/d1542/3/0
要查看它的实际效果，请参阅此 sql 小提琴：http://www.sqlfiddle.com/#!2/d1542/3 /0
More Information:
更多信息：
Given three tables (one with all numbers, one with all nulls, and one with a mixture):
给定三张表（一张全数字，一张全空，一张混合）：
MySQL 5.5.32 Schema Setup:
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);
Query 1:
查询 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 |
回答by Mark Byers
SELECT IFNULL(SUM(Column1), 0) AS total FROM...
SELECT COALESCE(SUM(Column1), 0) AS total FROM...
The difference between them is that IFNULLis a MySQL extension that takes two arguments, and COALESCEis a standard SQL function that can take one or more arguments. When you only have two arguments using IFNULLis slightly faster, though here the difference is insignificant since it is only called once.
它们之间的区别在于IFNULLMySQL 扩展接受两个参数，并且COALESCE是一个标准的 SQL 函数，可以接受一个或多个参数。当你只有两个参数时 usingIFNULL会稍微快一点，但这里的差异是微不足道的，因为它只被调用一次。
回答by Sashi Kant
Can't get exactly what you are asking but if you are using an aggregate SUM function which implies that you are grouping the table.
无法准确获得您所要求的内容，但是如果您使用的是聚合 SUM 函数，这意味着您正在对表进行分组。
The query goes for MYSQL like this
查询是这样的 MYSQL
Select IFNULL(SUM(COLUMN1),0) as total from mytable group by condition
