### 问题分解：

• 找出高难度SQL试卷得分平均值大于80分的7级用户，生成子表t_user_id：
• 筛选出目标用户：WHERE difficulty='hard' and tag='SQL' and `level`=7
• 按用户分组：GROUP BY uid
• 筛选平均分大于80的分组（用户）：HAVING AVG(score)>80
• 统计每个用户2021年的试卷完成数，生成子表t_exam_cnt：
• 筛选作答时间和已完成：WHERE YEAR(submit_time)=2021 AND submit_time IS NOT NULL
• 按用户分组：GROUP BY uid
• 统计试卷完成数：count(exam_id) as exam_cnt
• 统计每个用户2021年的试题作答数，生成子表t_question_cnt：
• 筛选作答时间：WHERE YEAR(submit_time)=2021
• 按用户分组：GROUP BY uid
• 统计试题作答数：count(question_id) as question_cnt
• 关联三张表：
• 内连接t_user_id和t_exam_cnt，因为用户属性条件必须满足，题目又要求了只输出有试卷完成记录的用户：t_user_id JOIN t_exam_cnt USING(uid)
• 左连接t_question_cnt：因为只要满足上面条件的结果，即使无试题作答记录也输出：LEFT JOIN t_question_cnt USING(uid)
• 特殊处理试题作答数可能为0的情况（此时关联结果中试题作答数为NULL）：IFNULL(question_cnt, 0) as question_cnt

### 细节问题：

• 表头重命名：as
• 按试卷完成数升序、题目练习数降序：ORDER BY exam_cnt, question_cnt desc

### 完整代码：

``````SELECT uid, exam_cnt, IFNULL(question_cnt, 0) as question_cnt
FROM (
SELECT uid
FROM exam_record
JOIN examination_info USING(exam_id)
WHERE difficulty='hard' and tag='SQL' and `level`=7
GROUP BY uid
HAVING AVG(score)>80
) as t_user_id
JOIN (
SELECT uid, count(exam_id) as exam_cnt
FROM exam_record
WHERE YEAR(submit_time)=2021 AND submit_time IS NOT NULL
GROUP BY uid
) as t_exam_cnt
USING(uid)
LEFT JOIN (
SELECT uid, count(question_id) as question_cnt
FROM practice_record WHERE YEAR(submit_time)=2021
GROUP BY uid
) as t_question_cnt
USING(uid)
ORDER BY exam_cnt, question_cnt desc;
``````