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

1道很好的mysql面试演习题,having综合使用,mysqlhaving

写一条SQL语句,求出二门以及二门之上比不上格的学科平均分

>要出现2门以及二门之上的教程比不上格

>计算该考生全体科目标平分分,不单是,不比格的那几门

 #创建表:

create table `ecs_mian2` (
    `user_name` varchar (20),
    `subject` varchar (20),
    `score` int (4)
); 
insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('张三','数学','90');
insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('张三','语文','50');
insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('张三','地理','40');
insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('李四','语文','55');
insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('李四','政治','45');
insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('王五','政治','30');
insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('赵六','物理','30');
insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('赵六','化学','50');
insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('赵六','语文','45');
insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('赵六','数学','90');

图片 1

 

思路一:

求出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

图片 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门科目标分数,所以可以总结到平均分

图片 3

 

 小结:

壹,mysql语句也急需灵活的笔触

二,表中的字段,能够以为是变量,变量当然能够计算,比较,调用函数等

写一条SQL语句,求出2门以及贰门以上比不上格的课程平均分
要出现二门以及二门之上的学科…

该思路区别于where,未有过滤任何1门课程的分数,所以能够总结到平均分

 

求出score < 60的科目数,然后总结平均分

>要出现贰门以及二门上述的科目不比格

>总括该考生具备课程的平分分,不单是,不比格的那几门

图片 2

 

 小结:

#查出所有人的平均分
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
create table `ecs_mian2` (
    `user_name` varchar (20),
    `subject` varchar (20),
    `score` int (4)
); 
insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('张三','数学','90');
insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('张三','语文','50');
insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('张三','地理','40');
insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('李四','语文','55');
insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('李四','政治','45');
insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('王五','政治','30');
insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('赵六','物理','30');
insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('赵六','化学','50');
insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('赵六','语文','45');
insert into `ecs_mian2` (`user_name`, `subject`, `score`) values('赵六','数学','90');