选择每个月，即使该月在mysql表中不存在

一尘不染

选择每个月，即使该月在mysql表中不存在

mysql

可以说我在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

然后，我有此查询以获取每个月的员工分析：

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中不存在，这不是我想要的。我仍然想得到这样的结果：

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

有人对此吗？请帮助我。谢谢！

阅读 334

2020-05-17

共1个答案

一尘不染

考虑以下模式，其中第三个表是提到的年/月帮助程序表。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 |
+----------+---------------+----------+-----------------+

外卖

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

2020-05-17