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

这道题很综合，有几个关键点

第一点：concat_ws将 日期和tag连接

第二点: group_concat,注意要distinct

select uid,
         sum(CASE WHEN submit_time is null then 1 else 0 end) as incomplete_cnt,
       count(submit_time) as complete_cnt,
       group_concat(DISTINCT detail separator ';')
FROM
    (SELECT uid, e1.exam_id, start_time,
    submit_time,score,tag,
    concat_ws(':',DATE_FORMAT(start_time,'%Y-%m-%d'),tag) as detail
    FROM exam_record e1
    LEFT JOIN examination_info e2
    ON e1.exam_id = e2.exam_id
    where year(start_time)='2021')a
GROUP BY uid
having count(submit_time) >=1
and sum(CASE WHEN submit_time is null then 1 else 0 end) between 2 and 4