mysql笔试题一：查询where having 以及统计函数的使用

micromax

　　题目：
　　有表如下：
　　只用一个select语句查询：不及格科目大于或等于2科的学生，的平均分（所有科目的平均分）。
　　

　　

mysql> select * from student;
+------+---------+--------+
| name | subject | score |
+------+---------+--------+
| 张三 | 数学    |     80 |
| 张三 | 语文    |     53 |
| 张三 | 英语    |     59 |
| 李四 | 数学    |     55 |
| 李四 | 语文    |     56 |
| 李四 | 英语    |     50 |
| 王五 | 数学    |    100 |
| 王五 | 语文    |     90 |
+------+---------+--------+
8 rows in set


要点：　　
　　1、要统计每个学生不及格科目的个数。

　　2、计算平均分。
　　

　　误解一：使用count统计。count永远是统计的所有行！
　　正解：用sum统计。先计算所有人的平均分，再筛选。
　　

mysql> select name,avg(score),sum(score < 60) as cnt from student group by name having cnt >= 2;
+------+------------+-----+
| name | avg(score) | cnt |
+------+------------+-----+
| 张三 | 64.0000    | 2   |
| 李四 | 53.6667    | 3   |
+------+------------+-----+
2 rows in set


　　
　　sum(score < 60) 就是统计 挂科数目。
　　having 是用于筛选的，这里不用用where。where只能用于存在的列。
　　

　　当然也可以用 子查询 左链接, 这样反到麻烦了。
　　

　　题目2：还是上面的表。
　　查询每个学生的最大分数的科目及分数。
　　误解1：
　　

mysql> select *,max(score) from student group by name;
+------+---------+-------+------------+
| name | subject | score | max(score) |
+------+---------+-------+------------+
| 张三 | 数学    |    80 |         80 |
| 李四 | 数学    |    55 |         56 |
| 王五 | 数学    |   100 |        100 |
+------+---------+-------+------------+
3 rows in set
　　


虽然查出了最大分数，但显示的科目是错误的。因为group by总是取第一条记录。　　

　　正解：
　　1、where 子查询 (先找到每个学生的最大分数， 在根据分数刷新出记录)。
　　

mysql> select * from student where score in(select max(score) from student group by name);
+------+---------+-------+
| name | subject | score |
+------+---------+-------+
| 张三 | 数学    |    80 |
| 李四 | 语文    |    56 |
| 王五 | 数学    |   100 |
+------+---------+-------+
3 rows in set


2、from子查询 (先排序，在group by 拿到第一个，即最大分数的那条记录)　　

mysql> select * from (select * from student order by score desc) as tmp group by name;
+------+---------+-------+
| name | subject | score |
+------+---------+-------+
| 张三 | 数学    |    80 |
| 李四 | 语文    |    56 |
| 王五 | 数学    |   100 |
+------+---------+-------+
3 rows in set


整两种方法是有很大区别的，第二种只能取出一条记录，如果最大分数相同的话。