mysql sum 没返回_如果没有找到值,如何获取SUM函数在MySQL中返回’0’？

使用

COALESCE来避免这个结果。

SELECT COALESCE(SUM(column),0)

FROM table

WHERE ...

更多信息：

给定三个表(一个具有所有数字，一个具有所有空值，一个具有混合)：

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 |