如果没有结果，MySQL 查询返回数字“零"

2022-01-09 00:00:00 sum zero mysql resultset

选择日期时，我的表中不存在该日期，它当前将返回一个空结果集.我怎样才能为那些空结果集返回数字零呢?:

When selecting a DATE and that date does not exist in my table it currently will return an empty result set. How can I be able to return the number zero for those empty result sets instead?:

    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) 返回.

I want to instead return the results of the SUM(TOTAL) and SUM(5STAR) (if zero rows) as the number zero (0).

全表结构:

  • ID = 主要
  • DATE = 唯一(日期)
  • 商店
  • 5 星
  • 4 星
  • 3 星
  • 2 星
  • 1 星
  • 总计
  • FROM = 唯一

推荐答案

我认为在PHP端处理空结果集会更容易(计算返回的行数).如果你想在数据库中处理它，你应该创建一个存储过程.

I think it would be easier to handle the empty result set on the PHP side (count the returned rows). If you want to handle it in the database, you should create a stored procedure.

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

相关文章