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

WITH 
valid_user AS 
(SELECT uid FROM user_info WHERE nick_name REGEXP '^牛客\\d+号$' OR nick_name REGEXP '^\\d+$'),
valid_exam AS
(SELECT exam_id FROM exam_record JOIN examination_info USING(exam_id) WHERE tag LIKE 'C%' OR tag LIKE '%c')

SELECT 
    uid,
    exam_id,
    ROUND(AVG(score)) AS avg_score
FROM 
    exam_record
WHERE 
    uid IN (SELECT uid FROM valid_user)
    AND exam_id IN (SELECT exam_id FROM valid_exam)
    AND score IS NOT NULL
GROUP BY 
    uid,
    exam_id
ORDER BY 
    uid,
    avg_score