题解 | #注册当天就完成了试卷的名单第三页#

SELECT
    user_info.uid,
    user_info.level,
    user_info.register_time,
    sb.max_score
FROM
    user_info
    LEFT JOIN (
        SELECT
            max(score) max_score,
            uid
        from
            exam_record
        GROUP BY
            uid
    ) sb on sb.uid = user_info.uid
WHERE
    user_info.job = '算法'
    AND EXISTS (
        SELECT
            *
        FROM
            exam_record
        WHERE
            submit_time IS NOT NULL
            AND DATE_FORMAT (exam_record.submit_time, '%Y%m%d') = DATE_FORMAT (user_info.register_time, '%Y%m%d')
            AND exam_record.uid = user_info.uid
            AND exam_id IN (
                SELECT
                    exam_id
                FROM
                    examination_info
                WHERE
                    tag = '算法'
            )
    )
ORDER BY
    sb.max_score DESC
LIMIT
    6, 3