欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-05-03 09:06:10
写一条SQL语句,求出2门以及2门以上不及格的科目平均分 >要出现2门以及2门以上的学科不及格 >计算该考生所有学科的平均分,不单是,不及格的那几门 #创建表: 思路一: 求出score < 60的科目数,然后统计平均分 这个结果是错误的,原因在于: " 用where过滤了score < 60的科目 ......

写一条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('张三','数学','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');

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

 

思路一:

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

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

这个结果是错误的,原因在于: " 用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,没有过滤任何一门学科的分数,所以能够统计到平均分

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

 

 小结:

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

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