使用
COALESCE以避免这种结果。
SELECt COALESCE(SUM(column),0)FROM tableWHERe ...
要查看实际效果，请参见以下SQL提琴：http
://www.sqlfiddle.com/#!2/d1542/3 /0
更多信息：
给定三张表（一张带有所有数字，一张带有所有空值，一张带有混合数）：
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, 21as expected_sum, COALESCE(SUM(val), 0) as actual_sumFROM fooUNIOn ALLSELECt 'bar' as table_name, 'all non-null' as description, 21as expected_sum, COALESCE(SUM(val), 0) as actual_sumFROM barUNIOn ALLSELECt 'baz' as table_name, 'all null' as description, 0 as expected_sum, COALESCE(SUM(val), 0) as actual_sumFROM 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 |
