拆解题目意思 统计2021年

where year(er.start_time)=2021

未完成试卷数

sum(case when er.submit_time is null then 1 else 0 end) incomplete_cnt

完成试卷数

sum(case when er.submit_time is not null then 1 else 0 end) complete_cnt

detail中是作答过的试卷{日期:tag}集合，日期和tag间用:连接，多元素间用;连接。 知识点：函数group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator'分隔符']

group_concat(distinct concat(date_format(er.start_time,'%Y-%m-%d'),':',ei.tag) order by start_time separator ';') detail

每个未完成试卷数大于1的有效用户的数据（有效用户指完成试卷数至少为1且未完成数小于5）

having complete_cnt>=1 and incomplete_cnt<5 and incomplete_cnt>1

再综上组合

select
er.uid
,sum(case when er.submit_time is null then 1 else 0 end) incomplete_cnt 
,sum(case when er.submit_time is not null then 1 else 0 end) complete_cnt 
,group_concat(distinct concat(date_format(er.start_time,'%Y-%m-%d'),':',ei.tag) order by start_time separator ';') detail
from exam_record er
left join examination_info ei
on er.exam_id=ei.exam_id
where year(er.start_time)=2021
group by er.uid
having complete_cnt>=1 and incomplete_cnt<5 and incomplete_cnt>1
order by 2 desc