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

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

使用

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 |