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

1. group by 子句的灵活运用 。 


select uid,
        sum(incompleteN) incomplete_cnt,
        sum(completeN) complete_cnt,
        group_concat(distinct concat_ws(':',date_format(start_time,'%Y-%m-%d'),tag) separator ';') detail
        from (
 select  ER.uid,
         if(ER.submit_time is null,1,0) incompleteN,
         if(ER.submit_time is not null,1,0) completeN,
         EI.tag,
         ER.start_time  
   from  exam_record ER,
         examination_info EI 
  where  ER.exam_id = EI.exam_id 
    and  year(start_time) = 2021
) a group by a.uid 
  having incomplete_cnt < 5 and complete_cnt >= 1 and incomplete_cnt > 1 
  order by incomplete_cnt desc ;