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
sum(if(type='B' OR TYPE='C',1,0)) AS BC
from table1
group by DATE_FORMAT(date,'%Y-%m-%d')
sum(case when type='A' then 1 else 0 end) AS A,
sum(case when type='B' or TYPE='C' then 1 else 0 end) AS BC
from table1
group by DATE_FORMAT(date,'%Y-%m-%d')
mysql> select * from table1;
+----+------+---------------------+
| id | type | date |
+----+------+---------------------+
| 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 |
+----+------+---------------------+
9 rows in set (0.00 sec)mysql> select date(`date`),sum(IF(type='A',1,0)) as A,sum(IF(type='B' or type='C' ,1,0)) as BC
-> from table1
-> group by date(`date`);
+--------------+------+------+
| date(`date`) | A | BC |
+--------------+------+------+
| 2007-05-28 | 2 | 3 |
| 2007-05-29 | 1 | 2 |
| 2007-05-30 | 0 | 1 |
+--------------+------+------+
3 rows in set (0.05 sec)mysql>
date_format(`date`,'%Y-%m-%d') as `date`,
sum(case when `type` = 'A' then 1 else 0 end) as `A`,
sum(case when `type`='B' or `type`='C' then 1 else 0 end) as `B,C`
from
`table1`
group by
date_format(`date`,'%Y-%m-%d')