mysql - 在mysql查询结果中添加过去一年的所有月份，即使没有行

Question

我正在寻找一种方法来检索过去 12 个月内所有职位空缺的订阅。由于并非每个职位空缺都会在每个月都有订阅，因此某些行必须返回 0，但现在它不在结果集中。如何添加缺失的行？

我使用的表格如下:

CREATE TABLE `calendar_months` (
  `month_id` int(8) DEFAULT NULL,
  `en_abbr` varchar(255) NOT NULL,
  `en_long` varchar(255) NOT NULL,
  `nl_abbr` varchar(255) NOT NULL,
  `nl_long` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `calendar_months` (`month_id`, `en_abbr`, `en_long`, `nl_abbr`, `nl_long`) VALUES
(1, 'jan', 'January', 'jan', 'Januari'),
(2, 'feb', 'February', 'feb', 'Februari'),
(3, 'mar', 'March', 'mrt', 'Maart'),
(4, 'apr', 'April', 'apr', 'April'),
(5, 'may', 'May', 'mei', 'Mei'),
(6, 'jun', 'June', 'jun', 'Juni'),
(7, 'jul', 'July', 'jul', 'Juli'),
(8, 'aug', 'August', 'aug', 'Augustus'),
(9, 'sep', 'September', 'sep', 'September'),
(10, 'oct', 'October', 'okt', 'Oktober'),
(11, 'nov', 'November', 'nov', 'November'),
(12, 'dec', 'December', 'dec', 'December');

CREATE TABLE `vacancies` (
  `vacancy_id` int(11) NOT NULL,
  `org_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL COMMENT 'title',
  `description` varchar(255) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `is_deleted` tinyint(4) NOT NULL DEFAULT '0',
  `status` int(11) NOT NULL DEFAULT '0'COMMENT
) ;

CREATE TABLE `vacancy_subscriptions` (
  `subscription_id` int(11) NOT NULL,
  `vacancy_id` int(11) DEFAULT NULL,
  `user_id` int(10) DEFAULT NULL,
  `subscription_date` datetime NOT NULL,
  `message` text
)

我使用以下查询:

SELECT
  CONCAT(cm.nl_long, ' ', YEAR(v.create_time)) as label, YEAR(v.create_time) as vacyear, MONTH(v.create_time) as vacmonth, COUNT(*) as totalsubscriptions
FROM `calendar_months` as cm
LEFT JOIN `vacancies` as v on cm.month_id = month(v.create_time)
LEFT JOIN `vacancy_subscriptions` as vs on v.vacancy_id = vs.vacancy_id 
WHERE
  (v.create_time >= (DATE_ADD(NOW(),INTERVAL -12 MONTH)))
  AND v.is_deleted = 0
  AND v.org_id = 1
GROUP BY vacyear, vacmonth
ORDER BY vacyear ASC, vacmonth ASC

这给了我我正在寻找的结果。

+---------------+---------+----------+--------------------+
|     label     | vacyear | vacmonth | totalsubscriptions |
+---------------+---------+----------+--------------------+
| Oktober 2017  |    2017 |       10 |                  5 |
| November 2017 |    2017 |       11 |                  1 |
| December 2017 |    2017 |       12 |                 13 |
| Maart 2018    |    2018 |        3 |                  4 |
| April 2018    |    2018 |        4 |                  5 |
+---------------+---------+----------+--------------------+

但是，如何添加过去 12 个月内没有订阅的月份，并且最后一列仅为“0”？像这样:

+----------------+---------+----------+--------------------+
|     label      | vacyear | vacmonth | totalsubscriptions |
+----------------+---------+----------+--------------------+
| Mei 2017       |    2017 |        5 |                  0 |
| Juni 2017      |    2017 |        6 |                  0 |
| Juli 2017      |    2017 |        7 |                  0 |
| Augustus 2017  |    2017 |        8 |                  0 |
| September 2017 |    2017 |        9 |                  0 |
| Oktober 2017   |    2017 |       10 |                  5 |
| November 2017  |    2017 |       11 |                  1 |
| December 2017  |    2017 |       12 |                 13 |
| Januari 2018   |    2018 |        1 |                  0 |
| Februari 2018  |    2018 |        2 |                  0 |
| Maart 2018     |    2018 |        3 |                  4 |
| April 2018     |    2018 |        4 |                  5 |
+----------------+---------+----------+--------------------+

更新:我创建了一个包含一些数据的 SQL fiddle ! http://www.sqlfiddle.com/#!9/75db76

Answer 1

你的逻辑有问题。您的日历表需要包含月份和年份，以及该月的第一个日期(为了方便起见)。

然后你可以这样做:

SELECT CONCAT(cm.nl_long, ' ', cm.year) as label, COUNT(*) as totalsubscriptions
FROM calendar_months cm LEFT JOIN
     vacancies v 
     ON cm.month_id = month(v.create_time) AND
        cm.year = year(v.create_time) AND
        v.is_deleted = 0 AND
        v.org_id = 1 LEFT JOIN
     `vacancy_subscriptions` vs
     on v.vacancy_id = vs.vacancy_id 
WHERE cm.month_start_date >= DATE_ADD(NOW(), INTERVAL -12 MONTH))
GROUP BY label
ORDER BY MIN(cm.month_start_date);

注释:

• 除第一个表之外的所有表的过滤应位于 ON 子句中(对于 LEFT JOIN)。
• GROUP BY 需要位于第一个表中的字段上。
• 如果您有一个真实的日历表(而不仅仅是月份列表)，那么您可以在该表上过滤过去一年的数据。
• 您可能不想过滤 NOW() - 您将获得 13 个月的数据，其中包括第一个月和最后一个月的部分数据。