case when 使用:
CREATE TABLE `temp` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`date` varchar(45) DEFAULT NULL,
`state` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ;
INSERT INTO `temp` VALUES (1,'2010-05-09','1'),(2,'2010-05-09','1'),(3,'2010-05-09','0'),(4,'2010-05-09','0'),(5,'2010-05-10','1'),(
6,'2010-05-10','0'),(7,'2010-05-10','0');
SELECT date, sum(case when state='1' then 1 else 0 end) 'success',
sum(case when state='0' then 1 else 0 end) 'failed'
From temp group by date ;