题解 | #未完成试卷数大于1的有效用户#题目说的不清不楚的，可以把两段和成一段
未完成试卷数大于1的有效用户
http://www.nowcoder.com/practice/46cb7a33f7204f3ba7f6536d2fc04286
select t1.uid,incomplete_cnt,complete_cnt,detail
from (
select
uid,
sum(if(submit_time is null,1,0)) as incomplete_cnt,
sum(if(submit_time is not null,1,0)) as complete_cnt
from exam_record t1
join examination_info t2
on t1.exam_id = t2.exam_id and year(start_time) = 2021
where uid in (select distinct uid from exam_record
where year(start_time) = 2021
group by uid
having sum(if(score is not null,1,0)) >= 1 and sum(if(score is null,1,0)) < 5)
group by uid
having sum(if(score is null,1,0)) > 1
) t1
join (
select uid,group_concat(d separator ';') as detail
from (
select distinct uid,concat_ws(':',date_format(start_time,'%Y-%m-%d'),t2.tag) as d,
date_format(start_time,'%Y-%m-%d') as start_time
from exam_record t1
join examination_info t2
on t1.exam_id = t2.exam_id and year(start_time) = 2021
order by start_time
) t
group by uid
)t2
on t1.uid = t2.uid
order by incomplete_cnt desc
