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

【场景】：分组后行拼接为一列

【分类】：分组查询、group_concat

分析思路

难点：

1.如何按照分组拼接？

使用group_concat

一、如何按照分组拼接

先使用concat或者concat_ws，将日期和tag拼接在一起，并加上分隔符’：‘

• [使用]：concat(date_format(start_time,'%Y-%m-%d'),':',tag)
• [使用]：concat_ws(':',date_format(start_time,'%Y-%m-%d'),tag)

再按照日期和tag拼接好的列分组拼接为一列，并加上分隔符‘；’

• [使用]：group by course_id,course_name

二、完成试卷作答数至少为1且未完成数小于5

放在having中做处理

• [使用]：having ((count(a.exam_id)-count(submit_time)) < 5 and count(submit_time) >= 1 and (count(a.exam_id)-count(submit_time)) > 1)

扩展

前往查看：MySQL 字符串拼接

求解代码

使用 group_concat concat

select
    a.uid,
    count(a.exam_id)-count(submit_time) as incomplete_cnt,
    count(submit_time) as complete_cnt,
    group_concat(distinct concat(date_format(start_time,'%Y-%m-%d'),':',tag) order by start_time Separator ';') as detail
from exam_record a,examination_info b
where b.exam_id = a.exam_id
and year(start_time) = 2021
group by uid 
#完成试卷作答数至少为1且未完成数小于5
having ((count(a.exam_id)-count(submit_time)) < 5 and count(submit_time) >= 1 and (count(a.exam_id)-count(submit_time)) > 1)
order by incomplete_cnt desc

方法二：

使用 group_concat concat_ws

select
    a.uid,
    count(a.exam_id)-count(submit_time) as incomplete_cnt,
    count(submit_time) as complete_cnt,
    group_concat(distinct concat_ws(':',date_format(start_time,'%Y-%m-%d'),tag) order by start_time Separator ';') as detail
from exam_record a,examination_info b
where b.exam_id = a.exam_id
and year(start_time) = 2021
group by uid 
#完成试卷作答数至少为1且未完成数小于5
having ((count(a.exam_id)-count(submit_time)) < 5 and count(submit_time) >= 1 and (count(a.exam_id)-count(submit_time)) > 1)
order by incomplete_cnt desc