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

渣渣来答题，搜索后发现需要一个group_concat函数，实现比别人的效率低多了啊……

-- 作答数至少1，未完成数2-4
-- detail去重:uid,date(start_time),tag
with tmp as(
select uid,sum(IF(submit_time is null,1,0))as incomplete_cnt, -- 这里蠢了，不应该提前统计的
sum(if(submit_time is null,0,1))as complete_cnt 
from exam_record 
join examination_info 
using(exam_id) where year(start_time)=2021 
group by uid
having  complete_cnt>=1 and incomplete_cnt BETWEEN 2 and 4)
select uid,incomplete_cnt,complete_cnt,
GROUP_CONCAT(distinct date(start_time),':',tag SEPARATOR ';')as detail 
from tmp 
join exam_record using(uid) 
join examination_info using(exam_id)
where year(start_time)=2021 
group by uid 
order by incomplete_cnt desc