解体解答SQL150

第一步，判断每个分数所属区间

            select u1.uid,level,exam_id,score,case when score >= 90 then '优'
                                                        when score < 90 && score >= 75 then '良'
                                                        when score < 75 && score >= 60 then '中'
                                                        else '差' end score_grade
                    from user_info u1 join exam_record e1
                    on u1.uid = e1.uid
                    where score is not null 

第二步，求得每个level,每个score_grade相应的数量

        select level,score_grade,count(*) ct from (
            select u1.uid,level,exam_id,score,case when score >= 90 then '优'
                                                        when score < 90 && score >= 75 then '良'
                                                        when score < 75 && score >= 60 then '中'
                                                        else '差' end score_grade
                    from user_info u1 join exam_record e1
                    on u1.uid = e1.uid
                    where score is not null 
        )t1
        group by level,score_grade

第三步，求得每个level一共多少数量

    select level,score_grade,ct,sum(ct) over(partition by level) sum_ct from(
        select level,score_grade,count(*) ct from (
            select u1.uid,level,exam_id,score,case when score >= 90 then '优'
                                                        when score < 90 && score >= 75 then '良'
                                                        when score < 75 && score >= 60 then '中'
                                                        else '差' end score_grade
                    from user_info u1 join exam_record e1
                    on u1.uid = e1.uid
                    where score is not null 
        )t1
        group by level,score_grade
    )t2

第四步，用第二步求得数量除第三步的数量

select level,score_grade,round(ct/sum_ct,3) ratio from(
    select level,score_grade,ct,sum(ct) over(partition by level) sum_ct from(
        select level,score_grade,count(*) ct from (
            select u1.uid,level,exam_id,score,case when score >= 90 then '优'
                                                        when score < 90 && score >= 75 then '良'
                                                        when score < 75 && score >= 60 then '中'
                                                        else '差' end score_grade
                    from user_info u1 join exam_record e1
                    on u1.uid = e1.uid
                    where score is not null 
        )t1
        group by level,score_grade
    )t2
)t3
order by level desc ,ratio desc