mysql> select * from weihongchang; +----+------+------+ | id | name | type | +----+------+------+ | 1 | aa | 0 | | 2 | bb | 1 | | 3 | aa | 1 | | 4 | bb | 0 | | 5 | aa | 0 | +----+------+------+ 5 rows in set (0.00 sec)mysql> select name , -> sum(if(type=1,1,0)) as `type=1`, -> sum(if(type=0,1,0)) as `type=0` -> from weihongchang -> group by name; +------+--------+--------+ | name | type=1 | type=0 | +------+--------+--------+ | aa | 1 | 2 | | bb | 1 | 1 | +------+--------+--------+ 2 rows in set (0.00 sec)mysql>
对于这个表中TYPE这种特殊可以mysql> select name , -> sum(type) as `type=1`, -> sum(1-type) as `type=0` -> from weihongchang -> group by name; +------+--------+--------+ | name | type=1 | type=0 | +------+--------+--------+ | aa | 1 | 2 | | bb | 1 | 1 | +------+--------+--------+ 2 rows in set (0.00 sec)mysql> 同样也可以把IF换成CASE WHEN mysql> select name , -> sum(case when type=1 then 1 else 0 end) as `type=1`, -> sum(case when type=0 then 1 else 0 end) as `type=0` -> from weihongchang -> group by name; +------+--------+--------+ | name | type=1 | type=0 | +------+--------+--------+ | aa | 1 | 2 | | bb | 1 | 1 | +------+--------+--------+ 2 rows in set (0.00 sec)mysql> select name , -> sum(case type when 1 then 1 else 0 end) as `type=1`, -> sum(case type when 0 then 1 else 0 end) as `type=0` -> from weihongchang -> group by name; +------+--------+--------+ | name | type=1 | type=0 | +------+--------+--------+ | aa | 1 | 2 | | bb | 1 | 1 | +------+--------+--------+ 2 rows in set (0.00 sec)mysql>
+----+------+------+
| id | name | type |
+----+------+------+
| 1 | aa | 0 |
| 2 | bb | 1 |
| 3 | aa | 1 |
| 4 | bb | 0 |
| 5 | aa | 0 |
+----+------+------+
5 rows in set (0.00 sec)mysql> select name ,
-> sum(if(type=1,1,0)) as `type=1`,
-> sum(if(type=0,1,0)) as `type=0`
-> from weihongchang
-> group by name;
+------+--------+--------+
| name | type=1 | type=0 |
+------+--------+--------+
| aa | 1 | 2 |
| bb | 1 | 1 |
+------+--------+--------+
2 rows in set (0.00 sec)mysql>
-> sum(type) as `type=1`,
-> sum(1-type) as `type=0`
-> from weihongchang
-> group by name;
+------+--------+--------+
| name | type=1 | type=0 |
+------+--------+--------+
| aa | 1 | 2 |
| bb | 1 | 1 |
+------+--------+--------+
2 rows in set (0.00 sec)mysql>
同样也可以把IF换成CASE WHEN
mysql> select name ,
-> sum(case when type=1 then 1 else 0 end) as `type=1`,
-> sum(case when type=0 then 1 else 0 end) as `type=0`
-> from weihongchang
-> group by name;
+------+--------+--------+
| name | type=1 | type=0 |
+------+--------+--------+
| aa | 1 | 2 |
| bb | 1 | 1 |
+------+--------+--------+
2 rows in set (0.00 sec)mysql> select name ,
-> sum(case type when 1 then 1 else 0 end) as `type=1`,
-> sum(case type when 0 then 1 else 0 end) as `type=0`
-> from weihongchang
-> group by name;
+------+--------+--------+
| name | type=1 | type=0 |
+------+--------+--------+
| aa | 1 | 2 |
| bb | 1 | 1 |
+------+--------+--------+
2 rows in set (0.00 sec)mysql>