SQL19 未完成试卷数大于1的有效用户

题目主要信息：

请统计2021年每个未完成试卷数大于1的有效用户的数据，输出用户ID、未完成试卷数、作答过的试卷集合，按未完成试卷数量由多到少排序
有效用户指完成试卷数至少为1且未完成数小于5
做过的试卷集合用{日期:tag}的形式，多元素之间用;连接

问题拆分：

选出2021年的uid、tag、开始时间、是否完成等信息：
- 作答时间在表exam_record，而题目tag又在表examination_info中，因此要先将其根据exam_id连接起来。知识点：join...on...
- 通过用if语句判断submit_time是否为空来显示完成、未完成信息，在完成中如果submit_time为空，则得到null，否则为1；在未完成中如果submit_time为空，得到1，否则得到空。知识点：if(判断，参数1，参数2)
- 选出年份等于2021年的数据 where year(start_time) = 2021。知识点：year()
从上面所选的信息中根据uid分组选出uid、完成试卷数、未完成试卷数、试卷详细信息：
- 按照uid分组。知识点：group by
- 根据上面筛选的完成、未完成的信息(只有1和null)，分别统计每个组的完成数时的1有多少，未完成数时的1有多少，得到每个uid的完成试卷数和未完成试卷数。知识点：count()
- 使用group_concat(distinct concat_ws(':', date(start_time), tag) separator ';')将每个组的不重复的开始做题的时间信息和题目的tag连接起来。知识点：group_concat、distinct、concat_ws、separator 其中group_concat是分组连接，concat_ws是使用分隔符将两个字符串连接
设置分组以后的判断条件，即完成数大于等于1，未完成数在2到5之间。知识点：having、between...and... having子句可以让我们筛选分组后的各组数据，而where要在分组前使用；
根据未完成试卷数降序输出。知识点：order by ... desc

代码：

select uid, 
       count(incomplete_num) as incomplete_cnt,
       count(complete_num) as complete_cnt,
       group_concat(distinct concat_ws(':', date(start_time), tag) separator ';') as detail
from(
    select uid, tag, start_time,
           if(submit_time is null, 1, null) as incomplete_num,
           if(submit_time is null, null, 1) as complete_num
    from exam_record join examination_info
    on exam_record.exam_id = examination_info.exam_id
    where year(start_time) = 2021
) as exam_complete_record
group by uid
having complete_cnt >= 1 and incomplete_cnt between 2 and 4
order by incomplete_cnt desc

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

SQL19 未完成试卷数大于1的有效用户

题目主要信息：

问题拆分：

代码：