按时间，类别分组统计的SQL语句

CREATE TABLE table1 (
id int(11) NOT NULL auto_increment,
type varchar(2) default NULL,
date datetime default NULL,
PRIMARY KEY (id) )
ENGINE=MyISAM DEFAULT CHARSET=gbk;
INSERT INTO `table1 ` (`id`, `type`,`date`) VALUES
(1,'A', '2007-05-28 12:17:59'),
(2,'A', '2007-05-28 13:17:59'),
(3,'B', '2007-05-28 13:23:59'),
(4,'B', '2007-05-28 14:25:59'),
(5,'C', '2007-05-28 15:25:20'),
(6,'C', '2007-05-29 22:25:20'),
(7,'B', '2007-05-29 23:26:20'),
(8,'A', '2007-05-29 09:16:46'),
(9,'B', '2007-05-30 09:16:46');

按照每天日期，然然后分组统计，按A分为一组， B,C分为一组分别进行统计统计结果如下
date A B,C
2007-05-28 2 3
2007-05-29 1 2  
2009-05-30 0 1

有这样一个需求，我给出的sql语句是

SELECT DATE_FORMAT( date,  "%Y-%m-%d"  )  AS date1, (
SELECT count( id )
FROM table1
WHERE  TYPE  =  'A'
AND DATE_FORMAT( date,  "%Y-%m-%d"  )  = date1

) AS A, (
SELECT count( id )
FROM table1
WHERE (
TYPE  =  'B'
OR  TYPE  =  'C'

)
AND DATE_FORMAT( date,  "%Y-%m-%d"  )  = date1

) AS BC
FROM table1
WHERE date
IN (
SELECT date
FROM table1

)
GROUP  BY date1

后来发现有更好的语句

SELECT DATE_FORMAT( date,  '%Y-%m-%d'  ) , sum( if(
TYPE  =  'A', 1, 0  )  )  AS A, sum( if(
TYPE  =  'B'
OR  TYPE  =  'C', 1, 0  )  )  AS BC
FROM table1
GROUP  BY DATE_FORMAT( date,  '%Y-%m-%d'  )