当GroupByMonth返回NULL时获得0

I`m trying to get values with mysql, but it gives no 0 for specific months when there is no value for it...

我试图用mysql获取值,但是当它没有值时,它在特定月份没有给出...

I tried IFNULL but it makes same and not returning 0 for the empty month.

我尝试了IFNULL,但它在同一个月没有返回0。

 SELECT IFNULL(COUNT(`bileti_id`),0) FROM `wp_biletistatus` WHERE
 `user_id`= 1 and `status`=1 GROUP BY MONTH(`date`) ORDER BY `date` ASC
 Limit 10

Here is my query as well for months which i want to get:

这是我想要获得的几个月的查询:

 SELECT MONTHNAME(`date`) FROM `wp_biletistatus` WHERE `user_id`= 1
 GROUP BY MONTH(`date`) ORDER BY `date` ASC Limit 10

Can someone help me how to get 0 for the months which has no value to count.

有人可以帮助我如何在没有任何价值的月份获得0。

1 个解决方案

#1

the reason why you are not getting zero on months that don't have value is because simply it does not exist. You need to have a fix values or atleast a subquery which have the list of all months, example.

你没有在没有价值的月份获得零的原因是因为它根本不存在。您需要具有修复值或至少具有所有月份列表的子查询,例如。

SELECT months.MonthNum,
    COUNT(a.bileti_id) 
FROM    (
    SELECT 1 AS MonthNum UNION ALL SELECT 2 UNION ALL
    SELECT 3  UNION ALL SELECT 4 UNION ALL
    SELECT 5  UNION ALL SELECT 6 UNION ALL
    SELECT 7  UNION ALL SELECT 8 UNION ALL
    SELECT 9  UNION ALL SELECT 10 UNION ALL
    SELECT 11  UNION ALL SELECT 12
    ) months
    LEFT JOIN wp_biletistatus a
        ON months.MonthNum = MONTH(a.date)
WHERE a.user_id= 1 and a.status=1 
GROUP BY months.MonthNum  
ORDER BY months.MonthNum