-- 练习（得昂）：查询每门课程的前三名：课程，第一名（姓名+分数），第二名（姓名+分数），第三名（姓名+分数）

create table t_stu_profile(

stu_id varchar(10) primary key,

stu_name varchar(10),

gender varchar(10),

age int,

class_id varchar(10) not null

);

create table t_lesson(

lesson_id varchar(10) primary key,

lesson_name varchar(10)

);

create table t_score(

stu_id varchar(10) references t_stu_profile(stu_id),

lesson_id varchar(10) references t_lesson(lesson_id),

score int

);

insert into t_stu_profile values

('001','郭东','F',16,'0611'),

('002','李西','M',18,'0612'),

('003','张北','F',16,'0613'),

('004','钱南','M',17,'0611'),

('005','王五','M',17,'0611'),

('006','赵七','F',16,'0615');

insert into t_lesson values

('L001','语文'),

('L002','数学'),

('L003','英语'),

('L004','物理'),

('L005','化学');

insert into t_score values

('001','L001',90),

('001','L002',86),

('002','L001',84),

('002','L004',75),

('003','L003',85),

('004','L005',98),

('004','L001',90),

('001','L003',88),

('001','L004',85),

('002','L002',83),

('002','L003',71),

('003','L001',82),

('004','L002',99),

('004','L003',89);

select * from t_stu_profile;

select * from t_lesson;

select * from t_score;

#方法一ZWT

##准备子查询数据

select SCO.stu_id as stu_id,SCO.lesson_id as lesson_id,score,stu_name,lesson_name

FROM t_score SCO LEFT JOIN t_stu_profile STU

ON SCO.stu_id=STU.stu_id

left join t_lesson LES on SCO.lesson_id=LES.lesson_id ;

##排名

SELECT *,dense_rank() over(partition by t1.lesson_id order by t1.score desc) as 排名

from

( select SCO.stu_id as stu_id,SCO.lesson_id as lesson_id,score,lesson_name,stu_name

FROM t_score SCO LEFT JOIN t_stu_profile STU

ON SCO.stu_id=STU.stu_id

left join t_lesson LES on SCO.lesson_id=LES.lesson_id )t1;

#方法二

select

lesson_name,

group_concat(if(排名=1,concat(stu_name,'+',score),null)) as 第一名,

group_concat(if(排名=2,concat(stu_name,'+',score),null)) as 第二名,

group_concat(if(排名=3,concat(stu_name,'+',score),null)) as 第三名

from

(select

lesson_name,

stu_name,

score,

dense_rank() over(partition by lesson_name order by score desc) as 排名

from t_score

left join t_lesson

on t_lesson.lesson_id=t_score.lesson_id

left join t_stu_profile

on t_stu_profile.stu_id=t_score.stu_id) as t

group by lesson_name;