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

#sql的关键是读题，错了好多次才发现对年份有要求，
#其次是这题的分组拼接，不能输出重复数据，输出要按照时间顺序，题干未作说明
#count只负责计数，但是会跳过null，如果if的F不设置为null，本题就错了，不过使用sum的话
#将if设置为1，0完全可以
select uid,count(if(submit_time is null, 1,null)) as incomplete_cnt,count(if(submit_time is not null, 1,null)) as complete_cnt,GROUP_CONCAT(distinct CONCAT(DATE(start_time), ':', tag) order by start_time  SEPARATOR ';') AS detail
from  exam_record ee
###count是计数，会忽略null，只要非空就会计数,
#如果是sum，则要将if的null改成0
inner join examination_info as e on e.exam_id = ee.exam_id
where year(start_time)=2021
group by uid
having count(if(submit_time is not null, 1,null))>=1 
and count(if(submit_time is null, 1,null)) <5 
and count(if(submit_time is null, 1,null)) >1
order by incomplete_cnt desc