一道很好的mysql面试练习题,having综合应用

写一条SQL语句，求出2门以及2门以上不及格的科目平均分

>要出现2门以及2门以上的学科不及格

>计算该考生所有学科的平均分，不单是，不及格的那几门

#创建表:

create table `ecs_mian2` (

    `user_name` varchar (20),

    `subject` varchar (20),

    `score` int (4)

);

insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('张三','数学','');

insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('张三','语文','');

insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('张三','地理','');

insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('李四','语文','');

insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('李四','政治','');

insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('王五','政治','');

insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('赵六','物理','');

insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('赵六','化学','');

insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('赵六','语文','');

insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('赵六','数学','');

思路一：

求出score < 60的科目数，然后统计平均分

#这种方法：用where过滤了score < 60的科目，就算不到score >= 60的科目的平均分

SELECT user_name, AVG(score ) AS avg_score,

COUNT( * ) AS num FROM ecs_mian2 WHERE score < 60 GROUP BY user_name  HAVING num >= 2

这个结果是错误的，原因在于： " 用where过滤了score < 60的科目，就算不到score >= 60的科目的平均分 "

思路二：

#查出所有人的平均分

SELECT user_name,AVG( score ) FROM ecs_mian2 GROUP BY user_name;

#查出所有人不及格的课程(不及格标记为1,及格标记为0 )

SELECT user_name,score, score < 60 FROM ecs_mian2;

#不及格在2门以上的人

SELECT user_name,SUM( score < 60 ) AS bujige FROM ecs_mian2 GROUP BY user_name HAVING bujige >= 2

#综合以上3条语句的结果，可以得出，不及格科目>=2的人的所有学科平均分

SELECT user_name,AVG( score ) AS pjf, SUM( score < 60 ) AS bujige FROM ecs_mian2 GROUP BY user_name HAVING bujige >= 2

该思路不同于where，没有过滤任何一门学科的分数，所以能够统计到平均分

 小结：

1，mysql语句也需要灵活的思路

2，表中的字段，可以认为是变量，变量当然可以计算，比较，调用函数等