题解 | #根据指定记录是否存在输出不同情况#

WITH
    user_info_tmp AS (
        SELECT
            ui.uid,
            ui.level level,
            SUM(
                IF (
                    er.submit_time is null
                    and start_time is not null,
                    1,
                    0
                )
            ) incomplete_cnt,
            ROUND(
                IFNULL (
                    SUM(
                        IF (
                            er.submit_time is null
                            and start_time is not null,
                            1,
                            0
                        )
                    ) / count(er.start_time),
                    0
                ),
                3
            ) incomplete_rate,
            count(er.start_time) has_strat
        FROM
            user_info ui
            LEFT JOIN exam_record er on er.uid = ui.uid
        GROUP BY
            ui.uid
    )
SELECT
    user_info_tmp.uid,
    user_info_tmp.incomplete_cnt,
    user_info_tmp.incomplete_rate
FROM
    user_info_tmp
WHERE
    EXISTS (
        SELECT
            1
        FROM
            user_info_tmp
        WHERE
            level = 0
            AND incomplete_cnt > 2
    )
    AND level = 0
UNION ALL
SELECT
    user_info_tmp.uid,
    user_info_tmp.incomplete_cnt,
    user_info_tmp.incomplete_rate
FROM
    user_info_tmp
WHERE
    NOT EXISTS (
        SELECT
            1
        FROM
            user_info_tmp
        WHERE
            level = 0
            AND incomplete_cnt > 2
    )
    and (has_strat <> 0)
ORDER BY
    incomplete_rate