题解 | #未完成试卷数大于1的有效用户#
未完成试卷数大于1的有效用户
https://www.nowcoder.com/practice/46cb7a33f7204f3ba7f6536d2fc04286
select er.uid, #count(if (submit_time is null, er.submit_time, 0)) as incomplete_cnt,此处不对显示结果为4 #count(if (submit_time is not null, er.submit_time, 0)) as complete_cnt,此处不对显示结果为6 sum(if(submit_time is null,1,0)) as incomplete_cnt, #对提交的时间进行计数 sum(if(submit_time is not null,1,0)) as complete_cnt, #新的函数group_concat,将多列的内容连接起来 group_concat(distinct concat(date_format(er.start_time, '%Y-%m-%d'), ':', ei.tag) order by er.start_time separator ';') as detail from exam_record er left join examination_info ei on er.exam_id = ei.exam_id #计2021年每个未完成试卷作答数大于1的有效用户的数据 #(有效用户指完成试卷作答数至少为1且未完成数小于5), where year(er.start_time) = 2021 #group by ----having----先分组后计数 group by er.uid having incomplete_cnt > 1 and complete_cnt >= 1 and incomplete_cnt < 5 order by incomplete_cnt desc;