题解 | #未完成试卷数大于1的有效用户#
未完成试卷数大于1的有效用户
https://www.nowcoder.com/practice/46cb7a33f7204f3ba7f6536d2fc04286
本题考察的点包括分组聚合、多表连接、字符串连接、子查询、if嵌套函数等知识点，内容较为综合，下面结合代码分享本题的完成思路以及完成过程中出现的问题：
select uid,count(incomplete) as incomplete_cnt,count(complete) as complete_cnt,
group_concat(distinct concat_ws(":",date(start_time),tag) separator ";") as detail // 使用分组连接字符串函数group_concat()获取 detail 字段，此函数需和group by函数一起使用
from (select uid,start_time,tag,
if(submit_time is null,1,null) incomplete,
if(submit_time is null,null,1) complete from exam_record t1 left join examination_info t2 on t1.exam_id = t2.exam_id
where year(start_time)=2021 ) as t // 将查询所需的字段生成一个新表，其中会用到left join函数、if嵌套函数
where uid in (select distinct uid from exam_record
group by uid
having count(if(submit_time is null,1,null)) < 5
and count(if(submit_time is null,null,1)) >= 1 ) // 子查询，将完成试卷作答数至少为1且未完成数小于5的有效用户筛选出来
group by uid
having incomplete_cnt > 1 // 筛选出未完成作答数大于1的有效用户
order by incomplete_cnt desc // 按未完数量降序排列
出现的问题：
group by + having函数不够熟练，导致最初完成时报错，错误代码如下：
select distinct uid from exam_record
count(if(submit_time is null,1,null)) < 5
and count(if(submit_time is null,null,1)) >= 1
group by uid
group_concat()、concat_ws()函数：https://blog.csdn.net/weihuan2323/article/details/123488376
