题解 | #筛选昵称规则和试卷规则的作答记录#

SELECT
    a.uid uid,
    er.exam_id exam_id,
    ROUND(avg(er.score), 0) avg_score
FROM
    exam_record er
    INNER JOIN (
        SELECT
            ui.uid
        FROM
            user_info ui
        WHERE
            nick_name REGEXP '^(牛客\\d+号|\\d+)$'
    ) a ON a.uid = er.uid
WHERE
    er.exam_id IN (
        SELECT
            exam_id
        FROM
            examination_info
        WHERE
            tag REGEXP '^[Cc].*'
    )
    AND er.submit_time IS NOT NULL
GROUP BY
    a.uid,
    er.exam_id
ORDER BY
    uid,
    avg_score