mysql - 如果日期记录在两个日期范围之间不匹配，则打印零

Question

我有数据的开始日期和结束日期，例如start_date 为 2016-07-10，end_date 为 2016-07-25。我希望通过添加两个字段以及零值来获取这两个日期之间的所有记录，但我没有得到表中不存在的日期记录。

SQL fiddle :http://sqlfiddle.com/#!9/c8dab/9

DDL:

CREATE TABLE `bugs` (`bug_id` int(11) NOT NULL, `bug_date` date NOT NULL, `cf1` int(11) NOT NULL, `cf2` int(11) NOT NULL, `bug_status` varchar(200) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `bugs` (`bug_id`, `bug_date`, `cf1`, `cf2`, `bug_status`) VALUES (101, '2016-07-19', 3, 2, 'RESOLVED'), (102, '2016-07-19', 2, 1, 'VERIFIED'), (103, '2016-07-19', 2, 1, 'VERIFIED'), (103, '2016-07-19', 2, 1, 'VERIFIED'), (1363, '2016-07-19', 2, 1, 'VERIFIED'), (1352, '2016-07-19', 2, 1, 'VERIFIED'), (102, '2016-07-19', 2, 1, 'VERIFIED'), (102, '2016-07-22', 2, 2, 'CLOSED'), (103, '2016-07-22', 2, 2, 'CLOSED'), (103, '2016-07-22', 2, 2, 'CLOSED'), (102, '2016-07-19', 3, 2, 'NEW'), (102, '2016-07-19', 2, 1, 'REOPENED'), (102, '2016-07-19', 2, 1, 'CLOSED'), (102, '2016-07-19', 2, 1, 'VERIFIED'), (1363, '2016-07-19', 2, 1, 'VERIFIED'), (1352, '2016-07-19', 2, 1, 'VERIFIED'), (565, '2016-07-19', 2, 1, 'VERIFIED'), (398, '2016-07-22', 2, 2, 'CLOSED'), (565, '2016-07-22', 2, 2, 'CLOSED'), (9872, '2016-07-22', 2, 2, 'CLOSED');

查询:

SELECT DATE_FORMAT(convert_tz(bugs.bug_date,@@session.time_zone,'+05:30'), '%Y-%m-%d') as date, SUM(bugs.cf1 + bugs.cf2) as count FROM bugs 
WHERE (bugs.bug_date BETWEEN '2016-07-10' AND '2016-07-25') 

GROUP BY bugs.bug_date;

实际输出:

date       | count
2016-07-19 | 46
2016-07-22 | 24

预期输出:

date       | count
2016-07-10 | 0
2016-07-11 | 0
2016-07-12 | 0
2016-07-13 | 0
2016-07-14 | 0
2016-07-15 | 0
2016-07-16 | 0
2016-07-17 | 0
2016-07-18 | 0
2016-07-19 | 46
2016-07-20 | 0
2016-07-21 | 0
2016-07-22 | 24
2016-07-23 | 0
2016-07-24 | 0
2016-07-25 | 0

我已经浏览了几篇相关的文章，但我没有通过尝试类似的解决方案得到答案。

我无法像某些帖子中建议的那样创建新表，数据库访问对我来说是只读的。

Answer 1

在获得查询输出后，我通过务实的方式(中间层)实现了这一点 -> 将日期明智的查询数据附加到数组。

for ($i=0; $i <= $noOfDays; $i++) {
  $date = date('Y-m-d', strtotime($startDate . ' +' . $i . ' day'));
  //this will add one day in startdate and add logic for $somevalue accordingly
  $rows[] = array($date, $somevalue);
}