mysql - 如果某个月份没有记录，如何获取 0 条记录？

Question

这个问题已经有答案了:

What is the most straightforward way to pad empty dates in sql results (on either mysql or perl end)? (9 个回答)

Mysql to select month-wise record even if data not exist (2 个回答)

已关闭 4 年前。

我有查询，显示最近 12 个月的记录，它对我来说工作正常，但我想显示那个没有记录的月份，如果特定月份没有记录，那么我想显示 0 条记录对于那个月，我在这里添加了我的查询

SELECT `amount`, CONVERT_TZ(created, "+00:00", "-05:00") as created, 
       MONTHNAME(CONVERT_TZ(created, "+00:00", "-05:00")) as month, 
       `sponsorion_fees`, `processor_fees`, `amount_after_fees`, 
       SUM(amount_after_fees) as total FROM `transaction` 
WHERE `record_owner_user_id` = '50' AND `is_one_time_purchase` = 'Y' 
       AND CONVERT_TZ(created,"+00:00","-05:00") <= "2018-11-26 07:08:24"
       and CONVERT_TZ(created,"+00:00","-05:00") >= 
       Date_add("2018-11-26 07:08:24",interval - 12 month)
GROUP BY `month` ORDER BY `id` ASC

谁能帮我解决这个问题吗？

Answer 1

尝试IFNULL :

SELECT `amount`, CONVERT_TZ(created, "+00:00", "-05:00") as created,
                 MONTHNAME(CONVERT_TZ(created, "+00:00", "-05:00")) as month,
                 `sponsorion_fees`, `processor_fees`, `amount_after_fees`, 
                 SUM(IFNULL(amount_after_fees, 0)) as total FROM `transaction`
WHERE `record_owner_user_id` = '50' 
                     AND `is_one_time_purchase` = 'Y' 
                     AND CONVERT_TZ(created,"+00:00","-05:00") <= "2018-11-26 07:08:24" 
                     and CONVERT_TZ(created,"+00:00","-05:00") >= Date_add("2018-11-26 07:08:24",interval - 12 month)
GROUP BY `month` 
ORDER BY `id` ASC

也可以尝试COALESCE :

SELECT `amount`, CONVERT_TZ(created, "+00:00", "-05:00") as created,
                     MONTHNAME(CONVERT_TZ(created, "+00:00", "-05:00")) as month,
                     `sponsorion_fees`, `processor_fees`, `amount_after_fees`, 
                     COALESCE(SUM(amount_after_fees),0) as total FROM `transaction`
    WHERE `record_owner_user_id` = '50' 
                         AND `is_one_time_purchase` = 'Y' 
                         AND CONVERT_TZ(created,"+00:00","-05:00") <= "2018-11-26 07:08:24" 
                         and CONVERT_TZ(created,"+00:00","-05:00") >= Date_add("2018-11-26 07:08:24",interval - 12 month)
    GROUP BY `month` 
    ORDER BY `id` ASC