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

bitsCN.commysql笔试题一：查询where having以及统计函数的使用 题目： 有表如下：只用一个select语句查询：不及格科目大于或等于2科的学生，的平均分（所有科目的平均分）。 [sql] mysql> select * from student; +------+---------+--------+ | name | subject | score | +------+---------+--------+ | 张三 | 数学 | 80 | | 张三 | 语文 | 53 | | 张三 | 英语 | 59 | | 李四 | 数学 | 55 | | 李四 | 语文 | 56 | | 李四 | 英语 | 50 | | 王五 | 数学 | 100 | | 王五 | 语文 | 90 | +------+---------+--------+ 8 rows in set 要点： 1、要统计每个学生不及格科目的个数。 2、计算平均分。 误解一：使用count统计。count永远是统计的所有行！ 正解：用sum统计。先计算所有人的平均分，再筛选。 [sql] mysql> select name,avg(score),sum(score = 2; +------+------------+-----+ | name | avg(score) | cnt | +------+------------+-----+ | 张三 | 64.0000 | 2 | | 李四 | 53.6667 | 3 | +------+------------+-----+ 2 rows in set sum(score having 是用于筛选的，这里不用用where。where只能用于存在的列。 当然也可以用 子查询 左链接, 这样反到麻烦了。 题目2：还是上面的表。 查询每个学生的最大分数的科目及分数。 误解1： [sql] 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 子查询 (先找到每个学生的最大分数， 在根据分数刷新出记录)。 [sql] 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 拿到第一个，即最大分数的那条记录)[sql] 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 整两种方法是有很大区别的，第二种只能取出一条记录，如果最大分数相同的话。 bitsCN.com