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

关键点

2021年——

• where year(start_time)=2021

未完成>1的有效用户（未完成<5+已完成>=1）

• 未完成——sum(if(submit_time is null,1,0))/count(if(submit_time is null,1,null))
• 已完成——sum(if(submit_time is not null,1,0))/count(if(submit_time is null,1,null))
• 由于是新定义变量，进行筛选时不可使用where而应使用having （where是提取数据前筛选，having是提取数据后筛选）

作答过的试卷tag集合，日期和tag间用“:”连接，多元素间用“;”连接

（例:2021-09-01:算法; 2021-07-02:SQL; 2021-09-02:SQL; 2021-09-05:SQL; 2021-07-05:SQL)

• 先将日期与tag相结合——concat(date_format(start_time,'%Y-%m-%d'),':',tag)
• 再将上述字段连接形成一个字段—— group_concat( DISTINCT str1 separator '分隔符') Order BY 排序字段 ASC/DESC

【血泪教训 order by 一定放在group_concat外面】

未通过答案：

select 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(date_format(start_time,'%Y-%m-%d'),':',tag)
             order by incomplete_cnt desc separator ';') as detail
   %%%%%%%%就是这里——【血泪教训 order by 一定放在group_concat外面】
from exam_record as er
join examination_info as ei
on er.exam_id=ei.exam_id
where year(start_time)=2021
group by uid
having complete_cnt>=1 and incomplete_cnt between 2 and 4

通过答案：

select 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(date_format(start_time,'%Y-%m-%d'),':',tag)
              separator ';') as detail
from exam_record as er
join examination_info as ei
on er.exam_id=ei.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

区别：把group_concat()内的order by挪到了最后

（我也不懂为什么就通过了）