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

select uid,sum(if(submit_time is null,1,0)) as incomplete_cnt,sum(if(submit_time is null,0,1)) as complete_cnt,group_concat(distinct concat_ws(':',substring(start_time,1,10),tag) separator ';') as detail
from exam_record as a
join examination_info as b on a.exam_id=b.exam_id
where year(start_time)=2021
group by uid
having complete_cnt>=1 and incomplete_cnt<5 and incomplete_cnt>1
order by incomplete_cnt desc

#where year(start_time)=2021
#未完成试卷做答数：
#sum(if(submit_time=null,1,0))
#完成试卷作答数：
#sum(if(submit_time=null,0,1))
#有效用户：
#having complete_cnt>=1 and incomplete_cnt<5
#未完成试卷作答数大于1：
#having incomplete_cnt>1
#group_concat(distinct concat_ws(':',substring(start_time,1,10),tag) separator ';')