如何在MySQL查询计数中引入完整的24个月跨度(包括空值)

Question

试图使用jQuery插件创建一个“前一年”和“当前年度”图，其中包含了来自我的MySQL数据库的数据。

这样的数据如下所示

   var previousyear = [
        [1,  24monthsago],
        [2,  23monthsago],
        [3,  22monthsago],
        [4,  21monthsago],
        [5,  20monthsago],
        [6,  19monthsago],
        [7,  18monthsago],
        [8,  17monthsago],
        [9,  16monthsago],
        [10, 15monthsago],
        [11, 14monthsago],
        [12, 13monthsago]
    ];
    var currentyear = [
        [1,  12monthsago],
        [2,  11monthsago],
        [3,  10monthsago],
        [4,  9monthsago],
        [5,  8monthsago],
        [6,  7monthsago],
        [7,  6monthsago],
        [8,  5monthsago],
        [9,  4monthsago],
        [10, 3monthsago],
        [11, 2monthsago],
        [12, 1monthago]
    ];

如果我们假设xmonthsago是一个整数值。

我工作的案例是一个库存系统的数据库，我希望用我的inventory_history表为销售的某些SKU创建24个月的期限。

我成功地使用了当前的查询：

SELECT Date_format(lastmodified, '%Y') AS 'year', 
       Date_format(lastmodified, '%m') AS 'month', 
       Count(id)                       AS 'total' 
FROM   inventory_history 
WHERE  inventory_history.sku = 'SKU_NAME' 
GROUP  BY Date_format(lastmodified, '%Y%m');

例如，产出：

╔══════╦═══════╦═══════╗
║ year ║ month ║ total ║
╠══════╬═══════╬═══════╣
║ 2017 ║    08 ║     2 ║
║ 2017 ║    12 ║     1 ║
║ 2018 ║    01 ║     1 ║
║ 2018 ║    03 ║     1 ║
║ 2019 ║    02 ║     1 ║
╚══════╩═══════╩═══════╝

我想知道是否有可能进一步简化这个查询，以给我一个适当的输出，其中包括没有任何COUNT的间隔的所有月份。

输出如下所示：

╔══════╦═══════╦═══════╗
║ year ║ month ║ total ║
╠══════╬═══════╬═══════╣
║ 2017 ║    08 ║     2 ║
║ 2017 ║    09 ║     0 ║
║ 2017 ║    10 ║     0 ║
║ 2017 ║    11 ║     0 ║
║ 2017 ║    12 ║     1 ║
║ 2018 ║    01 ║     1 ║
║ 2018 ║    02 ║     0 ║
║ 2018 ║    03 ║     1 ║
║ 2018 ║    04 ║     0 ║
║ 2018 ║    05 ║     0 ║
║ 2018 ║    06 ║     0 ║
║ 2018 ║    07 ║     0 ║
║ 2018 ║    08 ║     0 ║
║ 2018 ║    09 ║     0 ║
║ 2018 ║    10 ║     0 ║
║ 2018 ║    11 ║     0 ║
║ 2018 ║    12 ║     0 ║
║ 2019 ║    01 ║     0 ║
║ 2019 ║    02 ║     1 ║
║ 2019 ║    03 ║     0 ║
║ 2019 ║    04 ║     0 ║
║ 2019 ║    05 ║     0 ║
║ 2019 ║    06 ║     0 ║
║ 2019 ║    07 ║     0 ║
║ 2019 ║    08 ║     0 ║
╚══════╩═══════╩═══════╝

有人知道我如何使用MySQL查询来完成这个任务吗？

Answer 1

如果表中有所有月份的数据，最简单的方法可能是条件聚合：

SELECT year(lastmodified) AS year, 
       month(lastmodified) AS month, 
       SUM(ih.sku = 'SKU_NAME') AS total 
FROM inventory_history ih
GROUP BY year, month;

备注：

• 当date_format()具有方便的内置函数时，没有理由使用MySQL。
• 仅对字符串和日期常量使用单引号，特别是对列别名使用单引号。
• 你不需要转义你的列别名。
• GROUP BY列应该与SELECT中的未聚合列匹配。他们可以使用化名。
• SUM()使用了非常方便的MySQL特性。它将布尔值视为整数，1表示true，0表示false。