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

/*
* 建立新表，记录用户试卷未完成数和未完成率
*/
WITH incomplete_t AS (
    SELECT uid, level,
        COUNT(IF(submit_time IS NULL, exam_id, NULL)) AS incomplete_cnt,
        ROUND(
            IFNULL(1 - COUNT(submit_time) / COUNT(start_time), 0), 3 
        ) AS incomplete_rate,
        COUNT(start_time) AS total_cnt
    FROM user_info
        LEFT JOIN exam_record
        USING(uid)
    GROUP BY uid
)

SELECT uid, incomplete_cnt, incomplete_rate
FROM incomplete_t
WHERE EXISTS (
    SELECT uid FROM incomplete_t WHERE incomplete_cnt > 2 AND level = 0 
) AND level = 0
UNION ALL
SELECT uid, incomplete_cnt, incomplete_rate
FROM incomplete_t
WHERE NOT EXISTS (
    SELECT uid FROM incomplete_t WHERE incomplete_cnt > 2 AND level = 0
) AND total_cnt > 0
ORDER BY incomplete_rate