oj-sql

题目

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

思路

1. having筛选分组条件；
2. sum if筛选不同条件下的计数；
3. groupconcat拼接多个值到一个字段上；

solution

sum if groupconcat concat dateformat having

select
   uid,
   sum(if(submit_time is null,1,null)) incomplete_cnt,
   sum(if(submit_time is null,null,1)) complete_cnt,
   group_concat(distinct concat(date_format(start_time, '%Y-%m-%d'),':',tag) separator ';') detail
from exam_record re
left join examination_info info
on (re.exam_id=info.exam_id)
where year(re.start_time)=2021
group by uid
having incomplete_cnt between 2 and 4 and complete_cnt>=1
order by incomplete_cnt desc
;