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

SELECT 
	uid, 
	count(incomplete) as incomplete_cnt,
    count(complete) as complete_cnt,
    group_concat(distinct concat_ws(':', date(start_time), tag) SEPARATOR ';') as detail
from (
    SELECT 
		uid, 
		tag, 
		start_time,
        if(submit_time is null, 1, null) as incomplete,
        if(submit_time is null, null, 1) as complete
    from exam_record 
    left join examination_info using(exam_id)
    where year(start_time)=2021
) as exam_complete_rec
group by uid
having complete_cnt >= 1 
and incomplete_cnt BETWEEN 2 and 4
order by incomplete_cnt DESC;