考虑以下模式，其中第三个表是提到的年/月帮助程序表。Helper表非常常见，可以自然地在整个代码中重复使用。我将把它留给您以加载大量的日期数据。但是请注意，对于那些想要减少工作量的人来说，每个月的结束日期是如何组合在一起的，同时允许数据库引擎为我们找出leap年。
您在该帮助器表中可能只有一列。但这将需要在某些函数中使用函数调用作为结束日期，这意味着更加缓慢。我们喜欢快速。
架构图
create table workerRecords( id int auto_increment primary key, the_date date not null, staff_no int not null);-- truncate workerRecords;insert workerRecords(the_date,staff_no) values('2016-06-10',1),('2016-06-09',1),('2016-05-09',1),('2016-04-09',1),('2016-03-02',2),('2016-07-02',2);create table workers( staff_no int primary key, full_name varchar(100) not null);-- truncate workers;insert workers(staff_no,full_name) values(1,'David Higgins'),(2,"Sally O'Riordan");助手表如下
create table ymHelper( -- Year Month helper table. Used for left joins to pick up all dates. -- PK is programmer's choice. dtBegin date primary key, -- by definition not null dtEnd date null);-- truncate ymHelper;insert ymHelper (dtBegin,dtEnd) values('2015-01-01',null),('2015-02-01',null),('2015-03-01',null),('2015-04-01',null),('2015-05-01',null),('2015-06-01',null),('2015-07-01',null),('2015-08-01',null),('2015-09-01',null),('2015-10-01',null),('2015-11-01',null),('2015-12-01',null),('2016-01-01',null),('2016-02-01',null),('2016-03-01',null),('2016-04-01',null),('2016-05-01',null),('2016-06-01',null),('2016-07-01',null),('2016-08-01',null),('2016-09-01',null),('2016-10-01',null),('2016-11-01',null),('2016-12-01',null),('2017-01-01',null),('2017-02-01',null),('2017-03-01',null),('2017-04-01',null),('2017-05-01',null),('2017-06-01',null),('2017-07-01',null),('2017-08-01',null),('2017-09-01',null),('2017-10-01',null),('2017-11-01',null),('2017-12-01',null),('2018-01-01',null),('2018-02-01',null),('2018-03-01',null),('2018-04-01',null),('2018-05-01',null),('2018-06-01',null),('2018-07-01',null),('2018-08-01',null),('2018-09-01',null),('2018-10-01',null),('2018-11-01',null),('2018-12-01',null),('2019-01-01',null),('2019-02-01',null),('2019-03-01',null),('2019-04-01',null),('2019-05-01',null),('2019-06-01',null),('2019-07-01',null),('2019-08-01',null),('2019-09-01',null),('2019-10-01',null),('2019-11-01',null),('2019-12-01',null);-- will leave as an exercise for you to add more years. Good idea to start, 10 in either direction, at least.update ymHelper set dtEnd=LAST_DAY(dtBegin); -- data patch. Confirmed leap years.alter table ymHelper modify dtEnd date not null; -- there, ugly patch above worked fine. Can forget it ever happened (until you add rows)-- show create table ymHelper; -- this confirms that dtEnd is not null这就是一个辅助表。设置一次，然后忘记它几年
注意 ：不要忘记运行上面的更新stmt
快速测试查询
SELECT DATE_FORMAT(ymH.dtBegin,'%b %Y') as month,ifnull(COUNT(wr.the_date),0) as total_records,@soloname as full_name FROM ymHelper ymH left join workerRecords wr on wr.the_date between ymH.dtBegin and ymH.dtEnd and wr.staff_no = 1 and wr.the_date between '2016-04-01' and '2016-07-31' LEFT JOIN workers w on w.staff_no = wr.staff_no cross join (select @soloName:=full_name from workers where staff_no=1) xDerived WHERe ymH.dtBegin between '2016-04-01' and '2016-07-31' GROUP BY ymH.dtBegin order by ymH.dtBegin;+----------+---------------+---------------+| month | total_records | full_name |+----------+---------------+---------------+| Apr 2016 | 1 | David Higgins || May 2016 | 1 | David Higgins || Jun 2016 | 2 | David Higgins || Jul 2016 | 0 | David Higgins |+----------+---------------+---------------+
工作正常。第一个mysql表是Helper表。左联接以引入工作程序记录（允许为null）。让我们在这里暂停。毕竟，这就是您所要提出的问题： 缺少数据
。最后，在交叉联接中的工作表。
该
cross join是初始化变量（
@soloName），这是工作人员的姓名。通过
ifnull()返回0
的函数可以很好地找到您所要求的缺失日期的空状态，而对于工人的名字，我们却没有那么奢侈。这迫使
cross join。
交叉连接是笛卡尔积。但是由于它是单行，所以我们不会遇到笛卡尔会遇到的正常问题，导致结果集中出现很多行。无论如何，它是可行的。
但这是一个问题：可以看出，很难在6个地方维护和插入值。因此，请考虑下面的存储过程。
存储过程
drop procedure if exists getOneWorkersRecCount;DELIMITER $$create procedure getoneWorkersRecCount(pStaffNo int, pBeginDt date, pEndDt date)BEGIN SELECT DATE_FORMAT(ymH.dtBegin,'%b %Y') as month,ifnull(COUNT(wr.the_date),0) as total_records,@soloname as full_name FROM ymHelper ymH left join workerRecords wr on wr.the_date between ymH.dtBegin and ymH.dtEnd and wr.staff_no = pStaffNo and wr.the_date between pBeginDt and pEndDt LEFT JOIN workers w on w.staff_no = wr.staff_no cross join (select @soloName:=full_name from workers where staff_no=pStaffNo) xDerived WHERe ymH.dtBegin between pBeginDt and pEndDt GROUP BY ymH.dtBegin order by ymH.dtBegin;END$$DELIMITER ;
多次测试存储的过程
call getoneWorkersRecCount(1,'2016-04-01','2016-06-09');call getoneWorkersRecCount(1,'2016-04-01','2016-06-10');call getoneWorkersRecCount(1,'2016-04-01','2016-07-01');call getoneWorkersRecCount(2,'2016-02-01','2016-11-01');
嗯，使用起来要容易得多（在PHP，C＃，Java中，您可以命名）。选择权属于您，是否存储过程。
奖金存储过程
drop procedure if exists getAllWorkersRecCount;DELIMITER $$create procedure getAllWorkersRecCount(pBeginDt date, pEndDt date)BEGIN SELECT DATE_FORMAT(ymH.dtBegin,'%b %Y') as month,ifnull(COUNT(wr.the_date),0) as total_records,w.staff_no,w.full_name FROM ymHelper ymH cross join workers w left join workerRecords wr on wr.the_date between ymH.dtBegin and ymH.dtEnd and wr.staff_no = w.staff_no and wr.the_date between pBeginDt and pEndDt -- LEFT JOIN workers w on w.staff_no = wr.staff_no -- cross join (select @soloName:=full_name from workers ) xDerived WHERe ymH.dtBegin between pBeginDt and pEndDt GROUP BY ymH.dtBegin,w.staff_no,w.full_name order by ymH.dtBegin,w.staff_no;END$$DELIMITER ;
快速测试
call getAllWorkersRecCount('2016-03-01','2016-08-01');+----------+---------------+----------+-----------------+| month | total_records | staff_no | full_name |+----------+---------------+----------+-----------------+| Mar 2016 | 0 | 1 | David Higgins || Mar 2016 | 1 | 2 | Sally O'Riordan || Apr 2016 | 1 | 1 | David Higgins || Apr 2016 | 0 | 2 | Sally O'Riordan || May 2016 | 1 | 1 | David Higgins || May 2016 | 0 | 2 | Sally O'Riordan || Jun 2016 | 2 | 1 | David Higgins || Jun 2016 | 0 | 2 | Sally O'Riordan || Jul 2016 | 0 | 1 | David Higgins || Jul 2016 | 1 | 2 | Sally O'Riordan || Aug 2016 | 0 | 1 | David Higgins || Aug 2016 | 0 | 2 | Sally O'Riordan |+----------+---------------+----------+-----------------+外卖
辅助表已经使用了数十年。不要害怕或尴尬地使用它们。实际上，有时要在没有他们的情况下完成某些专业工作几乎是不可能的。
