一、知识点总结与拓展

1、左，右，内，外全连接：
左连接：表1 left join 表2 on 表1.字段=表2.字段  （以表1为准，表2进行匹配）
右连接：表1 right join 表2 on 表1.字段=表2.字段  （以表2为准，表1进行匹配）
全连接：表1 union all 表2 （表1 和表2的列数必须一样多，union 去除重复项，union all 不剔除重复项）
内连接：表1 inner join 表2（取表1和表2相交部分）
外连接：表1 full outer join 表2 （取表1和表2不相交的部分）
ps：MYSQL 不支持外连接，可以用左右连接后再全连接代替

2、时间函数 year（）

a.常用的截取时间的函数有：year（），month（），day（）
b.常用的时间差函数：timestampdiff(时间格式，开始时间，结束时间)
eg.timestampdiff (minute,start_time,submit_time)<5

3、条件函数 ifnull（字段名，0）如果字段名为空，返回0，否则返回原字段值
4、group by 函数

a.group by的常见搭配：常和以下聚合函数搭配 

    avg（）-- 求平均值
    count（）-- 计数
    sum（）-- 求和
    max（） -- 最大值
    min（）-- 最小值

b.group by 的进阶用法，和with rollup一起使用。

二、题目总结与步骤拆分

examination_info,user_info,exam_record,practice_record四张表
满足条件的7级用户在2021年试卷的总完成次数和题目总练习次数。

STEP1：先找出满足条件的用户
        条件1：高难度SQL试卷得分平均值大于80
        条件2：七级用户

STEP2:统计以上用户2021年的完成试卷数和练习题目数。


三、步骤代码

1）先看STEP1:寻找满足条件的用户“高难度SQL试卷得分平均值大于80分的7级用户” 
SELECT uid FROM exam_record 
WHERE exam_id IN(SELECT exam_id FROM examination_info WHERE difficulty='hard' AND tag='SQL') -- 高难度SQL试卷 AND uid IN (SELECT uid FROM user_info WHERE level=7) -- 七级用户 GROUP BY uid
HAVING AVG(score)>80; -- 得分平均值大于80
2）再看STEP2：统计以上用户2021年的完成试卷数和练习题目数
因为试卷完成情况和练习题完成情况位于两张不同的表，先分别看用户的试卷完成数和练习题完成数。

试卷完成数 
SELECT uid,COUNT(exam_id) exam_cnt FROM exam_record
WHERE year(submit_time)=2021 -- 2021年的数据
GROUP BY uid;
练习题完成数 
SELECT uid,COUNT(question_id) question_cnt FROM  practice_record
WHERE year(submit_time)=2021 -- 2021年的数据
GROUP BY uid;

四、完整代码组装

以uid为关键字段，通过表连接来对数据进行组装和筛选。 
SELECT c.uid,ifnull(a.exam_cnt,0) ex_cnt,ifnull(b.question_cnt,0) qu_cnt  -- ifnull(字段名，0) 如果字段名为空，返回0，否则返回原字段值
FROM (SELECT uid,COUNT(exam_id) exam_cnt FROM  exam_record
	WHERE year(submit_time)=2021
	GROUP BY uid) AS a

LEFT JOIN (SELECT uid,COUNT(question_id) question_cnt FROM  practice_record
	WHERE year(submit_time)=2021 -- year(submit_time) 截取时间的年份数据
	GROUP BY uid) AS b ON a.uid=b.uid

RIGHT JOIN (SELECT uid  FROM exam_record 
	WHERE exam_id IN(SELECT exam_id FROM examination_info WHERE difficulty='hard' AND tag='SQL')
	AND uid IN (SELECT uid FROM user_info WHERE level=7)
	GROUP BY uid
	HAVING AVG(score)>80) AS c ON a.uid=c.uid

ORDER BY ex_cnt ASC ,qu_cnt DESC
;