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

  • 内容
  • 评论
  • 相关

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

题目:

有表如下:

只用一个select语句查询:不及格科目大于或等于2科的学生,的平均分(所有科目的平均分)。


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

欢迎大家阅读《mysql笔考试题一:查询where having 以及统计函数的使用》,跪求各位点评,by 搞代码


要点:

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

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


原创文章,转载请注明: 转载自搞代码

本文链接地址: mysql笔考试题一:查询where having 以及统计函数的使用

微信支付二维码

微信 赏一包辣条吧~

支付宝支付二维码

支付宝 赏一听可乐吧~

评论

0条评论

发表评论

电子邮件地址不会被公开。 必填项已用*标注