t1表数据: +------+------+------+------+ | id | name | dept | flag | +------+------+------+------+ | 1 | w | w | 0 | | 1 | w | w | 1 | | 2 | w | w | 1 | | 2 | w | w | 0 | | 2 | a | a | 0 | | 2 | a | a | 1 | | 2 | a | a | 1 | | 2 | w | w | 1 | +------+------+------+------+希望的结果,id,name,dept 三个字段group by后 得到对应的flag为0的个数 f0num和 flag为1的个数 f1num。
select id,name,dept,sum(case when flag=0 then 1 else 0 end) f0num, sum(case when flag=1 then 1 else 0 end) f1num from t1 group by id,name,dept
t1表数据:
+------+------+------+------+
| id | name | dept | flag |
+------+------+------+------+
| 1 | w | w | 0 |
| 1 | w | w | 1 |
| 2 | w | w | 1 |
| 2 | w | w | 0 |
| 2 | a | a | 0 |
| 2 | a | a | 1 |
| 2 | a | a | 1 |
| 2 | w | w | 1 |
+------+------+------+------+希望的结果,id,name,dept 三个字段group by后 得到对应的flag为0的个数 f0num和 flag为1的个数 f1num。
sum(case when flag=1 then 1 else 0 end) f1num from t1 group by id,name,dept