mysql - 当日期范围内没有结果时选择零

Question

我有一个查询，我正在使用它来提取前 6 个月数据的每月总成本。我需要解决的问题是当没有特定月份的记录时，不会返回任何内容并且只显示 5 个月。

我需要修改此查询以始终显示 6 个月，即使没有特定月份的数据也是如此，但我不确定如何完成此操作。

        select sum(cost),
            CASE
                WHEN MONTH(collection_date) = 1 THEN 'January'
                WHEN MONTH(collection_date) = 2 THEN 'February'
                WHEN MONTH(collection_date) = 3 THEN 'March'
                WHEN MONTH(collection_date) = 4 THEN 'April'
                WHEN MONTH(collection_date) = 5 THEN 'May'
                WHEN MONTH(collection_date) = 6 THEN 'June'
                WHEN MONTH(collection_date) = 7 THEN 'July'
                WHEN MONTH(collection_date) = 8 THEN 'August'
                WHEN MONTH(collection_date) = 9 THEN 'September'
                WHEN MONTH(collection_date) = 10 THEN 'October'
                WHEN MONTH(collection_date) = 11 THEN 'November'
                WHEN MONTH(collection_date) = 12 THEN 'December'
                ELSE 'NULL'
                END AS datemodified
            from invoices
            WHERE collection_date >= DATE_SUB(now(), INTERVAL 5 MONTH)
            GROUP BY MONTH(collection_date)
            ORDER BY collection_date asc;

带有空月份的结果示例

 COST      Datemodified      
 300       September
 200       November
 200       December

期望的输出

 COST      Datemodified
 0         August
 300       September
 0         October
 200       November
 200       December

Answer 1

您可以创建虚假的月份数据并将您的发票表加入其中。试试这个:

SELECT SUM(cost), months.name AS datemodified
FROM (SELECT 1 AS num, 'January' AS name
      UNION SELECT 2, 'February'
      UNION SELECT 3, 'March'
      UNION SELECT 4, 'April'
      UNION SELECT 5, 'May'
      UNION SELECT 6, 'June'
      UNION SELECT 7, 'July'
      UNION SELECT 8, 'August'
      UNION SELECT 9, 'September'
      UNION SELECT 10, 'October'
      UNION SELECT 11, 'November'
      UNION SELECT 12, 'December') months
LEFT JOIN invoices.collection_date = months.num
WHERE collection_date >= DATE_SUB(NOW(), INTERVAL 5 MONTH)
GROUP BY MONTH(collection_date)
ORDER BY collection_date ASC;

但是，这给了您所有 12 个月的时间。要仅获取最近 6 个月的数据，您需要动态生成您的假月份数据:

SELECT SUM(cost),
       CASE num WHEN 1 THEN 'January'
                WHEN 2 THEN 'February'
                WHEN 3 THEN 'March'
                WHEN 4 THEN 'April'
                WHEN 5 THEN 'May'
                WHEN 6 THEN 'June'
                WHEN 7 THEN 'July'
                WHEN 8 THEN 'August'
                WHEN 9 THEN 'September'
                WHEN 10 THEN 'October'
                WHEN 11 THEN 'November'
                WHEN 12 THEN 'December'
       END AS datemodified
FROM (SELECT MONTH(NOW()) AS num
      UNION SELECT MONTH(DATE_SUB(NOW(), INTERVAL 1 MONTH)) AS num
      UNION SELECT MONTH(DATE_SUB(NOW(), INTERVAL 2 MONTH)) AS num
      UNION SELECT MONTH(DATE_SUB(NOW(), INTERVAL 3 MONTH)) AS num
      UNION SELECT MONTH(DATE_SUB(NOW(), INTERVAL 4 MONTH)) AS num
      UNION SELECT MONTH(DATE_SUB(NOW(), INTERVAL 5 MONTH)) AS num) months
LEFT JOIN invoices.collection_date = months.num
WHERE collection_date >= DATE_SUB(NOW(), INTERVAL 5 MONTH)
GROUP BY MONTH(collection_date)
ORDER BY collection_date ASC;