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

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

这编辑器格式化代码在group_concat后面加了个空格就报错，找了半天才发现去了就能运行

向找出2021年完成试卷作答数至少为1且未完成数大于1小于5的用户，然后从中算出数据，注意外层也要限制时间