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

with
    tag as (
        select
            ui.uid uid,
            level,
            count(start_time) - count(submit_time) incomplete_cnt,
            round(
                ifnull ((1 - count(submit_time) / count(start_time)), 0),
                3
            ) incomplete_rate,
            count(start_time) total
        from
            test.user_info ui
            left join test.exam_record er on ui.uid = er.uid
        group by
            ui.uid,
            level
    )
select
    uid,
    incomplete_cnt,
    incomplete_rate
from
    tag
where
    exists (
        select
            *
        from
            tag
        where
            level = 0
            and incomplete_cnt > 2
    )
    and level = 0
union all
select
    uid,
    incomplete_cnt,
    incomplete_rate
from
    tag
where
    not exists (
        select
            *
        from
            tag
        where
            level = 0
            and incomplete_cnt > 2
    )
    and total > 0
order by
    incomplete_rate asc