题解 | #未完成试卷数大于1的有效用户#
未完成试卷数大于1的有效用户
https://www.nowcoder.com/practice/46cb7a33f7204f3ba7f6536d2fc04286
select
er.uid,
count(
case when er.submit_time is null then 1 else null end ) as incomplete_cnt,
count(er.submit_time) as complete_cnt,
group_concat( distinct concat(date(start_time), ':', tag) separator ';') detil
from
exam_record er
left join examination_info ei on er.exam_id = ei.exam_id
where
er.uid in (select k.uid from (
select uid, count(case when submit_time is null then 1 else null end) as a,
count(submit_time) as b from exam_record where year(start_time) = '2021' group by uid
having a > 1 and a < 5 and b >= 1 ) k ) and year(er.start_time) = '2021'
group by r.uid order by incomplete_cnt desc
查看7道真题和解析