一道很好的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('張三','數學','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');

思路一：

求出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，表中的欄位，可以認為是變數，變數當然可以計算，比較，調用函數等