MySQL 面试——查询每门课程前几名

能看到这个考题的，都可以去面试需要 三五年经验的高级数据分析师 了。

首先，你必须按照这个博客内容写。为什么呢？因为partition是高阶功能，适用于大规模数据，而且提到partition表示你已经部分知道MySQL优化了，是很高阶的答案。其作用类似于你在中考的时候用微积分作答……

其它博客常见的有join（性能极低，会被追问性能）、嵌套子查询（语句难以理解，且未优化，仍然会被问性能和优化）——其它常见的虽不作为推荐，但附于文后，供大家比较、练习。

——文章写于20200610晚上，mysql版本如下：Server version: 8.0.20 MySQL Community Server - GPL

数据表大概长下面这样（建表语句放最后）：
select * from score;
±-----±-----±-------+
| sid | cid | cscore |
±-----±-----±-------+
| 01 | 01 | 80.0 |
| 01 | 02 | 90.0 |
| 01 | 03 | 99.0 |
| 02 | 01 | 70.0 |
| 02 | 02 | 60.0 |
| 02 | 03 | 80.0 |
| 03 | 01 | 80.0 |
| 03 | 02 | 80.0 |
| 03 | 03 | 80.0 |
| 04 | 01 | 50.0 |
| 04 | 02 | 30.0 |
| 04 | 03 | 20.0 |
| 05 | 01 | 76.0 |
| 05 | 02 | 87.0 |
| 06 | 01 | 31.0 |
| 06 | 03 | 34.0 |
| 07 | 02 | 89.0 |
| 07 | 03 | 98.0 |
±-----±-----±-------+

三者的区分还可以看另一篇博主的对比文章：https://blog.csdn.net/weixin_43629813/article/details/129847325

1——看重点——查询前三名，可以并列排名的那种（第三名有两个，但也会有第四名，注意与rank区分）

select  *  from(
select  *, dense_rank()  over(partition by cid ORDER BY cscore DESC) as rnk from score
) as t where t.rnk <= 3

2——可以并列第三名（但第三名有两个的话，就没有第四名）

select  *  from(
select  *, rank()  over(partition by cid ORDER BY cscore DESC) as rnk from score
) as t where t.rnk <= 3

3——那如果取排名最前面的三个人呢？（一定分先后，排名不中断）

select  *  from(
select  *, row_number()  over(partition by cid ORDER BY cscore DESC) as rnk from score
) as t where t.rnk <= 3

此处涉及另一个问题，排名相同时候，谁在前面谁在后面，这个问题等有空再码。

其它常见查询语句及其缺点

使用join、嵌套子查询

https://blog.csdn.net/and52696686/article/details/107591245?utm_source=app

附录

建表语句

CREATE TABLE student(
    s_no VARCHAR(20) PRIMARY KEY COMMENT'学生学号',
    s_name VARCHAR(20) NOT NULL COMMENT'学生姓名 不能为空',
    s_sex VARCHAR(10) NOT NULL COMMENT'学生性别',
    s_birthday DATETIME COMMENT'学生生日',
    s_class VARCHAR(20) COMMENT'学生所在的班级'
);

CREATE TABLE teacher(
    t_no VARCHAR(20) PRIMARY KEY COMMENT'教师编号',
    t_name VARCHAR(20) NOT NULL COMMENT'教师姓名',
    t_sex VARCHAR(20) NOT NULL COMMENT'教师性别',
    t_birthday DATETIME COMMENT'教师生日',
    t_rof VARCHAR(20) NOT NULL COMMENT'教师职称',
    t_depart VARCHAR(20) NOT NULL COMMENT'教师所在的部门'
);

CREATE TABLE course(
    c_no VARCHAR(20) PRIMARY KEY COMMENT'课程号',
    c_name VARCHAR(20) NOT NULL COMMENT'课程名称',
    t_no VARCHAR(20) NOT NULL COMMENT'教师编号 外键关联teacher表',
    FOREIGN KEY(t_no) references teacher(t_no)
);

CREATE TABLE score (
    s_no VARCHAR(20) NOT NULL COMMENT'成绩表的编号 依赖学生学号',
        c_no VARCHAR(20)  NOT NULL COMMENT'课程号 依赖于课程表中的c_id',
    sc_degree decimal,
    foreign key(s_no) references student(s_no),
    foreign key(c_no) references course(c_no),
    PRIMARY KEY(s_no,c_no)
);

--学生表数据
INSERT INTO student VALUES('101','曾华','男','1977-09-01','95033');
INSERT INTO student VALUES('102','匡明','男','1975-10-02','95031');
INSERT INTO student VALUES('103','王丽','女','1976-01-23','95033');
INSERT INTO student VALUES('104','李军','男','1976-02-20','95033');
INSERT INTO student VALUES('105','王芳','女','1975-02-10','95031');
INSERT INTO student VALUES('106','陆军','男','1974-06-03','95031');
INSERT INTO student VALUES('107','王尼玛','男','1976-02-20','95033');
INSERT INTO student VALUES('108','张全蛋','男','1975-02-10','95031');
INSERT INTO student VALUES('109','赵铁柱','男','1974-06-03','95031');

--教师表数据
INSERT INTO teacher VALUES('804','李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO teacher VALUES('856','张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO teacher VALUES('825','王萍','女','1972-05-05','助教','计算机系');
INSERT INTO teacher VALUES('831','刘冰','女','1977-08-14','助教','电子工程系');

--添加课程表
INSERT INTO course VALUES('3-105','计算机导论','825');
INSERT INTO course VALUES('3-245','操作系统','804');
INSERT INTO course VALUES('6-166','数字电路','856');
INSERT INTO course VALUES('9-888','高等数学','831');

--添加成绩表
INSERT INTO score VALUES('103','3-245','86');
INSERT INTO score VALUES('105','3-245','75');
INSERT INTO score VALUES('109','3-245','68');
INSERT INTO score VALUES('103','3-105','92');

INSERT INTO score VALUES('105','3-105','88');
INSERT INTO score VALUES('109','3-105','76');
INSERT INTO score VALUES('103','6-166','85');

INSERT INTO score VALUES('105','6-166','79');
INSERT INTO score VALUES('109','6-166','81');

end