MySql count()返回0，如果找不到记录 [英] MySql count() to return 0 if no records found

问题描述

我每个月都有一组帖子.现在，我需要一个数组，其中包含每个月发布的帖子总数.我尝试下面的MySql查询，它的工作正常，但我期望0(零)几个月没有记录.在这里，它不返回0.

I have a set of posts on monthly basis. Now i need an array which contains total records of posts posted in each month. I tried below MySql query, Its working fine, but I was expecting 0(Zero) for months where there is no records. Here its not returning 0.

我读到COUNT()不会返回'0'，那么我该如何实现呢?

I read that COUNT() will not return '0', So how do i achieve this?

我尝试了IFNULL()和COALESCE()，但仍得到相同的结果.请帮助此查询.谢谢……

I tried IFNULL(), and COALESCE() but still getting the same result. Please help with this query. Thank You......

SELECT
count(id) as totalRec
FROM ('post')
WHERE year(date) =  '2013'
AND monthname(date) IN ('January', 'February', 'March') 
GROUP BY year(date)-month(date)
ORDER BY 'date' ASC

得到的结果:

+----------+
| totalRec |
+----------+
|        7 |
|        9 |
+----------+

预期结果(一月份没有职位):

Expected Result (Where there is no posts for January):

+----------+
| totalRec |
+----------+
|        0 |
|        7 |
|        9 |
+----------+

样本数据:

+----+---------------------+
| id | date                |
+----+---------------------+
| 24 | 2012-12-16 16:29:56 |
|  1 | 2013-02-25 14:57:09 |
|  2 | 2013-02-25 14:59:37 |
|  4 | 2013-02-25 15:12:44 |
|  5 | 2013-02-25 15:14:18 |
|  7 | 2013-02-26 11:31:31 |
|  8 | 2013-02-26 11:31:59 |
| 10 | 2013-02-26 11:34:47 |
| 14 | 2013-03-04 04:39:02 |
| 15 | 2013-03-04 05:44:44 |
| 16 | 2013-03-04 05:48:29 |
| 19 | 2013-03-07 15:22:34 |
| 20 | 2013-03-15 12:24:43 |
| 21 | 2013-03-16 16:27:43 |
| 22 | 2013-03-16 16:29:28 |
| 23 | 2013-03-16 16:29:56 |
| 11 | 2013-03-17 11:35:12 |
+----+---------------------+

推荐答案

January月份没有记录，这就是为什么您未获得任何结果的原因.一种有效的解决方案是将一个子查询与包含要在列表中显示的月份列表一起加入.

There is no record for the month of January that is why you are getting no result. One solution that works is by joining a subquery with contains list of months that you want to be shown on the list.

SELECT count(b.id) as totalRec
FROM   (
            SELECT 'January' mnth
            UNION ALL
            SELECT 'February' mnth
            UNION ALL
            SELECT 'March' mnth
        ) a
        LEFT JOIN post b
            ON a.mnth = DATE_FORMAT(b.date, '%M') AND
               year(b.date) =  '2013' AND 
               DATE_FORMAT(b.date, '%M') IN ('January', 'February', 'March') 
GROUP  BY year(b.date)-month(b.date) 
ORDER  BY b.date ASC

• SQLFiddle演示
• SQLFiddle Demo

输出

╔══════════╗
║ TOTALREC ║
╠══════════╣
║        0 ║
║        7 ║
║        9 ║
╚══════════╝

这篇关于MySql count()返回0，如果找不到记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！