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

with cte_m as
(select 
    distinct a.*,b.tag
from
    exam_record a
left join  examination_info b
on a.exam_id = b.exam_id
where substring(start_time,1,4)='2021')
select
    uid,
    count(start_time)-count(submit_time) as incomplete_cnt,
    count(submit_time) as cnt_d,
    group_concat(distinct concat_ws(':',date(start_time),tag) separator ';') as detail
from cte_m
group by uid
having cnt_d >=1 and incomplete_cnt <5 and  incomplete_cnt>1
order by incomplete_cnt desc;

先创建一个临时表cte_m，方便后续查询。接着对查询出有效用户uid=1002。这里用到了一个聚合函数group_concat(字段名 separator '分隔符')，对一个分组中的值进行组合。