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

SELECT uid, sum(case when submit_time is null then 1 else 0 end) incomplete_cnt,
sum(case when submit_time is not  null then 1 else 0 end) complete_cnt,
       group_concat(distinct concat_ws(':', date(start_time), tag) SEPARATOR ';') as detail
FROM (
         select uid, tag, start_time, submit_time
         from exam_record
                  left join examination_info using (exam_id)
         where year(start_time) = 2021
     ) t1
GROUP BY uid
HAVING 
complete_cnt >= 1
    AND incomplete_cnt < 5
    AND incomplete_cnt > 1
order by incomplete_cnt DESC

先选择需要的内容，再进行分组计算