题解 | #各用户等级的不同得分表现占比#

select 
c.level level,
c.score_grade score_grade,
format(count(1)/d.lev_count,3) ratio
from 
(
    select
b.uid,
b.exam_id,
b.score,
a.level,
case when b.score<60 then '差'
when b.score>=60 and b.score<75 then '中'
when b.score>=75 and b.score<90 then '良'
else  '优' end score_grade
from 
exam_record b
left join user_info a
on a.uid=b.uid
where b.submit_time is not null
) c,
(
select
a.level,
count(1) lev_count
from 
exam_record b
left join user_info a
on a.uid=b.uid
where b.submit_time is not null
group by a.level
) d
where c.level = d.level
group by c.level,c.score_grade
order by level desc,ratio desc