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

select uid, 
sum(if(submit_time is null,1,null)) as incomplete_cnt,
sum(if(submit_time is not null,1,null)) as complete_cnt,
group_concat(distinct concat_ws(':',date(start_time),tag) order by start_time separator';') as detail
from exam_record as er
left join examination_info as ei on er.exam_id=ei.exam_id
where year(start_time) = 2021
group by uid
having complete_cnt >=1 and incomplete_cnt between 2 and 4
order by incomplete_cnt desc

问题分解：

• 关联作答记录和试卷信息：left join examination_info as ei on er.exam_id=ei.exam_id；
• 按用户分组：group by uid
• 统计未完成试卷作答数和已完成试卷作答数：sum(if(submit_time is null,1,null)) as incomplete_cnt
• 统计完成试卷作答数和已完成试卷作答数：sum(if(submit_time is not null,1,null)) as complete_cnt
• 统计作答过的tag集合：对于每条作答tag，用:连接日期和tag：concat_ws(':', date(start_time), tag)对于一个人（组内）的多条作答，用;连接去重后的作答记录：group_concat(distinct concat_ws(':',date(start_time),tag) order by start_time separator';')
• 筛选未完成试卷作答数大于1的有效用户：having complete_cnt >=1 and incomplete_cnt between 2 and 4

细节问题：