表:
mysql> select * from gg;
+------+------+-----+-------+
| id | name | sex | class |
+------+------+-----+-------+
| 1 | 张三 | 男 | 1 |
| 2 | 李四 | 女 | 2 |
| 3 | 王五 | 女 | 1 |
+------+------+-----+-------+
3 rows in set (0.00 sec)
sql语句:
select class,sum(case when sex="男" then 1 else 0 end) as numOfMan,
sum(case when sex="女" then 1 else 0 end) as numOfWoman from gg group by class;
结果:
mysql> select class,sum(case when sex="男" then 1 else 0 end) as numOfMan,
-> sum(case when sex="女" then 1 else 0 end) as numOfWoman from gg group by class;
+-------+----------+------------+
| class | numOfMan | numOfWoman |
+-------+----------+------------+
| 1 | 1 | 1 |
| 2 | 0 | 1 |
+-------+----------+------------+
2 rows in set (0.00 sec)