MySQL查询返回数字'零'，如果没有结果 [英] MySQL query to return number 'zero' if no results

问题描述

在选择一个日期和时间并不在我的表存在，目前将返回一个空的结果集.我该如何为那些空结果集返回数字零?:

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 = UNIQUE(日期)
• 存储
• 5STAR
• 4STAR
• 3STAR
• 2STAR
• 1STAR
• 总计
• FROM =唯一

• ID = Primary
• DATE = UNIQUE (date)
• STORE
• 5STAR
• 4STAR
• 3STAR
• 2STAR
• 1STAR
• TOTAL
• FROM = UNIQUE

推荐答案

我认为在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

这篇关于MySQL查询返回数字'零'，如果没有结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！