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

首先建一个新表temp，连接exam_record和examination_info提取出uid等所需字段，用concat_ws连接start_time和tag两字段作为sub_detail字段，start_time要format成%Y-%m-%d形式。这里要注意的是，题目中要的是所有作答过的试卷信息而不是完成了的试卷信息。

with temp as 
(select er.uid, er.start_time, er.submit_time,
 concat_ws( ":", 
           date_format(er.start_time,"%Y-%m-%d"), 
           ei.tag) as sub_detail
from exam_record as er, examination_info as ei
where er.exam_id = ei.exam_id and year(er.start_time) = 2021)

接下来根据uid将temp表分组，用count计算complete_cnt和incomplete_cnt，并用group_concat将每组的所有sub_detail连接起来，用“;”作分隔，并按照sub_detail顺序排序。最后用having对条件进行限制，不用where是因为where里不能有聚合函数最后按照incomplete_cnt倒序排序。

select 
uid,
count(*) - count(submit_time) as incomplete_cnt,
count(submit_time) as complete_cnt,
group_concat(distinct sub_detail order by sub_detail separator ";" ) as detail
from temp
group by uid
having incomplete_cnt<5 and incomplete_cnt>1 and complete_cnt>=1

order by incomplete_cnt desc

完整如下

with temp as 
(select er.uid, er.start_time, er.submit_time,
 concat_ws( ":", 
           date_format(er.start_time,"%Y-%m-%d"), 
           ei.tag) as sub_detail
from exam_record as er, examination_info as ei
where er.exam_id = ei.exam_id and year(er.start_time) = 2021)

select 
uid,
count(*) - count(submit_time) as incomplete_cnt,
count(submit_time) as complete_cnt,
group_concat(distinct sub_detail order by sub_detail separator ";" ) as detail
from temp
group by uid
having incomplete_cnt<5 and incomplete_cnt>1 and complete_cnt>=1

order by incomplete_cnt desc