在零查询的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 
------------------------ 

我如何获得（2017年2月16日）的值，（2017年2月18日）和（2017-02-19）0值？

在此先感谢！

Answer 1

你可以用你需要这样的所有日期生成辅助表：

select @rowNum := @rowNum +1, DATE_ADD('2017-02-21', INTERVAL [email protected] DAY) as fechacierre from Any_table_with_some_records, (select @rowNum := 0) t 

然后你可以用鼠标右键加入

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

这是一个想法，我没有测试它。

Answer 2

你可以解决它做的权利与具有这样的日子一表连接：

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