题解 | #未完成试卷数大于1的有效用户#主要是group_concat函数的用法
未完成试卷数大于1的有效用户
http://www.nowcoder.com/practice/46cb7a33f7204f3ba7f6536d2fc04286
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,
group_concat(distinct concat_ws(':',date_format(start_time,'%Y-%m-%d'),t2.tag ) separator ';') as detail
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
order by incomplete_cnt desc
