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

WITH t1 as(
SELECT ui.uid,
SUM(IF(submit_time IS NULL,1,0)) AS incomplete_cnt,
COUNT(start_time) AS total_cnt
FROM user_info AS ui
JOIN exam_record AS er
ON ui.uid = er.uid
GROUP BY ui.uid
),
t2 as(
SELECT ui.uid,level,
IF(incomplete_cnt IS NULL,0,incomplete_cnt) AS incomplete_cnt,
IF(total_cnt IS NULL,0,total_cnt) AS total_cnt
FROM user_info AS ui
LEFT OUTER JOIN t1 ON ui.uid = t1.uid
),
t3 as(
SELECT uid,level,incomplete_cnt,total_cnt,
IF(incomplete_cnt = 0,0,ROUND(incomplete_cnt / total_cnt,3)) AS incomplete_rate
FROM t2
)
SELECT uid,incomplete_cnt,incomplete_rate
FROM t3
WHERE EXISTS(SELECT uid FROM t3 WHERE incomplete_cnt > 2 AND level = 0) AND level = 0
UNION ALL
SELECT uid,incomplete_cnt,incomplete_rate
FROM t3
WHERE NOT EXISTS(SELECT uid FROM t3 WHERE incomplete_cnt > 2 AND level = 0) AND total_cnt > 0
ORDER BY incomplete_rate ASC;