选择每个月，即使该月在mysql表中不存在 [英] Select each month even though the month not exist in mysql table

问题描述

假设我在mysql中有这两个表.

Let say I have these two tables in mysql.

表1:

date         staff_no
2016-06-10   1
2016-06-09   1
2016-05-09   1
2016-04-09   1

表2:

staff_no    name
1           David

然后，我有这个查询来获取每个月的员工分析:

Then, I have this query to get analysis for the staff for each month:

SELECT DATE_FORMAT(table1.date,'%b %Y') as month,COUNT(table1.date) as total_records,table2.name as name
FROM table1 as table1 
LEFT JOIN table2 as table2 on table2.staff_no = table1.staff_no
WHERE table1.staff_no = "1" and date(table1.date) between = "2016-04-01" and "2016-06-30" 
GROUP BY table2.name,DATE_FORMAT(table1.date,'%Y-%m')
ORDER BY DATE_FORMAT(table1.date,'%Y-%m-%d')

此查询将输出:

month      total_records  name
Apr 2016               1  David
May 2016               1  David
Jun 2016               2  David

但是，如果我从查询中替换"2016-04-01"和"2016-07-31"之间的日期，则不会向我显示7月记录，因为它在表1中不存在，这不是我想要的.我仍然想得到这样的结果:

But, if I replace the date between "2016-04-01" and "2016-07-31" from the query,it wont show me the July record because it is not exist in table1 which is not what I want. I still want to get result like this:

month      total_records  name
Apr 2016               1  David
May 2016               1  David
Jun 2016               2  David
Jul 2016               0  David   

有人对此吗?请帮助我.谢谢！

Anyone expert on this? Kindly help me into this. Thanks!

推荐答案

请考虑以下架构，其中第3个表是提到的年/月Helper表. Helper表非常常见，可以自然地在整个代码中重复使用.我将把它留给您以加载大量的日期数据.但是请注意，对于那些想要减少工作量的人来说，每个月的结束日期是如何组合在一起的，同时允许数据库引擎为我们找出leap年.

Consider the following schema with the 3rd table being the year/month Helper Table mentioned. Helper tables are very common and can be re-used throughout your code naturally. I will leave it to you to load it up with substantial date data. Note however the way the end date for each month was put together for those of us that want to do less work, while allowing the db engine to figure out leap years for us.

该帮助器表中可能只有一列.但这将需要在某些函数中使用函数调用作为结束日期，这意味着更加缓慢.我们喜欢快速.

You could have just one column in that helper table. But that would require the use of function calls for end dates in some of your functions and that means more slowness. We like fast.

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

所以这是一个助手表.设置一次，然后忘记它

So that is a helper table. Set it up once and forget about it for a few years

注意:请不要忘记运行上述更新stmt

Note: Don't forget to run the above update 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).让我们在这里暂停.毕竟，这是您的问题要点:缺少数据.最后，在交叉联接中的工作表.

It works fine. The first mysql table is the Helper table. A left join to bring in the worker records (allowing for null). Let's pause here. That was afterall the point of your question: missing data. Finally the worker table in a cross join.

cross join用于初始化作为工作人员名称的变量(@soloName).通过返回0的ifnull()函数可以很好地找到您所要求的缺失日期的空状态，而对于工人的名字来说，我们却没有那么奢侈.这会强制cross join.

The cross join is to initialize a variable (@soloName) that is the worker's name. Whereas the null status of missing dates as you requested is picked up fine via the ifnull() function returning 0, we don't have that luxury for a worker's name. That forces the cross join.

交叉连接是笛卡尔积.但是由于它是单行，所以我们不会遇到笛卡尔会遇到的正常问题，导致在结果集中出现很多行.无论如何，它都可以.

A cross join is a cartesian product. But since it is a single row, we don't suffer from the normal problems one gets with cartesians causing way to many rows in the result set. Anyway, it works.

但这是一个问题:可以看出，很难在6个地方维护和插入值.因此，请在下面考虑一个存储的proc.

But here is one problem: it is too hard to maintain and plug in values in 6 places as can be seen. So consider below a stored proc for it.

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中，您可以命名).选择权是您的，存储的过程是否存在.

Ah, much easier to work with (in PHP, c#, Java, you name it). Choice is yours, stored proc or not.

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 |
+----------+---------------+----------+-----------------+

外卖店

Helper Table已经使用了数十年.不要害怕或尴尬地使用它们.实际上，有时要在没有它们的情况下完成某些专业工作几乎是不可能的.

The Takeaway

Helper Tables have been used for decades. Don't be afraid or embarrassed to use them. In fact, trying to get some specialty work done without them is nearly impossible at times.

这篇关于选择每个月，即使该月在mysql表中不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！