表如下
id name type
1 aa 0
2 bb 1
3 aa 1
4 bb 0
5 aa 0
结果
name type=1 type=0
aa 1 2
bb 1 1
有高手么帮忙写条查询语句实现
id name type
1 aa 0
2 bb 1
3 aa 1
4 bb 0
5 aa 0
结果
name type=1 type=0
aa 1 2
bb 1 1
有高手么帮忙写条查询语句实现
调试欢乐多
+----+------+------+
| 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>