SQL 练习题---一定要认真逐字逐句读题，拆分断句
一、题目：
请你找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬，统计他们的2021年试卷总完成次数和题目总练习次数，只保留2021年有试卷完成记录的用户。结果按试卷完成数升序，按题目练习数降序。
二、分析：
第一句：找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬
#筛选条件 ：筛选用where或having，有聚合函数avg出现用having
第二句：统计他们的2021年试卷总完成次数和题目总练习次数，只保留2021年有试卷完成记录的用户
#即第一句筛选出的用户，求这些用户的试卷总完成次数和题目总练习次数，立即想到聚合函数和group by 用户的联用，where筛选2021
第三句：结果按试卷完成数升序，按题目练习数降序。
#order by排序即可
三、程序拆解：
第一句：找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬
select uid from exam_record left join user_info using(uid) left join examination_info using(exam_id) where difficulty='hard' and tag='SQL' and level=7 group by uid having avg(score)>80;
第二句：统计他们的2021年试卷总完成次数和题目总练习次数，只保留2021年有试卷完成记录的用户
-- 2021年试卷总完成次数
select uid,count(exam_id) as exam_cnt
from exam_record
where year(submit_time)=2021
group by uid;
-- 2021年题目总练习次数
select uid,count(question_id) as question_cnt
from practice_record
where year(submit_time)=2021
group by uid;
合并表：
select uid,exam_cnt,if(question_cnt is null,0,question_cnt) as question_cnt from( select uid,count(exam_id) as exam_cnt from exam_record where year(submit_time)=2021 group by uid) as t1 left join( select uid,count(question_id) as question_cnt from practice_record where year(submit_time)=2021 group by uid) as t2 using(uid);
四、完整代码
select uid,exam_cnt,if(question_cnt is null ,0,question_cnt) as question_cnt from( (select uid,count(exam_id) as exam_cnt from exam_record where year(submit_time)=2021 group by uid) as t1 left join (select uid,count(question_id) as question_cnt from practice_record where year(submit_time)=2021 group by uid) as t2 using(uid) ) where uid in( select uid FROM user_info right join exam_record using(uid) left join examination_info using(exam_id) where tag='SQL' and difficulty='hard' and level=7 group by uid having avg(score)>80 ) order by exam_cnt asc,question_cnt DESC;
五、冲突
为什么先分开求2021年试卷总完成次数和题目总练习次数再合并表，而不是先合并表再求2021年试卷总完成次数和题目总练习次数？
六、附：
user_info表：
examination_info表：
practice_record表：
exam_record表：
drop table if exists examination_info,user_info,exam_record,practice_record; CREATE TABLE examination_info ( id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID', exam_id int UNIQUE NOT NULL COMMENT '试卷ID', tag varchar(32) COMMENT '类别标签', difficulty varchar(8) COMMENT '难度', duration int NOT NULL COMMENT '时长', release_time datetime COMMENT '发布时间' )CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE TABLE user_info ( id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID', uid int UNIQUE NOT NULL COMMENT '用户ID', `nick_name` varchar(64) COMMENT '昵称', achievement int COMMENT '成就值', level int COMMENT '用户等级', job varchar(32) COMMENT '职业方向', register_time datetime COMMENT '注册时间' )CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE TABLE practice_record ( id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID', uid int NOT NULL COMMENT '用户ID', question_id int NOT NULL COMMENT '题目ID', submit_time datetime COMMENT '提交时间', score tinyint COMMENT '得分' )CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE TABLE exam_record ( id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID', uid int NOT NULL COMMENT '用户ID', exam_id int NOT NULL COMMENT '试卷ID', start_time datetime NOT NULL COMMENT '开始时间', submit_time datetime COMMENT '提交时间', score tinyint COMMENT '得分' )CHARACTER SET utf8 COLLATE utf8_general_ci; INSERT INTO user_info(uid,`nick_name`,achievement,level,job,register_time) VALUES (1001, '牛客1号', 3100, 7, '算法', '2020-01-01 10:00:00'), (1002, '牛客2号', 2300, 7, '算法', '2020-01-01 10:00:00'), (1003, '牛客3号', 2500, 7, '算法', '2020-01-01 10:00:00'), (1004, '牛客4号', 1200, 5, '算法', '2020-01-01 10:00:00'), (1005, '牛客5号', 1600, 6, 'C++', '2020-01-01 10:00:00'), (1006, '牛客6号', 2000, 6, 'C++', '2020-01-01 10:00:00'); INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES (9001, 'SQL', 'hard', 60, '2021-09-01 06:00:00'), (9002, 'C++', 'hard', 60, '2021-09-01 06:00:00'), (9003, '算法', 'medium', 80, '2021-09-01 10:00:00'); INSERT INTO practice_record(uid,question_id,submit_time,score) VALUES (1001, 8001, '2021-08-02 11:41:01', 60), (1002, 8001, '2021-09-02 19:30:01', 50), (1002, 8001, '2021-09-02 19:20:01', 70), (1002, 8002, '2021-09-02 19:38:01', 70), (1004, 8001, '2021-08-02 19:38:01', 70), (1004, 8002, '2021-08-02 19:48:01', 90), (1001, 8002, '2021-08-02 19:38:01', 70), (1004, 8002, '2021-08-02 19:48:01', 90), (1004, 8002, '2021-08-02 19:58:01', 94), (1004, 8003, '2021-08-02 19:38:01', 70), (1004, 8003, '2021-08-02 19:48:01', 90), (1004, 8003, '2021-08-01 19:38:01', 80); INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES (1001, 9001, '2021-09-01 09:01:01', '2021-09-01 09:31:00', 81), (1002, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 81), (1003, 9001, '2021-09-01 19:01:01', '2021-09-01 19:40:01', 86), (1003, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:51', 89), (1004, 9001, '2021-09-01 19:01:01', '2021-09-01 19:30:01', 85), (1005, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:02', 85), (1006, 9003, '2021-09-07 10:01:01', '2021-09-07 10:21:01', 84), (1006, 9001, '2021-09-07 10:01:01', '2021-09-07 10:21:01', 80);
