难点在：如果不用子查询直接过滤，会过滤掉条件以外其他答题情况

SELECT
    uid,
    COUNT(DISTINCT r.id) as exam_cnt,
    COUNT(DISTINCT p.id) as question_cnt
FROM
    exam_record r
LEFT JOIN
    practice_record p USING(uid)
LEFT JOIN
    examination_info USING(exam_id)
LEFT JOIN
    user_info USING(uid)    
WHERE
    uid in (
        SELECT
            uid
        FROM
            exam_record r 
        LEFT JOIN
            practice_record p USING(uid)
        LEFT JOIN
            examination_info USING(exam_id)  
        LEFT JOIN
            user_info USING(uid)     
        WHERE
            tag = 'SQL' and difficulty = 'hard' and level = 7
        GROUP BY
            uid
        HAVING
            avg(r.score) > 80
) and left(r.submit_time,4) = '2021' and (left(p.submit_time,4) = '2021' or p.submit_time is null)
GROUP BY
    uid
ORDER BY
    exam_cnt, question_cnt DESC