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

with t1 as (
select level,
case when score>=90 then '优' 
     when score>=75 then '良'
     when score>=60 then '中'
     else '差' end as score_grade
from(
select level,score
from user_info
inner join exam_record using(uid)
where score is not null
)t
),
t2 as 
(
    select level,count(*) as lvsm
    from t1 
    group by level
)
select level,score_grade,round(sum(1/lvsm),3) as ratio
#或者select level,score_grade,round(count(*)/lvsm,3) as ratio
from t1
inner join t2 using(`level`)
group by level,score_grade
order by level desc,ratio desc