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

 请统计2021年每个未完成试卷作答数大于1的有效用户的数据（有效用户指完成试卷作答数至少为1且未完成数小于5），输出用户ID、未完成试卷作答数、完成试卷作答数、作答过的试卷tag集合，按未完成试卷数量由多到少排序。

一、知识点

1、substring

substring（str, pos）:        substring（被截取字段，从第几位开始截取）

substring（str, pos, length）:        substring（被截取字段，从第几位开始截取，截取长度）

2、concat

concat(str1,str2,...) 

SELECT CONCAT('My', 'S', 'QL')
#结果是MySQL

concat_ws(‘拼接符’,str1,str2,...) 

concat_ws(‘_’,My,S,Q,L) 
#结果是My_S_Q_L

group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'])

将多行合并成一行

---

二、题目分析

1、条件

2021年每个未完成试卷作答数大于1的有效用户的数据（有效用户指完成试卷作答数至少为1且未完成数小于5）

2021用where:

where year(start_time)=2021

未完成试卷做答数及完成试卷作答数用sum函数统计：

sum(if(submit_time is null,1,0)) as incomplete_cnt,
sum(if(submit_time is null,0,1)) as complete_cnt

未完成试卷作答数大于1用having筛选：

having incomplete_cnt>1

有效用户用having筛选：

having complete_cnt>=1 and incomplete_cnt<5

2、输出结果

detail中是1002作答过的试卷{日期:tag}集合，日期和tag间用:连接，多元素间用;连接

group_concat(distinct concat_ws(':',substring(start_time,1,10),tag) separator ';')

3、完整代码

select uid,
sum(if(submit_time is null,1,0)) as incomplete_cnt,
sum(if(submit_time is null,0,1)) as complete_cnt,
group_concat(distinct concat_ws(':',substring(start_time,1,10),tag) separator ';') as detail
from exam_record as a
join examination_info as b on a.exam_id=b.exam_id
where year(start_time)=2021
group by uid
having complete_cnt>=1 and incomplete_cnt<5 and incomplete_cnt>1
order by incomplete_cnt desc

欢迎探讨