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

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

学习到了GROUP_CONCAT(DISTINCT expression ORDER BY expression SEPARATOR sep) 的用法。

对此题，先将日期和tag组合，再利用此函数将结果连接起来，并用';'分割。