在MySQL中需要某种“条件分组”

总和加上大小写就行了

select date
    , sum(case when type ='A' then 1 else 0 end) as count_type_a 
    , sum(case when type ='B' then 1 else 0 end) as count_type_b
    , sum(case when type ='C' then 1 else 0 end) as count_type_c 
from article group by date

编辑: Alex's answer above 使用一个更好的方法,在这个答案。我把它放在这里只是因为它也满足了这个问题,以另一种方式:

您应该能够使用子查询,如下所示:

SELECT    DATE(a.date) as date,
          (SELECT COUNT(a1.id) FROM articles a1 WHERE a1.type = 'A' AND a1.date = a.date) count_a,
          (SELECT COUNT(a2.id) FROM articles a2 WHERE a2.type = 'B' AND a2.date = a.date) count_b,
          (SELECT COUNT(a3.id) FROM articles a3 WHERE a3.type = 'C' AND a3.date = a.date) count_c
FROM      articles a
GROUP BY  a.date;

测试用例:

CREATE TABLE articles (id int, type char(1), date datetime);

INSERT INTO articles VALUES (1, 'A', '2010-01-01');
INSERT INTO articles VALUES (2, 'A', '2010-01-01');
INSERT INTO articles VALUES (3, 'B', '2010-01-01');
INSERT INTO articles VALUES (4, 'B', '2010-01-02');
INSERT INTO articles VALUES (5, 'B', '2010-01-02');
INSERT INTO articles VALUES (6, 'B', '2010-01-03');
INSERT INTO articles VALUES (7, 'B', '2010-01-01');
INSERT INTO articles VALUES (8, 'C', '2010-01-05');

+------------+---------+---------+---------+
| date       | count_a | count_b | count_c |
+------------+---------+---------+---------+
| 2010-01-01 |       2 |       2 |       0 |
| 2010-01-02 |       0 |       2 |       0 |
| 2010-01-03 |       0 |       1 |       0 |
| 2010-01-05 |       0 |       0 |       1 |
+------------+---------+---------+---------+
4 rows in set (0.00 sec)