一个mysql的问题

Question

有一个表 mytable 有下面几个字段
id    class(学生班级)    studentSex(学生性别,1男2女)  studentName(学生性名)假设现有十几个班级,
我想获得班级中男学生最多的三条相应记录,返回结果类似如下:
class    boy(男同学数量,这个降序)  girl(女同学数量)
1         20                         10
6          19                        11
3          18                        20我想知道用一条SQL语句如何实现这个结果,谢谢

Answer 1

mysql> select * from tab;
+------+--------+------------+-------------+
| id   | class  | studentsex | studentname |
+------+--------+------------+-------------+
|    1 | class1 |          1 | s1          |
|    2 | class1 |          0 | s2          |
|    3 | class2 |          1 | s3          |
|    4 | class1 |          1 | s4          |
|    5 | class1 |          1 | s5          |
|    6 | class1 |          0 | s6          |
|    7 | class2 |          1 | s7          |
|    8 | class2 |          0 | s8          |
|    9 | class3 |          1 | s9          |
|   10 | class3 |          0 | s10         |
|   11 | class3 |          0 | s11         |
|   12 | class4 |          1 | s12         |
|   13 | class4 |          1 | s13         |
|   14 | class4 |          1 | s14         |
+------+--------+------------+-------------+
14 rows in set (0.00 sec)mysql> select class,sum(if(studentsex=1,1,0)) boy,
    -> sum(if(studentsex=1,0,1)) girl
    -> from tab group by class
    -> order by boy desc,girl desc
    -> limit 3;
+--------+------+------+
| class  | boy  | girl |
+--------+------+------+
| class1 |    3 |    2 |
| class4 |    3 |    0 |
| class2 |    2 |    1 |
+--------+------+------+
3 rows in set (0.03 sec)