题解 | #根据指定记录是否存在输出不同情况#

1. 使用with建立临时表，筛选出所有用户的等级，ID，以及对应的未完成题数和未完成率；然后通过where exists 和 where not exists 子句判断  等级为0未完成数大于2 的两种情况下的数据集合 ，由于这两个条件互斥，所以可以使用union all 进行关联取交集。

with t_tag_count as (
         select UI.uid ,
                UI.level ,
                count(start_time)-count(submit_time) incomplete_cnt ,
                count(start_time) exam_cnt,
                round(IFNULL(count(if(score is null,1,null))/count(start_time),0),3) incomplete_rate 
                from exam_record ER right join user_info UI using(uid) 
                group by UI.uid        
    )
     select uid,
            incomplete_cnt,
            incomplete_rate 
       from t_tag_count  
      where exists (
            select uid from t_tag_count where level = '0' and incomplete_cnt > 2
        ) and level = '0'  
        union all 
    select uid,
           incomplete_cnt,
           incomplete_rate 
       from t_tag_count  
      where not exists (
            select uid from t_tag_count where level = '0' and incomplete_cnt > 2
        ) and exam_cnt>0
        order by incomplete_rate ;