题解 | #未完成试卷数大于1的有效用户#

SELECT
    uid,
    SUM(IF(submit_time IS NULL,1, 0)) AS incomplete_cnt,  
    SUM(IF(submit_time IS NOT NULL, 1, 0)) AS complete_cnt,
    GROUP_CONCAT(distinct concat_ws(':',date(start_time),tag) separator ';')AS detail
FROM
    exam_record AS er
LEFT JOIN
    examination_info AS ei USING (exam_id)
WHERE
    uid IN (
        SELECT uid
        FROM (
            SELECT uid, SUM(IF(submit_time IS NOT NULL, 1, 0)) AS cot,
            SUM(IF(submit_time IS NULL, 1, 0)) AS nocot
            FROM exam_record
            GROUP BY uid
            HAVING cot>=1 and nocot<5
        ) AS a
    )
and year(start_time)=2021
GROUP BY
    uid
having  incomplete_cnt>1
ORDER BY
    incomplete_cnt DESC
	写了半天终于写完了