即使没有记录，也要在每周/每月查询一次SQL [英] SQL request for query in every week/month even if no record exist

问题描述

我正在使用MySQL，我很难获取每月的计数，包括没有记录的月份.

I am using MySQL and I have a hard time to get the count per month including months with no records.

这是我的桌子"Plays":

This is my table "Plays":

idplay    idplayer    day
------    --------    ---
1             26        17-11-2015
2             37        22-12-2015
3             65        24-04-2016
4             12        16-05-2016
5             37        17-05-2016
6             37        30-05-2016
7             14        30-06-2016

我想获得玩家"37"在最近6个月中每个月的演奏次数，而当他没有参加比赛时，该数字为0.

I want to get the number of times the player "37" has played in every month the last 6 month and get 0 when he has not played.

像这样的东西

Month          Playtimes
----           -------
11-2015             0
12-2015             1
01-2016             0
02-2016             0
03-2016             0
04-2016             0
05-2016             5

推荐答案

为使此方法有效，如注释中所建议，我创建了一个新的关系，该关系简单地列出了月份(我使用了过去的值年).

For this to work, as suggested in the comments, I've created a new relation that simple lists the months (I've used the values over the past year).

然后使用左外连接将这些匹配到Plays表中的月份，仅对应于与指定播放器匹配的播放.

It then uses a left outer join to match these to the months in the Plays table, corresponding only to the plays matching your designated player.

然后使用查询对这些行进行计数，并将空值归为零.

A query is then used to count up these rows, with nulls attributed to zero.

create语句在这里；

The create statement is here;

CREATE TABLE Months (day DATE);
INSERT INTO Months VALUES
('2015.11.1'),('2015.12.1'),('2016.1.1'),
('2016.2.1'), ('2016.2.1'), ('2016.3.1'),
('2016.4.1'), ('2016.5.1'), ('2016.6.1'),
('2016.7.1'), ('2016.8.1'), ('2016.9.1'),
('2016.10.1'), ('2016.11.1');

下面的查询显示了玩家37在过去6个月每月的游戏次数

And the following query to show the games per month, in the last 6 months, by player 37 is here

SELECT DATE_FORMAT(M.day,'%Y-%m') AS Month, COUNT(idPlayer) AS Playtimes
FROM Months M
LEFT JOIN (SELECT * 
           FROM Plays
           WHERE Plays.idPlayer = 37) P
ON MONTH(M.day) = MONTH(P.day) 
AND YEAR(M.day) = YEAR(P.day)
WHERE M.day BETWEEN (NOW() - INTERVAL 6 Month) 
    AND NOW() 
GROUP BY M.day;

我已经将其创建为 SQL提琴，在这里它也可以正常工作产生以下结果:

I've created it into an SQL fiddle as well here which works and produces the following result:

这篇关于即使没有记录，也要在每周/每月查询一次SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！