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

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(DATE_FORMAT(start_time, '%Y-%m-%d'),':',tag) separator ';') as detail
 from exam_record
 left join
 examination_info
 on examination_info.exam_id = exam_record.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

连接两个表并且把限制条件先写好，having语句和order by 语句

if条件统计数量，group_concat函数的用法是新的知识点