MYSQL - 计算每天按其他字段分组的行数

Question

为了绘制活动图表，我们如何统计每天每种类型（不同字段值）的行数？

考虑一个具有日期字段为每种类型的一个表和一个字段：

CREATE TABLE TableName 
    (`PK` int, `type` varchar(1), `timestamp` datetime) 
; 

INSERT INTO TableName 
    (`PK`, `type`, `timestamp`) 
VALUES 
    (11, 'Q', '2013-01-04 22:23:56'), 
    (7, 'A', '2013-01-03 22:23:41'), 
    (8, 'C', '2013-01-04 22:23:42'), 
    (10, 'Q', '2013-01-05 22:23:56'), 
    (5, 'C', '2013-01-03 22:23:25'), 
    (12, 'Q', '2013-01-05 22:23:57'), 
    (6, 'Q', '2013-01-07 22:23:40'), 
    (4, 'Q', '2013-01-02 22:23:23'), 
    (9, 'A', '2013-01-05 22:23:55'), 
    (1, 'A', '2013-01-08 21:29:38'), 
    (2, 'Q', '2013-01-02 21:31:59'), 
    (3, 'C', '2013-01-04 21:32:22') 
; 

例如输出可以是（最后的字段是与该类型和在这天的行数）：

'Q', 2013-01-04, 1 
'C', 2013-01-04, 2 
'A', 2013-01-03, 1 
'C', 2013-01-03, 2 
and so on... 

Answer 1

select `type`, date(`timestamp`) as the_date, count(*) as counter 
from MyTable 
group by `type`, date(`timestamp`) 

Answer 2

你只需要一个group by。

select `type`, date(`timestamp`), count(*) 
from tableName 
group by `type`, date(`timestamp`)