问题

我有代码。如何将其更改以显示月份的零总和？

SELECT 
DATE_FORMAT(`item_date_added`, "%m.%Y") AS date, IFNULL(SUM(`item_quantity`), 0) AS sum 
FROM 
`ip_invoice_items` 
WHERE 
YEAR(`item_date_added`) = 2019 
GROUP BY 
MONTH(`item_date_added`);

我尝试使用IFNULL，但没有结果。

<details>
<summary>英文:</summary>

i have code. how can i change it to show month with zero sum?[![enter image description here](https://i.stack.imgur.com/zAGKE.png)](https://i.stack.imgur.com/zAGKE.png)

SELECT
DATE_FORMAT(item_date_added,"%m.%Y") AS date, SUM(item_quantity) AS sum
FROM
ip_invoice_items
WHERE
YEAR(item_date_added)= 2019
GROUP BY
MONTH(item_date_added);

i try use ISNULL and no result. 

</details>


# 答案1
**得分**: 0

以下是翻译好的部分：

- 你可以尝试这个解决方案：
- 在表中生成所有介于最小日期和最大日期之间的月份年份日期，或者你可以手动硬编码控制最小/最大日期（由你决定）。
- 与表进行左连接，以查找空行。
- 根据需要添加更多的日期筛选条件

```mysql
SELECT all_months.date, invoice_items.sum
FROM ( # 生成最小和最大日期之间的所有月份 
    SELECT DATE_FORMAT(m1, '%m.%Y') AS date
    FROM
    (
        SELECT ((SELECT MIN(`item_date_added`) FROM `ip_invoice_items`) + INTERVAL m MONTH) as m1
        FROM
        (
            SELECT @rownum:=@rownum+1 as m 
            FROM (select 1 union select 2 union select 3 union select 4) t1,
                (select 1 union select 2 union select 3 union select 4) t2,
                (select 1 union select 2 union select 3 union select 4) t3,
                (select 1 union select 2 union select 3 union select 4) t4,
                (select @rownum:=-1) t0
        ) d1
    ) d2 
    where m1 <= (SELECT MAX(`item_date_added`) FROM `ip_invoice_items`)
) AS all_months
LEFT JOIN (
    SELECT DATE_FORMAT(`item_date_added`, "%m.%Y") AS date, SUM(`item_quantity`) AS sum 
    FROM `ip_invoice_items`
    GROUP BY DATE_FORMAT(`item_date_added`, "%m.%Y")
) AS invoice_items ON invoice_items.date = all_months.date

SELECT all_months.date, invoice_items.sum
FROM ( # Generating all months between min and max dates 
    SELECT DATE_FORMAT(m1, '%m.%Y') AS date
    FROM
    (
        SELECT ((SELECT MIN(`item_date_added`) FROM `ip_invoice_items`) + INTERVAL m MONTH) as m1
        FROM
        (
            SELECT @rownum:=@rownum+1 as m 
            FROM (select 1 union select 2 union select 3 union select 4) t1,
                (select 1 union select 2 union select 3 union select 4) t2,
                (select 1 union select 2 union select 3 union select 4) t3,
                (select 1 union select 2 union select 3 union select 4) t4,
                (select @rownum:=-1) t0
        ) d1
    ) d2 
    where m1 <= (SELECT MAX(`item_date_added`) FROM `ip_invoice_items`)
) AS all_months
LEFT JOIN (
    SELECT DATE_FORMAT(`item_date_added`,"%m.%Y") AS date, SUM(`item_quantity`) AS sum 
    FROM `ip_invoice_items`
    GROUP BY DATE_FORMAT(`item_date_added`,"%m.%Y")
) AS invoice_items ON invoice_items.date = all_months.date