mysql 选择 3 年的月份，包括那些没有值的月份

Question

我有 2 个表:月份和启动器

mysql > select * from months
---------------------
| id   | month      |
| ---- | ---------- |
| 1    | 2012-01-31 |
| 2    | 2012-02-29 |
| 3    | 2012-03-31 |
| 4    | 2012-04-30 |
| 5    | 2012-05-31 |
| 6    | 2012-06-30 |
| 7    | 2012-07-31 |
| 8    | 2012-08-31 |
| 9    | 2012-09-30 |
| 10   | 2012-10-31 |
| 11   | 2012-11-30 |
| 12   | 2012-12-31 |
---------------------

从表starter中查询返回

+----------+------------+
|       id | startdate  |
+----------+------------+
|        1 | 2017-07-20 |
|        2 | 2017-07-19 |
|        4 | 2017-07-01 |
|        5 | 2017-06-28 |
|        6 | 2017-05-04 |
|        7 | 2017-04-03 |
|        8 | 2017-01-09 |
|        9 | 2017-02-01 |
|       10 | 2016-01-01 |
|       11 | 2015-01-07 |
|       12 | 2015-01-19 |
|       13 | 2016-02-09 |
+----------+------------+

我修改了https://stackoverflow.com/a/17916395/8104587对此:

SELECT concat(date_format(m.month,'%b'),' ',year(CURRENT_DATE())) as Months, 
COUNT(s.id) AS Total FROM months AS m LEFT JOIN starter s ON 
MONTH(m.month) = MONTH(s.startdate) AND YEAR(s.startdate) = 
YEAR(CURRENT_DATE()) GROUP BY m.month

输出:

+----------+-------+
| Months   | Total |
+----------+-------+
| Jan 2017 |     1 |
| Feb 2017 |     1 |
| Mar 2017 |     0 |
| Apr 2017 |     1 |
| May 2017 |     1 |
| Jun 2017 |     1 |
| Jul 2017 |     3 |
| Aug 2017 |     0 |
| Sep 2017 |     0 |
| Oct 2017 |     0 |
| Nov 2017 |     0 |
| Dec 2017 |     0 |
+----------+-------+

我希望能够按此顺序生成从 2015 到 2017 的 3 年

+----------+-------+
| Months   | Total |
+----------+-------+
| Jan 2017 |     1 |
| Jan 2016 |     1 |
| Jan 2015 |     0 |
| Feb 2017 |     1 |
| Feb 2016 |     1 |
| Feb 2015 |     0 |
...
| Dec 2017 |     0 |
| Dec 2016 |     0 |
| Dec 2015 |     0 |
+----------+-------+

如有任何帮助，我们将不胜感激。

Answer 1

您可以获得年和月的计数表，如下所示:

SET @mn = 1; SET @r = 2015;
SELECT @mn, FLOOR(@yr / 12), CASE
    WHEN @mn = 12 THEN @mn := 1
    WHEN @mn < 12 THEN @mn := @mn + 1 
END,
@yr := @yr + 1 FROM anything WHERE @yr < (2018 * 12)

您可以LEFT JOIN到您的数据表并获取所有年份和月份的列表。也许有更优雅的 SELECT 语句可以实现相同的目的，但至少它有效。 anything 只是用作 SELECT 语句占位符的任何表。为了速度，它应该是一个小的。