未完成试卷数大于1的有效用户

select er.uid,
       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(distinct concat_ws(':',date(start_time),tag) separator ';') as detail
from exam_record er left join examination_info ei on er.exam_id = ei.exam_id where year(start_time)=2021
group by er.uid
having complete_cnt>=1 and incomplete_cnt<5 and incomplete_cnt>1
order by incomplete_cnt desc;

select 
    t.uid,t.incomplete_cnt,t.complete_cnt
    # 进行group_concat()的时候，发现有重复记录，因此做一个去重
    ,group_concat(distinct t.new_con separator ';') as detial
from 
(
    select 
        uid,tag,start_time
        # 两个sum()窗口函数可以计算出没要记录的未完成与完成题目数，方便后续条件筛选，比较清晰
        ,sum(if(submit_time is null,1,0))over(partition by uid) as incomplete_cnt 
        ,sum(if(submit_time is not null,1,0))over(partition by uid) as complete_cnt
        ,concat(substring(start_time,1,10),':',tag) as new_con
    from exam_record t1 
    left join examination_info t2 on t1.exam_id=t2.exam_id
    where year(start_time)='2021' 
    order by start_time
) t 
where t.incomplete_cnt >1 and t.incomplete_cnt <5 and t.complete_cnt >=1 
group by t.uid
order by t.incomplete_cnt desc

with t2 as (
select t.*,t1.tag from exam_record t 
    inner join examination_info t1 using(exam_id) where year(start_time)=2021 )



    select uid,count(submit_time  is null or null) as incomplete_cnt,count(submit_time ) as complete_cnt
    ,group_concat(distinct concat(date(start_time),':',tag) order by start_time  separator ";") as detail from t2 
    group by uid having complete_cnt>=1 and incomplete_cnt between 2 and 4
    order by complete_cnt

根据思路进行
首先想到可能表连接会多次进行，所以使用的with 临时表。

根据每个用户的情况，可以自然想到groupby uid,筛选条件都是聚合后的，所以自然想到 having。

条件1：有效用户，指完成试卷作答数至少为1且未完成数小于5 条件2：未完成试卷作答数大于1结合得 1``<未完成试卷答题数<``5 , 完成试卷作答数 >= 1
条件3：考试开始年限为2021年

主要是第三个group_concat 的使用。
group_concat( DISTINCT 要连接的字段Order BY ASC/DESC 排序字段]Separator '分隔符')

易错点：
1、这题不能用score去判断是否为null，不然提交的时候，有的人有成绩没提交时间
2、group_concat 需要使用distinct，因为有重复的
3、最后要排序
@苏打烟花无糖粉 的答案和解释，简洁有效、赞

select
uid,
(count(start_time))- (count(submit_time)) as incomplete_cnt,
count( submit_time) complete_cnt,
GROUP_CONCAT(distinct concat(date_format(start_time,"%Y-%m-%d"),":",tag)
             order by start_time 
            separator ";")
from exam_record t1
left join
examination_info t2
on t1.exam_id =t2.exam_id
where year(start_time)= 2021
group by uid
having complete_cnt >=1 and incomplete_cnt < 5 and 
incomplete_cnt > 1 
order by uid desc

select uid,
(count(*)-count(submit_time))as incomplete_cnt,
count(submit_time)as complete_cnt,
group_concat(distinct concat(date(start_time),':',tag) order by start_time asc separator ';')as detail
#结合使用group_concat函数和concat函数
from exam_record as er left join examination_info as ei on er.exam_id=ei.exam_id 
where year(start_time)=2021
group by uid
having count(submit_time)>=1 #完成试卷作答数至少为1，及大于等于1。缺少=号，会报错
and (count(*)-count(submit_time))<5 
and (count(*)-count(submit_time))>1
order by incomplete_cnt desc;

select e1.uid,sum(e1.submit_time is NULL) as incomplete_cnt,
sum(e1.submit_time is not null) as complete_cnt,
group_concat(distinct date_format(e1.start_time,'%Y-%m-%d'),':',e2.tag separator';')
from exam_record e1 left join examination_info e2
on e1.exam_id = e2.exam_id
where year(e1.start_time)=2021
group by e1.uid
having incomplete_cnt<5 and complete_cnt >=1 and incomplete_cnt>1
order by incomplete_cnt desc;

SELECT t1.*,group_concat(distinct d separator ';') detil
FROM 
    (select uid,
         count(*)-count(submit_time) incomplete_cnt,
         count(submit_time) complete_cnt
    from exam_record 
    where year(start_time)=2021
    group by uid
        ) t1
join 
    (select uid, concat(date(start_time),':',tag) d
    from exam_record join examination_info
    using (exam_id)
    where year(start_time)=2021
        ) t2
using (uid)
WHERE complete_cnt>=1 and incomplete_cnt between 2 and 4
group by uid
order by incomplete_cnt desc

SELECT uid,sum(if(submit_time is null,1,0)) incomplete_cnt,
COUNT(submit_time) complete_cnt,
GROUP_CONCAT(DISTINCT CONCAT(STR_TO_DATE(start_time,'%Y-%m-%d'),':',tag) separator ';') detail
FROM exam_record e_r
JOIN examination_info e_i ON e_r.exam_id=e_i.exam_id
WHERE YEAR(start_time)=2021
GROUP BY uid
HAVING incomplete_cnt>1 AND incomplete_cnt<5 AND complete_cnt>=1
ORDER BY incomplete_cnt DESC;

select
    uid,
    count(if(submit_time is null ,1,null)) as incomplete_cnt,
    count(submit_time) as complete_cnt ,
    replace(group_concat(distinct concat_ws(':',date_format(start_time,'%Y-%m-%d'),tag)),',',';' )as detail
from exam_record t1
join examination_info t2 on t1.exam_id = t2.exam_id
where year(start_time) = '2021'
group by uid
having complete_cnt > 1 and incomplete_cnt between 2 and 4
order by incomplete_cnt desc ;