【如何成为SQL高手】第九关：高级复杂查询


哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10年DBA工作经验
一位上进心十足的【大数据领域博主】！
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程，B站及腾讯课堂讲师，直播量破10W
擅长主流数据Oracle、MySQL、PG 运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。
✨ 如果有对【数据库】感兴趣的【小可爱】，欢迎关注【IT邦德】
❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️

SQL对于现在的互联网公司产研岗位几乎是一个必备技能，但仅会SQL的话，应该是什么都做不了。
1.如果你是数据分析师，你需要熟练地把自己脑子里的数据和指标需求翻译成SQL逻辑去查询数据，进而完成自己的数据分析报告等，你的产出是分析报告，而不是SQL代码；
2.如果你是数仓工程师（偏应用层），你需要根据业务逻辑去设计模型，编写调度任务去产出数据，以供业务人员使用，你的产出是数据模型和表；
3.如果你是算法工程师，你可能需要用SQL来实现用户标签、特征工程等工作，但是这些是为你的模型训练评估做基础准备工作，你的产出是可以提升某些指标的算法模型。

所以，SQL每个人都要用，但是用来衡量产出的并不是SQL本身，你需要用这个工具，去创造其它的价值。
IT邦德老师带你成为SQL高手，那我们开始吧~

文章目录

⛳️1.ANY用法⛳️2.ALL的用法⛳️3、自连接⛳️4、排序

 4.1 多字段排序 4.2 等级处理 4.3 排名

环境准备，通过以下建表

-- 创建数据库CREATE DATAbase mes_db charset utf8mb4;-- 切换数据库USE mes_db;-- 创建学生表CREATE TABLE student (no VARCHAr(20) PRIMARY KEY,name VARCHAr(20) NOT NULL,sex VARCHAr(10) NOT NULL,birthday DATE, -- 生日class VARCHAr(20) -- 所在班级);-- 创建教师表CREATE TABLE teacher (no VARCHAr(20) PRIMARY KEY,name VARCHAr(20) NOT NULL,sex VARCHAr(10) NOT NULL,birthday DATE,profession VARCHAr(20) NOT NULL, -- 职称department VARCHAr(20) NOT NULL -- 部门);-- 创建课程表CREATE TABLE course (no VARCHAr(20) PRIMARY KEY,name VARCHAr(20) NOT NULL,t_no VARCHAr(20) NOT NULL, -- 教师编号-- 表示该 t_no 来自于 teacher 表中的 no 字段值FOREIGN KEY(t_no) REFERENCES teacher(no));-- 成绩表CREATE TABLE score (s_no VARCHAr(20) NOT NULL, -- 学生编号c_no VARCHAr(20) NOT NULL, -- 课程号degree DECIMAL, -- 成绩-- 表示该 s_no, c_no 分别来自于 student, course 表中的 no 字段值FOREIGN KEY(s_no) REFERENCES student(no),FOREIGN KEY(c_no) REFERENCES course(no),-- 设置 s_no, c_no 为联合主键PRIMARY KEY(s_no, c_no));-- 查看所有表SHOW TABLES;-- 添加学生表数据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-105', '92');INSERT INTO score VALUES('103', '3-245', '86');INSERT INTO score VALUES('103', '6-166', '85');INSERT INTO score VALUES('105', '3-105', '88');INSERT INTO score VALUES('105', '3-245', '75');INSERT INTO score VALUES('105', '6-166', '79');INSERT INTO score VALUES('109', '3-105', '76');INSERT INTO score VALUES('109', '3-245', '68');INSERT INTO score VALUES('109', '6-166', '81');

⛳️1.ANY用法

查询课程 3-105 且成绩至少高 3-245 的 score 表，DESC ( 降序 )

#ANY: 符合 SQL 语句中的任意条件
#也就是说，3-105成绩中，只要有一个大于从3-245筛选出来的任意行就符合条件
#最后根据降序查询结果
SELECt * FROM score WHERe c_no = ‘3-105’ AND degree > ANY(
SELECt degree FROM score WHERe c_no = ‘3-245’
) ORDER BY degree DESC;

⛳️2.ALL的用法

查询课程 3-105 且成绩高于 3-245 的 score 表

#只需对上一道题稍作修改。
#ALL: 符合 SQL 语句中的所有条件。
#也就是说，在3-105成绩中，都要大于从3-245筛选出来全部行才算符合条件
SELECt * FROM score WHERe c_no = ‘3-105’ AND degree > ALL(
SELECt degree FROM score WHERe c_no = ‘3-245’);

⛳️3、自连接

查询某课程成绩比该课程平均成绩低的 score 表
#将表 b 作用于表 a 中查询数据
#score 别名a,b 如此就能用 a.c_no=b.c_no 作为条件执行查询
SELECt * FROM score a WHERe degree < (
(SELECt AVG(degree) FROM score b WHERe a.c_no = b.c_no));

⛳️4、排序  4.1 多字段排序

以 class 和 birthday 从大到小的顺序查询 student 表
SELECt * FROM student ORDER BY class DESC, birthday;

 4.2 等级处理

建立一个 grade 表代表学生的成绩等级，并插入数据：
CREATE TABLE grade (
low INT(3),
upp INT(3),
grade char(1)
);
INSERT INTO grade VALUES (90, 100, ‘A’);
INSERT INTO grade VALUES (80, 89, ‘B’);
INSERT INTO grade VALUES (70, 79, ‘C’);
INSERT INTO grade VALUES (60, 69, ‘D’);
INSERT INTO grade VALUES (0, 59, ‘E’);
SELECt * FROM grade;
–查询所有学生的 s_no 、c_no 和 grade 列
SELECt s_no, c_no, grade FROM score, grade
WHERe degree BETWEEN low AND upp;

 4.3 排名

create table scores_tb (id int auto_increment primary key,xuehao int not null,score int not null) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;insert into scores_tb (xuehao,score)values (1001,89),(1002,99),(1003,96),(1004,96),(1005,92),1006,90),(1007,90),(1008,94);select * from scores_tb;

MySQL8.0 中可以利用 ROW_NUMBER()，DENSE_RANK()，RANK()
三个窗口函数实现上述三种排名

普通排名：
按分数高低直接排名，从 1 开始，往下排，类似于 row number
select xuehao,score,
ROW_NUMBER() OVER(order by score desc)
as row_r from scores_tb;

分数相同，名次相同，排名无间隔
select xuehao,score,
DENSE_RANK() OVER(order by score desc) as dense_r
from scores_tb;

并列排名，排名有间隔
select xuehao,score,
RANK() over(order by score desc) as r
from scores_tb;

大家点赞、收藏、关注、评论啦 微信公众号