10行代码搞定
with cte as(
select count(start_time)-count(submit_time) as non_0_num
from exam_record
where uid in (select distinct uid from user_info where level=0) )
#计算出0级用户未完成的试卷量
select if((select* from cte)>2,uid in (select distinct uid from user_info where level=0),uid) as uid,
#if(expr,v1,v2)函数,v1中加入uid范围条件,注意不要直接引用cte,然后就是基操了
count(start_time)-count(submit_time) as non_num,
round((count(start_time)-count(submit_time))/count(start_time),3) as non_rate
from exam_record
group by uid
order by non_rate;