时间戳

首页 > 解决方案 > 如果特定月份没有记录，如何获得 0 条记录？

mysql - 如果特定月份没有记录，如何获得 0 条记录？

问题描述

我有查询，显示最近 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

谁能帮我解决这个问题？

标签： mysql

解决方案

尝试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

推荐阅读