Mysql having 与group的综合练习

数据表result如下,题目如下:

题目要求：查询出2门及两门以上不及格者的平均成绩

------+---------+-------+
| name | subject | score |
+------+---------+-------+
| 张三 | 数学    |    90 |
| 张三 | 语文    |    50 |
| 张三 | 地理    |    40 |
| 李四 | 语文    |    55 |
| 李四 | 政治    |    45 |
| 王五 | 政治    |    30 |
+------+---------+-------+

建表语句:

create table result (
name varchar(20) default null,
subject varchar(20) default null,
score tinyint(4) default null
)engine=myisam default charset=utf8;

插入数据:

insert into result
values
('张三','数学',90),
('张三','语文',50),
('张三','地理',40),
('李四','语文',55),
('李四','政治',45),
('王五','政治',30);

我的思路;

select name,avg(score)
from result
group by name having count(score<(60)) >=2;

结果为：

+------+------------+
| name | avg(score) |
+------+------------+
| 张三 |    60.0000 |
| 李四 |    50.0000 |
+------+------------+

但是如果再插入几条数据,如下：

insert into result
values
('赵六','语文',100),
('赵六','数学',99),
('赵六','品德',98);

数据表现在的结构为：

+------+---------+-------+
| name | subject | score |
+------+---------+-------+
| 张三 | 数学    |    90 |
| 张三 | 语文    |    50 |
| 张三 | 地理    |    40 |
| 李四 | 语文    |    55 |
| 李四 | 政治    |    45 |
| 王五 | 政治    |    30 |
| 赵六 | 语文    |   100 |
| 赵六 | 数学    |    99 |
| 赵六 | 品德    |    98 |
+------+---------+-------+

再执行上面那一条语句select name,avg(score)
from result
group by name having count(score<(60)) >=2;

结果为：

+------+------------+
| name | avg(score) |
+------+------------+
| 张三 |    60.0000 |
| 李四 |    50.0000 |
| 赵六 |    99.0000 |
+------+------------+

你会发现赵六的成绩都及格但是还是被取出来了， 这是为什么呢？

这是因为我错误的理解了count函数的用法， count函数只计算出结果，并没有判断成绩是否真的小于60分！

正解如下:

第一步，我们就查询所有的平均分

 select name,avg(score)
 from result
 group by name;

结果为：

+------+------------+
| name | avg(score) |
+------+------------+
| 张三 |    60.0000 |
| 李四 |    50.0000 |
| 王五 |    30.0000 |
| 赵六 |    99.0000 |
+------+------------+

下一步，再想办法计算出每个人挂科的情况

 select name,subject,score,score<60 as gk from result;

结果为：

------+---------+-------+------+
 name | subject | score | gk   |
------+---------+-------+------+
 张三 | 数学    |    90 |    0 |
 张三 | 语文    |    50 |    1 |
 张三 | 地理    |    40 |    1 |
 李四 | 语文    |    55 |    1 |
 李四 | 政治    |    45 |    1 |
 王五 | 政治    |    30 |    1 |
 赵六 | 语文    |   100 |    0 |
 赵六 | 数学    |    99 |    0 |
 赵六 | 品德    |    98 |    0 |
------+---------+-------+------

下一步，再想办法计算出每个人挂科的情况

select name,subject,score,score<60 as gk from result;

------+---------+-------+------+
 name | subject | score | gk   |
------+---------+-------+------+
 张三 | 数学    |    90 |    0 |
 张三 | 语文    |    50 |    1 |
 张三 | 地理    |    40 |    1 |
 李四 | 语文    |    55 |    1 |
 李四 | 政治    |    45 |    1 |
 王五 | 政治    |    30 |    1 |
 赵六 | 语文    |   100 |    0 |
 赵六 | 数学    |    99 |    0 |
 赵六 | 品德    |    98 |    0 |
------+---------+-------+------+

如上，挂科数目就是gk的sum结果

综合上面两个语句:

 select name,avg(score),sum(score<60) as gks
 from result
 group by name;

结果为：

| name | avg(score) | gks  |
+------+------------+------+
| 张三 |    60.0000 |    2 |
| 李四 |    50.0000 |    2 |
| 王五 |    30.0000 |    1 |
| 赵六 |    99.0000 |    0 |
+------+------------+------+

最终答案为：

 select name,avg(score),sum(score<60) as gks
 from result
 group by name having gks>=2;

结果为：

+------+------------+------+
| name | avg(score) | gks  |
+------+------------+------+
| 张三 |    60.0000 |    2 |
| 李四 |    50.0000 |    2 |
+------+------------+------+
#这一个思路是逆向思维，先查出所有人的平均，再筛选
#如果正常的考虑，我们可能会这么做
#先找出谁的挂科书>=2,找到这些人，再求这些人的平均分
#先找挂科数》=2的那些人

这个思路：设计到子查询：

最后三层嵌套语句为：

select name,avg(score) from 
result
where name in(select name from (select name,count(1) as gks from result
 where score<60
 group by name
 having gks>=2) as tmp)
group by name

同样实现一样的查询结果，前一种比后一种快的太多了，深刻的理解了sql 学习的重要性，也明白了数据库优化的重要性

最后这些都是通过燕十八老师讲的视频学的，非常感谢！