MYSQL窗口函数的应用

---

一、窗口函数应用于排名

with student_message(student_name, class_name, score) as (
    select 'sqlservercn', 'MYSQL', 95
    union all
    select 'tom', 'MYSQL', 99
    union all
    select 'mary', 'MYSQL', 98
    union all
    select 'jack', 'MYSQL', 99
    union all
    select 'tom', 'ORACLE', 99
    union all
    select 'mary', 'ORACLE', 99
    union all
    select 'jerry', 'ORACLE', 93
)
select student_name, class_name, score,
       row_number() over (partition by class_name order by score desc) as rw,
       rank() over (partition by class_name order by score desc) as rk,
       dense_rank() over (partition by class_name order by score desc) as drk
from student_message order by class_name, rw;

执行结果如下

二、查询出每一类课程的学习人数排名前3的课程、学习人数、排名、分类

with tmp as ( select title, study_cnt,
       rank() over (partition by b.type_id order by study_cnt desc) as rk, b.type_name
from imc_course a
join imc_type b on b.type_id = a.type_id)
select * from tmp where rk <= 3;

执行结果如下

三、每门课程学习人数占该类课程的学习总人数的比率

with tmp as (
select class_name, title, study_cnt,
       sum(study_cnt) over (partition by class_name) as class_total
from imc_course a
join imc_class b on b.class_id = a.class_id)
select class_name, title, concat(study_cnt/class_total*100, '%') as percentage from tmp order by class_name;

执行结果如下