题解 | #满足条件的用户的试卷完成数和题目练习数#

SELECT
    a.uid,
    IFNULL (a.exam_cnt, 0) exam_cnt,
    IFNULL (b.question_cnt, 0) question_cnt
FROM
    (
        SELECT
            uid,
            count(*) exam_cnt
        FROM
            exam_record er
        WHERE
            er.uid in (
                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 DISTINCT
                            uid
                        FROM
                            user_info
                        WHERE
                            level = '7'
                    )
                GROUP BY
                    uid
                HAVING
                    AVG(score) > 80
            )
            AND YEAR (er.submit_time) = '2021'
        GROUP BY
            uid
    ) a
    LEFT JOIN (
        SELECT
            uid,
            count(*) question_cnt
        FROM
            practice_record pr
        WHERE
            pr.uid in (
                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 DISTINCT
                            uid
                        FROM
                            user_info
                        WHERE
                            level = '7'
                    )
                GROUP BY
                    uid
                HAVING
                    AVG(score) > 80
            )
            AND YEAR (pr.submit_time) = '2021'
        GROUP BY
            uid
    ) b on b.uid = a.uid
ORDER BY
    exam_cnt asc,
    question_cnt desc