题解 | #未完成试卷数大于1的有效用户#
未完成试卷数大于1的有效用户
http://www.nowcoder.com/practice/46cb7a33f7204f3ba7f6536d2fc04286
明确题意：
统计2021年每个未完成试卷作答数大于1的有效用户的数据（有效用户指完成试卷作答数至少为1且未完成数小于5），输出用户ID、未完成试卷作答数、完成试卷作答数、作答过的试卷tag集合，按未完成试卷数量由多到少排序
问题拆解：
- 本题主要是考察知识点：case when、group by、date_format、group_concat
- DATE_FORMAT(submit_time, "%Y%m" ) 返回的是202109这样；
-
语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )。说明：通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，缺省为一个逗号。
- t3对应的是 未完成试卷作答数大于1的有效用户的数据（有效用户指完成试卷作答数至少为1且未完成数小于5）
- t3需要与t4关联，得到uid的作答明细
-
t3需要与t5关联，得到tag
- 最后group by，用group_concat计算
代码实现：
select t3.uid, t3.incomplete_cnt, t3.complete_cnt, group_concat(distinct concat(date_format(t4.start_time,'%Y-%m-%d'),':',t5.tag) order by t4.start_time separator ';') as detail from ( select * from ( select * from ( select uid , count(case when submit_time is not null then 1 else null end) as complete_cnt , count(case when submit_time is null then 1 else null end) as incomplete_cnt from exam_record where year(start_time) = '2021' group by uid )t1 where complete_cnt >= 1 and incomplete_cnt < 5 )t2 where incomplete_cnt >1 -- complete_cnt >= 1 and incomplete_cnt >=2 and incomplete_cnt<=4 )t3 join exam_record t4 on t3.uid = t4.uid join examination_info t5 on t4.exam_id = t5.exam_id where year(t4.start_time) = '2021' -- 这里需要再次卡这个条件，因为join exam_record后可能会有2020年的记录。 group by t3.uid order by incomplete_cnt desc ;end
