mysql - 用 0 填充缺失值 - sql 查询

Question

我有一个如下所述的 SQL 查询，它选择两个日期之间的数据。

SELECT date, total FROM db WHERE date >= '2016-03-14' AND date <= '2016-03-20';

我想在没有各种日期数据的情况下输出“0”，例如:

查询范围 = 2016-03-14 到 2016-03-20

目前我的 SQL 将输出:

Date         Total
2016-03-14   50

我想输出:

Date        Total
2016-03-14  50
2016-03-15  0
2016-03-16  0
2016-03-17  0
2016-03-18  0
2016-03-19  0
2016-03-20  0

有没有什么方法可以在没有复杂连接的情况下做到这一点？

谢谢

马特

Answer 1

为数据中不存在的日期创建记录的最佳方法是联接到日历表。

SELECT a.cal_dt, COALESCE(b.total,0) AS total
FROM lkp_Calendar a
LEFT JOIN db b
  ON b.date = a.cal_dt 
WHERE a.cal_dt >= '2016-03-14' 
  AND a.cal_dt <= '2016-03-20';

有很多好的脚本可以创建强大的日历表，一个简单的脚本是:

CREATE TABLE lkp_Calendar (cal_dt date);

CREATE PROCEDURE addDates(dateStart DATE, dateEnd DATE)
BEGIN
      WHILE dateStart <= dateEnd DO
        INSERT INTO lkp_Calendar (cal_dt) VALUES (dateStart);
        SET dateStart = date_add(dateStart, INTERVAL 1 DAY);
      END WHILE;
    END;

CALL addDates('2016-01-01','2016-12-31');