需关注的地方在当有任意一个0级用户未完成试卷数大于2时输出每个0级用户的试卷未完成数和未完成率(保留3位小数)
with t1 as (select b.uid uid,sum(if(submit_time is null and start_time is not null,1,0)) cn,count(start_time) cnt from exam_record a right join user_info b on a.uid=b.uid where level=0 group by a.uid ) , t2 as (select uid,sum(if(submit_time is null,1,0)) cn,count(start_time) cnt from exam_record group by uid) select if((select max(cn) from t1)>2,t1.uid,t2.uid) uid,if((select max(cn) from t1)>2,t1.cn,t2.cn), round(ifnull(if((select max(cn) from t1)>2,t1.cn/t1.cnt,t2.cn/t2.cnt),0),3) rate from t1,t2 group by uid order by rate;