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

SELECT  uid, sum(if(submit_time IS NULL, 1, 0)) incomplete_cnt, count(submit_time) complete_cnt,  group_concat(DISTINCT date_format(start_time,'%Y-%m-%d'),':',tag SEPARATOR ';') detail FROM  exam_record e JOIN examination_info ei ON e.exam_id = ei.exam_id WHERE  YEAR(start_time) = 2021 GROUP BY  uid HAVING  # 筛选有效用户  COUNT(submit_time) >= 1 #   AND count(*) - count(submit_time) < 5;  AND SUM(IF(submit_time IS NULL, 1, 0)) < 5 AND sum(if(submit_time IS NULL, 1, 0)) > 1 ORDER BY incomplete_cnt DESC ;