php - 如果使用 group by 进行 mysql SUM 查询时没有记录，则将其设为 0 而不是 null

Question

这个问题已经有答案了:

MySQL how to fill missing dates in range? (7 个回答)

已关闭 4 年前。

我编写了一个查询来获取结果，例如按日期计算每个产品组的金额总和。
获取输出时，某些产品没有相应的日期金额。该日期未在输出中列出，因此我需要显示每个日期，如果有金额，则需要显示金额，否则为 0。

我尝试使用 IFNULL 和 COALESCE 但得到相同的输出。

My OUTPUT 

 date         amount
 2019-05-16   499
 2019-05-17   1998
 2019-05-18   999
 2019-05-19   999

Needed output should look like this

 date         amount
 2019-05-16   499
 2019-05-17   1998
 2019-05-18   999
 2019-05-19   999
 2019-05-20   0

从mysql获取数据的查询

$query = "select date, SUM(amount) as amount from product_details where date between '2019-05-16 00:00:00' and '2019-05-20 23:59:59' and prodid = 1 group by date";

有没有办法让日期显示没有金额？

Answer 1

试试这个。首先，我们生成一个记录集，其中包含 curdate() 和一月份之间的所有日期。将 curdate() 更改为结束日期，将 1 月 1 日更改为开始日期。然后我们将其与您的表外部连接并使用它按日期分组。

  With cal as  (SELECT a.Days 
    FROM (
        SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS Days
        FROM       (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
    ) a
    WHERE a.Days >= '2019-01-01' and a.Days<=curdate()) 
    SELECT cal.Days as date, sum(p.amount)  as amount FROM cal LEFT JOIN product_details p  on cal.Days=p.date and p.prodid=1 group by cal.Days