mysql - 如何在mysql中的一个查询中选择和选择计数？

Question

我有这张表:

Activity     Date    
reading   12-10-2014
watching  12-10-2014
reading   13-10-2014
reading   12-10-2014
watching  13-10-2014 

我想要做的是选择事件并根据日期计算事件数量，我希望输出是这样的(条件:where date ='12-10-2014'):

Activity   count    date
reading      2    12-10-2014
watching     1    12-10-2014

我该怎么做？请帮助我。谢谢。

Answer 1

SELECT Activity,COUNT(1) `count`,date
FROM mytable WHERE date='12-10-2014'
GROUP BY date,Activity;

或

SELECT Activity,COUNT(1) `count`,date
FROM mytable WHERE date='2014-10-12'
GROUP BY date,Activity;

确保你的表有日期和事件的索引

ALTER TABLE mytable ADD INDEX date_Activity_ndx (date,Activity);