题解 | #未完成试卷数大于1的有效用户#{重点}
未完成试卷数大于1的有效用户
https://www.nowcoder.com/practice/46cb7a33f7204f3ba7f6536d2fc04286
select uid,
count(case when score is null then 1 end) incomplete_cnt,
count(case when score is not null then 1 end) complete_cnt,
group_concat(distinct concat_ws(':',DATE_FORMAT(start_time,'%Y-%m-%d'),tag) separator ';') detail
from exam_record a
left join examination_info b
on a.exam_id=b.exam_id
where YEAR(start_time)=2021
group by uid
having count(case when score is not null then 1 end)>=1
and count(case when score is null then 1 end)<5
and count(case when score is null then 1 end)>1
order by count(case when score is not null then 1 end) desc
count(case when score is null then 1 end) incomplete_cnt,
count(case when score is not null then 1 end) complete_cnt,
group_concat(distinct concat_ws(':',DATE_FORMAT(start_time,'%Y-%m-%d'),tag) separator ';') detail
from exam_record a
left join examination_info b
on a.exam_id=b.exam_id
where YEAR(start_time)=2021
group by uid
having count(case when score is not null then 1 end)>=1
and count(case when score is null then 1 end)<5
and count(case when score is null then 1 end)>1
order by count(case when score is not null then 1 end) desc
Group concat用法
Group_concat(distinct c order by desc separator ‘:’)
concat_ws 用法
写一次分隔符号，完成全部
concat_ws(‘:’,tag,tag2,...n)
第一个是操作符号，后面是需要链接的各个部分
