MySQL 查询返回数字'zero'如果没有结果

Question

当选择一个DATE并且该日期在我的表中不存在时，它当前将返回一个空的结果集。我如何才能为那些空的结果集返回数字零？：

    SELECT SUM(TOTAL), SUM(5STAR), STORE, DATE
    FROM `table` WHERE DATE >= '2012-02-24' GROUP BY TOTAL
MySQL returned an empty result set (i.e. zero rows)

相反，我想将SUM(TOTAL)和SUM(5STAR)（如果为零行）的结果返回为数字零（0）。

全表结构：

• ID=主要
• DATE=唯一（日期）
• 商店
• 5星
• 4星级
• 3STAR
• 2星
• 1星
• 合计
• FROM=唯一

Answer 1

尝试COALESCE

SELECT COALESCE(SUM(TOTAL),0), COALESCE(SUM(5STAR),0), STORE, DATE
FROM `table` WHERE DATE >= '2012-02-24' GROUP BY TOTAL

Answer 2

TRY

SELECT
       IFNULL(SUM(TOTAL), 0) AS total,
       IFNULL(SUM(5STAR), 0) AS FiveStar, 
       STORE,
       DATE
FROM `table`
WHERE DATE >= '2012-02-24'
GROUP BY TOTAL

参考

Answer 3

我认为在PHP端处理空结果集会更容易（计算返回的行数）。如果要在数据库中处理它，则应该创建一个存储过程。

DELIMITER $$
CREATE PROCEDURE `mc`.`new_routine` (IN DT DATETIME)
BEGIN
IF EXISTS (SELECT 1 FROM `table` WHERE DATE >= @DT)
THEN
    SELECT SUM(TOTAL) AS SumTotal, SUM(5STAR) AS Sum5Star, STORE, `DATE`
    FROM `table`
    WHERE DATE >= @DT
    GROUP BY TOTAL;
ELSE
    SELECT 0 AS SumTotal, 0 AS Sum5Star, NULL AS STORE, NULL AS `DATE`;
END IF;
END