假设我在 MySQL 中有一个简单的函数:
SELECT SUM(Column_1) FROM Table WHERE Column_2 = 'Test'
如果 2 中没有条目Column_包含文本“测试”,则此函数返回NULL,而我希望它返回 0。
Column_
NULL
我知道类似的问题已经在这里被问过几次,但我无法根据我的目的调整答案,所以我很感激能得到一些帮助来解决这个问题。
用来COALESCE避免这种结果。
COALESCE
SELECT COALESCE(SUM(column),0) FROM table WHERE ...
要查看它的实际效果,请查看此 sql fiddle: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, 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 |