Mysql统计结果为零值

Question

在mysql中，我得到了表“分数”，如下所示

Id  Date                Name        score
1   2011-08-24 00:00:00 sharique    10
2   2011-08-24 00:00:00 joe     11
3   2011-08-24 00:00:00 vijay       5
4   2011-08-25 00:00:00 sharique    0
5   2011-08-25 00:00:00 joe    11

现在当我运行查询时，

SELECT date,count(id) as count FROM scores where `name` = 'vijay' group by `date`;

我得到的结果是

date                 count
2011-08-24 00:00:00,  1

而不是

date                 count
2011-08-24 00:00:00,  1
2011-08-25 00:00:00,  0

如何显示零计数的结果，请问？

Answer 1

这是一种简单的方法：

SELECT s2.date, count(s1.id) as count
FROM (select distinct `date` from scores) s2 
  left join scores s1
    on  s1.`date` = s2.`date`
    and s1.`name` = 'vijay'
group by 1

这保证了表中每个不同日期的每个人都有一个日期

Answer 2

这是您计算与 where 条件匹配的行数时的预期结果。

SELECT date,count(id) as count FROM scores where `name` = 'vijay' group by `date`;

它只选择一条记录，因此计数结果为 1。

如果您想对所有行进行计数，请不要使用 Where Clause 来限制数据，例如：-

SELECT date,count(id) as count FROM scores group by `date`;

同样，以下查询将返回 3作为计数..

SELECT date,count(id) as count FROM scores where id in (1,2,3) group by `date`;