mysql - 在 mysql 查询中添加零值缺失行

Question

问题是这样的，我有一个查询返回日期和该特定日期的计数，但我想要的是未出现在该范围内的日期(因为表中不存在)出现但值为零。

我的查询是这样的:

     SELECT fechacierre, COUNT(idcliente) as cantidad
     FROM cliente
     WHERE fechacierre BETWEEN DATE_ADD('2017-02-21', INTERVAL -6 DAY) AND '2017-02-21'
     GROUP BY fechacierre

给出这个结果:

------------------------
fechacierre | cantidad
------------------------
2017-02-15  | 3
------------------------
2017-02-17  | 1
------------------------
2017-02-20  | 3
------------------------
2017-02-21  | 2
------------------------

如何获取值为 0 的 (2017-02-16)、(2017-02-18) 和 (2017-02-19) 的值？

提前致谢!

Answer 1

您可以通过使用包含所有日期的表进行 RIGHT JOIN 来解决此问题，如下所示:

SELECT fechacierre, COUNT(idcliente) as cantidad
     FROM cliente RIGHT JOIN (select * from 
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between DATE_ADD('2017-02-21', INTERVAL -6 DAY) and '2017-02-21') t ON fechacierre = selected_date
     WHERE fechacierre BETWEEN DATE_ADD('2017-02-21', INTERVAL -6 DAY) AND '2017-02-21'
     GROUP BY fechacierre