如果没有找到记录，MySql count() 返回 0
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16636433/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySql count() to return 0 if no records found
提问by sravis
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.
我每个月都有一组帖子。现在我需要一个数组，其中包含每个月发布的帖子的总记录。我在 MySql 查询下尝试过，它工作正常，但我期待几个月没有记录的 0（零）。这里它不返回0。
I read that COUNT() will not return '0', So how do i achieve this?
我读到 COUNT() 不会返回“0”，那么我该如何实现呢？
I tried IFNULL(), and COALESCE() but still getting the same result. Please help with this query. Thank You......
我尝试了 IFNULL() 和 COALESCE() 但仍然得到相同的结果。请帮忙解决这个问题。谢谢你......
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
Got Result:
得到结果：
+----------+
| totalRec |
+----------+
| 7 |
| 9 |
+----------+
Expected Result (Where there is no posts for January):
预期结果（一月没有职位的地方）：
+----------+
| totalRec |
+----------+
| 0 |
| 7 |
| 9 |
+----------+
Sample Data:
样本数据：
+----+---------------------+
| 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 |
+----+---------------------+
回答by John Woo
There is no record for the month of Januarythat 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.
那个月没有记录，January这就是为什么你没有得到任何结果。一种有效的解决方案是加入包含要显示在列表中的月份列表的子查询。
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
OUTPUT
输出
╔══════════╗
║ TOTALREC ║
╠══════════╣
║ 0 ║
║ 7 ║
║ 9 ║
╚══════════╝
回答by kiriloff
Did you try IFNULL()the right way? Maybe try IFNULL(Count(id), 0)in a SELECTclause with join.
你尝试IFNULL()正确的方法了吗？也许尝试加入IFNULL(Count(id), 0)一个SELECT子句。
回答by Paul Stanley
COALESCEis what you could use, if you have a table of dates and left joined against it. It goes left to right to return the first non null value.
Your group by does look a little nutty at the minute, I have adjusted it.
COALESCE如果您有一个日期表并左连接它，那么您可以使用它。它从左到右返回第一个非空值。你的小组现在看起来有点疯狂，我已经调整过了。
SELECT
COALESCE(count(id),0) as totalRec
FROM ('post')
LEFT JOIN dates
ON dates.date = post.date
WHERE year(date) = '2013'
AND monthname(date) IN ('January', 'February', 'March')
GROUP BY month(date), year(date)
ORDER BY 'date' ASC
Where dates table is..
日期表在哪里..
DATE
2013-01-01
2013-01-02
2013-01-03
etc....
See here : http://easywebapps.blogspot.co.uk/2011/07/mysql-how-to-create-calendar-table.html
见这里：http: //easywebapps.blogspot.co.uk/2011/07/mysql-how-to-create-calendar-table.html
回答by Justin
If the result set had no posts during that time period, you won't get any results to count, hence why it doesn't show.
如果结果集在该时间段内没有帖子，您将不会得到任何结果可计算，因此它不显示。
You would need to either join to another table that has all of the year months or fill in the data programmatically when the results are returned. I can't think of another way to do this, but perhaps it's possible.
您需要连接到另一个包含所有年份月份的表，或者在返回结果时以编程方式填写数据。我想不出另一种方法来做到这一点，但也许这是可能的。
Also, as others have said, separate the group by a comma.
此外，正如其他人所说，用逗号分隔组。
回答by HSP
Try Like this::
像这样尝试::
SELECT
count(id) as totalRec
IF(id NULL, 0, id) As IdList
FROM ('post')
WHERE year(date) = '2013'
AND monthname(date) IN ('January', 'February', 'March')
GROUP BY year(date)-month(date)
ORDER BY 'date' ASC
To return 0 instead of null in MySQL
USE
在 MySQL
USE 中返回 0 而不是 null
SELECT id, IF(age IS NULL, 0, age) FROM tblUser
SELECT id, IF(age IS NULL, 0, age) FROM tblUser
USE with count() having join of 2 tables
使用 count() 连接 2 个表
Tables
表
tblA
tblA
tblA_Id Name
-----------------
1 HSP
2 MANI
3 MEET
4 user
5 jaani
tblB
表
tblB_Id SongName tblA_Id
-----------------------------
1 song1 3
2 song2 3
3 song3 1
4 song4 1
5 song4 5
Query
询问
SELECT
tblA.tblA_Id,
tblA.Name,
tblC.SongCount,
IF(tblC.SongCount IS NULL, 0, tblC.SongCount) As noOfSong
FROM tblA
LEFT JOIN
(
SELECT
ArtistId,count(*) AS SongCount
FROM
tblB
GROUP BY
ArtistId
) AS tblC
ON
tblA.tblA_Id = NoOfSong.ArtistId
Result is
结果是
tblA_Id Name SongCount noOfSong
-------------------------------------------
5 jaani 1 1
4 user NULL 0
3 MEET 2 2
2 MANI NULL 0
1 HSP 2 2
回答by MRRaja
I think you simply need query like this
我认为你只需要这样的查询
SELECT COALESCE(COUNT(id),0) AS totid FROM table
vb example
VB 例子
Set count=Con.Execute("SELECT COALESCE(COUNT(id),0) AS totid FROM table")
then write
然后写
<%=count("totid")%>
