MySQL进阶-SQL高级语法全解析

一、复杂查询语法搞定多表与子查询的核心技巧实际开发中我们很少只查询单张表多表关联、子查询是高频需求这部分也是SQL进阶的基础掌握这些能解决80%的业务查询场景。1.1 多表连接不同场景选对连接方式效率翻倍多表连接的核心是“通过关联字段将多张表的数据整合为一张虚拟表”常用的连接方式有4种INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN很多人容易混淆它们的区别这里用“学生表成绩表”的示例一次性讲清楚。先准备测试表和测试数据后续所有示例均基于这两张表-- 学生表student CREATE TABLE student ( id INT PRIMARY KEY AUTO_INCREMENT, student_no VARCHAR(20) NOT NULL COMMENT 学号, name VARCHAR(50) NOT NULL COMMENT 姓名, grade VARCHAR(10) NOT NULL COMMENT 年级, class VARCHAR(20) NOT NULL COMMENT 班级 ); -- 成绩表score CREATE TABLE score ( id INT PRIMARY KEY AUTO_INCREMENT, student_no VARCHAR(20) NOT NULL COMMENT 学号关联student表, subject VARCHAR(30) NOT NULL COMMENT 科目, score INT NOT NULL COMMENT 分数, exam_date DATE NOT NULL COMMENT 考试日期 ); -- 插入测试数据 INSERT INTO student (student_no, name, grade, class) VALUES (2024001, 张三, 高一, 1班), (2024002, 李四, 高一, 1班), (2024003, 王五, 高一, 2班), (2024004, 赵六, 高二, 3班); INSERT INTO score (student_no, subject, score, exam_date) VALUES (2024001, 数学, 92, 2024-06-10), (2024001, 语文, 88, 2024-06-10), (2024002, 数学, 75, 2024-06-10), (2024003, 语文, 90, 2024-06-10), (2024005, 数学, 80, 2024-06-10); -- 该学号无对应学生信息1.1.1 INNER JOIN内连接只取两张表的交集核心逻辑只返回“两张表中关联字段匹配成功”的数据不匹配的会被过滤掉。示例查询有成绩记录的学生姓名、班级及对应科目和分数只显示既有学生信息、又有成绩的记录SELECT s.name, s.class, sc.subject, sc.score FROM student s INNER JOIN score sc ON s.student_no sc.student_no; -- 执行结果不会出现赵六无成绩和学号2024005无学生信息的记录 -- 姓名 班级 科目 分数 -- 张三 1班 数学 92 -- 张三 1班 语文 88 -- 李四 1班 数学 75 -- 王五 2班 语文 901.1.2 LEFT JOIN左连接左表全保留右表匹配补充核心逻辑保留“左表LEFT JOIN左边的表”的所有数据右表中匹配成功的显示对应数据匹配失败的显示NULL。示例查询所有学生的姓名、班级以及他们的成绩无成绩的学生也显示成绩字段为NULLSELECT s.name, s.class, sc.subject, sc.score FROM student s LEFT JOIN score sc ON s.student_no sc.student_no; -- 执行结果赵六2024004无成绩subject和score显示NULL -- 姓名 班级 科目 分数 -- 张三 1班 数学 92 -- 张三 1班 语文 88 -- 李四 1班 数学 75 -- 王五 2班 语文 90 -- 赵六 3班 NULL NULL1.1.3 RIGHT JOIN右连接右表全保留左表匹配补充核心逻辑和LEFT JOIN相反保留“右表RIGHT JOIN右边的表”的所有数据左表匹配成功显示对应数据匹配失败显示NULL。示例查询所有成绩记录以及对应学生的姓名、班级无对应学生的成绩也显示SELECT s.name, s.class, sc.subject, sc.score FROM student s RIGHT JOIN score sc ON s.student_no sc.student_no; -- 执行结果学号2024005无学生信息name和class显示NULL -- 姓名 班级 科目 分数 -- 张三 1班 数学 92 -- 张三 1班 语文 88 -- 李四 1班 数学 75 -- 王五 2班 语文 90 -- NULL NULL 数学 801.1.4 FULL JOIN全连接保留两张表的所有数据核心逻辑保留左表和右表的所有数据匹配成功的显示对应数据匹配失败的一侧显示NULL。 注意MySQL本身不直接支持FULL JOIN但可以通过“LEFT JOIN UNION ALL RIGHT JOIN”模拟实现。示例查询所有学生和所有成绩记录无论是否匹配-- 模拟FULL JOIN SELECT s.name, s.class, sc.subject, sc.score FROM student s LEFT JOIN score sc ON s.student_no sc.student_no UNION ALL SELECT s.name, s.class, sc.subject, sc.score FROM student s RIGHT JOIN score sc ON s.student_no sc.student_no WHERE s.id IS NULL; -- 过滤掉重复的匹配数据 -- 执行结果包含所有学生和所有成绩无匹配的字段显示NULL -- 姓名 班级 科目 分数 -- 张三 1班 数学 92 -- 张三 1班 语文 88 -- 李四 1班 数学 75 -- 王五 2班 语文 90 -- 赵六 3班 NULL NULL -- NULL NULL 数学 801.1.5 自连接与不等值连接除了上述4种常规连接还有两种特殊连接场景在复杂业务中会用到1. 自连接一张表自己和自己连接本质是“将一张表当作两张表使用”常用于查询“同一表中具有关联关系的数据”如员工表中查询员工及其上级。示例新增员工表测试数据-- 员工表含上级ID CREATE TABLE emp ( id INT PRIMARY KEY AUTO_INCREMENT, emp_name VARCHAR(50) NOT NULL, dept VARCHAR(30) NOT NULL, manager_id INT COMMENT 上级ID关联自身id ); INSERT INTO emp (emp_name, dept, manager_id) VALUES (张三, 研发部, NULL), -- 无上级部门经理 (李四, 研发部, 1), -- 上级是张三 (王五, 研发部, 1), -- 上级是张三 (赵六, 测试部, NULL); -- 无上级部门经理 -- 自连接查询查询每个员工的姓名及其上级姓名 SELECT e.emp_name AS 员工姓名, m.emp_name AS 上级姓名 FROM emp e LEFT JOIN emp m ON e.manager_id m.id; -- 执行结果 -- 员工姓名 上级姓名 -- 张三 NULL -- 李四 张三 -- 王五 张三 -- 赵六 NULL2. 不等值连接连接条件不是“”而是“、、、、BETWEEN AND”等不等值条件常用于“范围匹配”场景。示例新增分数等级表-- 分数等级表 CREATE TABLE score_level ( level VARCHAR(10) PRIMARY KEY, min_score INT NOT NULL, max_score INT NOT NULL ); INSERT INTO score_level (level, min_score, max_score) VALUES (优秀, 90, 100), (良好, 80, 89), (及格, 60, 79), (不及格, 0, 59); -- 不等值连接查询每个学生的成绩及对应等级 SELECT sc.student_no, sc.subject, sc.score, sl.level FROM score sc LEFT JOIN score_level sl ON sc.score BETWEEN sl.min_score AND sl.max_score; -- 执行结果 -- student_no subject score level -- 2024001 数学 92 优秀 -- 2024001 语文 88 良好 -- 2024002 数学 75 及格 -- 2024003 语文 90 优秀 -- 2024005 数学 80 良好1.2 子查询嵌套查询的正确用法与避坑技巧子查询又称嵌套查询将一个查询语句的结果作为另一个查询语句的条件或数据源分为“标量子查询、列子查询、行子查询、表子查询、关联子查询”5种核心是“先执行内层查询再执行外层查询”。重点子查询虽然灵活但嵌套过深会影响性能实际开发中需合理使用优先考虑用JOIN替代复杂子查询。1.2.1 标量子查询返回单个值一行一列核心特点内层查询结果只有“一个值”常用于WHERE条件中搭配、、等比较运算符。示例1查询数学成绩大于平均分的学生学号、科目、分数-- 先查询数学平均分内层子查询返回单个值再查询大于该平均分的记录 SELECT student_no, subject, score FROM score WHERE subject 数学 AND score (SELECT AVG(score) FROM score WHERE subject 数学); -- 执行逻辑先执行内层查询得到数学平均分(927580)/3 ≈ 82.33再筛选出分数82.33的记录 -- 执行结果 -- student_no subject score -- 2024001 数学 92示例2查询成绩最高的学生姓名、科目、分数假设最高成绩唯一SELECT s.name, sc.subject, sc.score FROM student s JOIN score sc ON s.student_no sc.student_no WHERE sc.score (SELECT MAX(score) FROM score); -- 执行结果成绩最高为92张三数学 -- name subject score -- 张三 数学 921.2.2 列子查询返回一列多行核心特点内层查询结果是“一列多个值”常用于WHERE条件中搭配IN、NOT IN、ANY、ALL等运算符。示例1查询高一学生的所有成绩记录先查高一学生的学号再查对应成绩SELECT * FROM score WHERE student_no IN (SELECT student_no FROM student WHERE grade 高一); -- 内层查询返回高一学生的学号2024001、2024002、2024003外层查询筛选出这些学号的成绩 -- 执行结果包含张三、李四、王五的成绩不包含赵六高二和2024005无年级信息 -- id student_no subject score exam_date -- 1 2024001 数学 92 2024-06-10 -- 2 2024001 语文 88 2024-06-10 -- 3 2024002 数学 75 2024-06-10 -- 4 2024003 语文 90 2024-06-10示例2查询分数大于“高一所有学生数学成绩”的记录用ALLSELECT * FROM score WHERE score ALL (SELECT score FROM score WHERE student_no IN (SELECT student_no FROM student WHERE grade 高一) AND subject 数学); -- 内层查询返回高一学生的数学成绩92、75ALL表示“大于所有值”即92 -- 执行结果无符合条件的记录最高分为921.2.3 行子查询返回一行多列核心特点内层查询结果是“一行多个值”常用于WHERE条件中搭配、IN等运算符需保证内层和外层的列数、数据类型一致。示例查询和“张三2024001数学成绩”相同的所有记录学号、科目、分数都匹配SELECT * FROM score WHERE (student_no, subject, score) (SELECT student_no, subject, score FROM score WHERE student_no 2024001 AND subject 数学); -- 内层查询返回一行三列2024001、数学、92外层查询匹配相同的记录 -- 执行结果 -- id student_no subject score exam_date -- 1 2024001 数学 92 2024-06-101.2.4 表子查询返回多行多列当作临时表核心特点内层查询结果是“多行多列”相当于一张临时表常用于FROM子句中给临时表起别名后使用。示例查询每个学生的平均成绩筛选出平均成绩大于85分的学生先查每个学生的平均成绩再筛选-- 表子查询将每个学生的平均成绩作为临时表 SELECT t.student_no, s.name, t.avg_score FROM (SELECT student_no, AVG(score) AS avg_score FROM score GROUP BY student_no) t JOIN student s ON t.student_no s.student_no WHERE t.avg_score 85; -- 执行逻辑内层子查询生成临时表t包含student_no和avg_score再和student表连接筛选平均成绩85的记录 -- 执行结果 -- student_no name avg_score -- 2024001 张三 90.0000 -- 2024003 王五 90.00001.2.5 关联子查询内层查询依赖外层查询核心难点核心特点和普通子查询不同关联子查询的内层查询会用到外层查询的字段执行顺序是“外层查询每执行一行内层查询就执行一次”灵活性高但性能相对较差避免大量数据场景使用。示例1查询每个学生的最高成绩及对应科目按学生分组每个学生取最高成绩的记录SELECT s.name, sc.subject, sc.score FROM student s JOIN score sc ON s.student_no sc.student_no WHERE sc.score (SELECT MAX(score) FROM score WHERE student_no sc.student_no); -- 执行逻辑外层查询每取一条score记录内层查询就根据该记录的student_no查询该学生的最高成绩判断当前score是否等于最高成绩 -- 执行结果 -- name subject score -- 张三 数学 92 -- 李四 数学 75 -- 王五 语文 90 -- 2024005无学生信息不显示示例2查询每个科目中分数大于该科目平均分的记录SELECT subject, student_no, score FROM score sc1 WHERE score (SELECT AVG(score) FROM score sc2 WHERE sc2.subject sc1.subject); -- 执行逻辑外层查询每取一条sc1记录内层查询就根据该记录的subject查询该科目的平均分判断当前score是否大于平均分 -- 执行结果数学平均分≈82.33语文平均分≈89 -- subject student_no score -- 数学 2024001 92 -- 语文 2024003 901.3 EXISTS / IN 优化与选择EXISTS和IN都是用于“判断是否存在满足条件的记录”但用法和性能有差异很多人不知道该选哪个这里结合场景给出明确结论。1.3.1 语法区别-- IN判断字段是否在子查询返回的列表中 SELECT * FROM 表名 WHERE 字段 IN (子查询); -- EXISTS判断子查询是否返回数据只要有一条就返回true SELECT * FROM 表名 WHERE EXISTS (子查询);1.3.2 性能差异与选择原则核心结论外层表小用IN内层表小用EXISTS原因IN是先执行内层子查询将结果存入临时表再和外层表匹配EXISTS是先执行外层表再执行内层子查询匹配到就终止无需全部执行。示例1查询有成绩记录的学生外层表student小用INSELECT * FROM student WHERE student_no IN (SELECT DISTINCT student_no FROM score); -- 等价于用EXISTS SELECT * FROM student s WHERE EXISTS (SELECT 1 FROM score sc WHERE sc.student_no s.student_no); -- 两种方式结果一致但student表数据量小时IN效率更高示例2查询有学生信息的成绩记录内层表student小用EXISTSSELECT * FROM score sc WHERE EXISTS (SELECT 1 FROM student s WHERE s.student_no sc.student_no); -- 等价于用IN SELECT * FROM score WHERE student_no IN (SELECT student_no FROM student); -- 两种方式结果一致但student表数据量小时EXISTS效率更高注意NOT IN 和 NOT EXISTS 差异更大——NOT IN 会受到NULL值影响如果子查询返回的结果包含NULLNOT IN会返回空集而NOT EXISTS 不受NULL值影响优先用NOT EXISTS替代NOT IN。反例NOT IN 踩坑-- 子查询返回的student_no包含NULL假设新增一条student_no为NULL的成绩 INSERT INTO score (student_no, subject, score, exam_date) VALUES (NULL, 英语, 85, 2024-06-10); -- 用NOT IN 查询无学生信息的成绩记录会返回空集因为NULL无法比较 SELECT * FROM score WHERE student_no NOT IN (SELECT student_no FROM student); -- 用NOT EXISTS 查询正常返回结果 SELECT * FROM score sc WHERE NOT EXISTS (SELECT 1 FROM student s WHERE s.student_no sc.student_no);1.4 UNION / UNION ALL 区别与性能UNION和UNION ALL 用于“合并两个或多个查询结果集”要求两个查询的列数、数据类型一致核心区别在于“是否去重”。1.4.1 语法与示例-- UNION合并结果集去重会扫描所有结果删除重复记录性能较差 SELECT student_no, subject, score FROM score WHERE subject 数学 UNION SELECT student_no, subject, score FROM score WHERE score 90; -- UNION ALL合并结果集不去重直接合并不处理重复性能较好 SELECT student_no, subject, score FROM score WHERE subject 数学 UNION ALL SELECT student_no, subject, score FROM score WHERE score 90;执行结果对比1. UNION 结果合并后去重张三的数学92分只出现一次2. UNION ALL 结果合并后不去重张三的数学92分出现两次既满足subject数学又满足score90。1.4.2 选择原则1. 如果确定两个结果集没有重复记录优先用UNION ALL性能更优避免不必要的去重操作2. 如果可能有重复记录且需要去重再用UNION3. 注意UNION 和 UNION ALL 都要求两个查询的“列数、数据类型、列顺序”完全一致否则会报错。二、分组与聚合高级用法摆脱GROUP BY的常见坑分组GROUP BY和聚合函数COUNT、SUM、AVG等是统计类查询的核心很多人在使用时会遇到“ONLY_FULL_GROUP_BY”报错、HAVING与WHERE混淆等问题这里逐一拆解结合示例避坑。2.1 GROUP BY 原理与常见坑ONLY_FULL_GROUP_BY核心原理GROUP BY 用于“按指定字段分组”分组后SELECT 后面的字段只能是“分组字段”或“聚合函数”否则会出现逻辑混乱MySQL 5.7 开启了ONLY_FULL_GROUP_BY模式会直接报错。常见坑SELECT 字段中包含非分组、非聚合的字段导致报错。示例正确 vs 错误-- 错误示例SELECT 包含非分组、非聚合字段nameONLY_FULL_GROUP_BY模式下报错 SELECT student_no, name, AVG(score) AS avg_score FROM student s JOIN score sc ON s.student_no sc.student_no GROUP BY student_no; -- 报错原因name不是分组字段也不是聚合函数分组后无法确定返回哪个name虽然一个student_no对应一个name但MySQL不允许 -- 正确示例1SELECT 只包含分组字段和聚合函数 SELECT student_no, AVG(score) AS avg_score FROM score GROUP BY student_no; -- 正确示例2如果需要显示name将name也加入分组因为student_no是主键name和student_no一一对应分组后不影响 SELECT s.student_no, s.name, AVG(sc.score) AS avg_score FROM student s JOIN score sc ON s.student_no sc.student_no GROUP BY s.student_no, s.name;补充如果确实需要在SELECT中显示非分组字段可通过“聚合函数如MAX、MIN”包裹适用于非分组字段和分组字段一一对应的场景SELECT student_no, MAX(name) AS name, AVG(score) AS avg_score FROM student s JOIN score sc ON s.student_no sc.student_no GROUP BY student_no;2.2 聚合函数COUNT/SUM/AVG/MAX/MIN 进阶基础用法大家都熟悉这里重点讲进阶用法和避坑点尤其是COUNT和AVG的细节。2.2.1 COUNT统计行数的3种用法与区别-- 1. COUNT(*)统计所有行数包括NULL值最常用效率最高 SELECT COUNT(*) FROM score; -- 统计所有成绩记录数含student_no为NULL的记录 -- 2. COUNT(字段名)统计该字段非NULL的行数 SELECT COUNT(student_no) FROM score; -- 统计student_no非NULL的成绩记录数不含student_no为NULL的记录 -- 3. COUNT(DISTINCT 字段名)统计该字段非NULL且去重后的行数 SELECT COUNT(DISTINCT student_no) FROM score; -- 统计有成绩的不同学生数避坑点COUNT(1) 和 COUNT(*) 效率基本一致MySQL优化后无需刻意替换COUNT(字段名) 效率低于COUNT(*)因为需要判断字段是否为NULL。2.2.2 SUM/AVG处理NULL值与异常数据1. SUM自动忽略NULL值若所有值都是NULL返回NULL可搭配IFNULL处理2. AVG自动忽略NULL值计算的是“非NULL值的平均值”不是所有行的平均值。-- 示例给score表新增一条score为NULL的记录 INSERT INTO score (student_no, subject, score, exam_date) VALUES (2024001, 英语, NULL, 2024-06-10); -- SUM忽略NULL值返回9288759080 425 SELECT SUM(score) FROM score; -- AVG忽略NULL值计算5个非NULL值的平均425/585不是6条记录的平均 SELECT AVG(score) FROM score; -- 处理NULL值将NULL视为0计算平均 SELECT AVG(IFNULL(score, 0)) FROM score; -- (425 0)/6 ≈ 70.832.2.3 MAX/MIN忽略NULL值支持非数值类型MAX和MIN不仅支持数值类型还支持字符串、日期类型自动忽略NULL值。-- 数值类型查询最高、最低分数 SELECT MAX(score) AS max_score, MIN(score) AS min_score FROM score; -- 字符串类型查询姓名排序最前、最后的学生按字典序 SELECT MAX(name) AS max_name, MIN(name) AS min_name FROM student; -- 日期类型查询最早、最晚的考试日期 SELECT MAX(exam_date) AS latest_date, MIN(exam_date) AS earliest_date FROM score;2.3 HAVING 与 WHERE 区别核心重点很多人混淆HAVING和WHERE核心区别只有一个WHERE 过滤行HAVING 过滤分组具体差异如下对比项WHEREHAVING作用对象单个行数据分组后的结果集使用时机分组GROUP BY之前分组GROUP BY之后支持的条件普通字段、表达式不支持聚合函数聚合函数、分组字段支持普通字段不推荐示例对比使用-- 需求1查询数学科目中分数大于80分的学生按学号分组统计平均成绩先过滤行再分组 SELECT student_no, AVG(score) AS avg_score FROM score WHERE subject 数学 AND score 80 -- WHERE过滤数学科目、分数80的行 GROUP BY student_no; -- 需求2查询每个学生的平均成绩筛选出平均成绩大于85分的学生先分组再过滤分组 SELECT student_no, AVG(score) AS avg_score FROM score GROUP BY student_no HAVING avg_score 85; -- HAVING过滤平均成绩85的分组 -- 错误示例WHERE中使用聚合函数会报错 SELECT student_no, AVG(score) AS avg_score FROM score WHERE AVG(score) 85 -- 报错Invalid use of group function GROUP BY student_no;2.4 分组后排序、分页实际开发中分组后常需要对分组结果排序如按平均成绩降序、分页如只显示前2个分组核心是“GROUP BY 之后用ORDER BY 排序LIMIT 分页”。-- 示例查询每个学生的平均成绩按平均成绩降序排序分页显示前2条 SELECT s.name, s.class, AVG(sc.score) AS avg_score FROM student s JOIN score sc ON s.student_no sc.student_no GROUP BY s.student_no, s.name, s.class ORDER BY avg_score DESC -- 分组后排序 LIMIT 0, 2; -- 分页从第0条开始取2条 -- 执行结果显示平均成绩最高的2个学生 -- name class avg_score -- 张三 1班 90.0000 -- 王五 2班 90.00002.5 去重统计COUNT(DISTINCT xx)用于“统计某字段不重复的行数”是业务中高频需求如统计有多少个不同的学生参加了考试、有多少个不同的科目。-- 示例1统计有多少个不同的学生参加了考试去重 SELECT COUNT(DISTINCT student_no) AS student_count FROM score; -- 示例2统计有多少个不同的科目去重 SELECT COUNT(DISTINCT subject) AS subject_count FROM score; -- 示例3统计每个班级有多少个不同的学生参加了考试 SELECT s.class, COUNT(DISTINCT s.student_no) AS student_count FROM student s JOIN score sc ON s.student_no sc.student_no GROUP BY s.class;避坑点COUNT(DISTINCT 字段1, 字段2) 表示“两个字段同时去重”只有两个字段都相同才视为重复。-- 统计不同的“学生科目”组合数一个学生选多个科目视为不同组合 SELECT COUNT(DISTINCT student_no, subject) AS combo_count FROM score;三、窗口函数进阶必备面试高频重点难点窗口函数是MySQL 8.0 新增的核心功能也是进阶阶段的重点能轻松解决“TopN、连续登录、环比同比、排名”等复杂业务场景替代复杂的子查询和自连接代码更简洁、可读性更高。核心定义窗口函数 聚合函数 / 排序函数 OVER(窗口子句)其中“窗口子句”用于定义“分析的范围”如按哪个字段分组、排序。窗口子句常用语法OVER(PARTITION BY 分组字段 ORDER BY 排序字段 [ROWS/RANGE 范围])说明PARTITION BY 相当于“分组但不聚合”分组后每个组内的每行都保留ORDER BY 用于组内排序ROWS/RANGE 用于指定窗口范围默认无需手动指定。3.1 排序类窗口函数ROW_NUMBER() / RANK() / DENSE_RANK()三者都是用于“组内排序、排名”核心区别在于“处理并列排名的方式”用示例一次性讲清楚基于score表按科目分组对分数排序。-- 示例按科目分组对每个科目的分数进行排名对比三种函数 SELECT subject, student_no, score, ROW_NUMBER() OVER(PARTITION BY subject ORDER BY score DESC) AS row_num, RANK() OVER(PARTITION BY subject ORDER BY score DESC) AS rnk, DENSE_RANK() OVER(PARTITION BY subject ORDER BY score DESC) AS dense_rnk FROM score; -- 执行结果以数学科目为例分数92、80、75 -- subject student_no score row_num rnk dense_rnk -- 数学 2024001 92 1 1 1 -- 数学 2024005 80 2 2 2 -- 数学 2024002 75 3 3 3 -- 若新增一条数学分数92的记录student_no2024006结果变化 -- subject student_no score row_num rnk dense_rnk -- 数学 2024001 92 1 1 1 -- 数学 2024006 92 2 1 1 -- 数学 2024005 80 3 3 2 -- 数学 2024002 75 4 4 3三者区别总结1. ROW_NUMBER()不处理并列即使分数相同排名也不重复1、2、3、42. RANK()处理并列并列的排名相同后续排名跳过1、1、3、43. DENSE_RANK()处理并列并列的排名相同后续排名不跳过1、1、2、3。实战场景TopN问题每个科目取分数前2名-- 方法1用ROW_NUMBER()分数相同只取前2个不管并列 SELECT * FROM ( SELECT subject, student_no, score, ROW_NUMBER() OVER(PARTITION BY subject ORDER BY score DESC) AS row_num FROM score ) t WHERE t.row_num 2; -- 方法2用RANK()分数相同并列排名可能超过2条 SELECT * FROM ( SELECT subject, student_no, score, RANK() OVER(PARTITION BY subject ORDER BY score DESC) AS rnk FROM score ) t WHERE t.rnk 2;3.2 分布类窗口函数NTILE() / PERCENT_RANK()用于“将数据分组、计算百分比排名”适用于“分档次、占比分析”场景。3.2.1 NTILE(n)将组内数据分为n个档次核心将每个分组内的行平均分为n个部分返回每个行所在的档次若无法平均分前面的档次会多1行。-- 示例按科目分组将每个科目的分数分为3个档次优秀、良好、及格 SELECT subject, student_no, score, NTILE(3) OVER(PARTITION BY subject ORDER BY score DESC) AS level FROM score; -- 执行结果数学科目4条记录分为3档第1档1行第2档1行第3档2行 -- subject student_no score level -- 数学 2024001 92 1 -- 数学 2024006 92 2 -- 数学 2024005 80 3 -- 数学 2024002 75 33.2.2 PERCENT_RANK()计算百分比排名核心返回当前行在组内的百分比排名公式(当前排名 - 1) / (组内总行数 - 1)结果范围0~10表示最低1表示最高。-- 示例按科目分组计算每个学生分数的百分比排名 SELECT subject, student_no, score, RANK() OVER(PARTITION BY subject ORDER BY score DESC) AS rnk, PERCENT_RANK() OVER(PARTITION BY subject ORDER BY score DESC) AS pct_rank FROM score; -- 执行结果数学科目4条记录组内总行数4 -- subject student_no score rnk pct_rank -- 数学 2024001 92 1 0.0000 -- (1-1)/(4-1)0 -- 数学 2024006 92 1 0.0000 -- (1-1)/(4-1)0 -- 数学 2024005 80 3 0.6667 -- (3-1)/(4-1)≈0.6667 -- 数学 2024002 75 4 1.0000 -- (4-1)/(4-1)13.3 偏移类窗口函数LAG() / LEAD()用于“获取当前行的前n行LAG或后n行LEAD的数据”无需自连接就能实现“连续数据对比”如环比、连续登录判断。语法LAG(字段名, n, 默认值) / LEAD(字段名, n, 默认值)n表示“偏移n行”默认值表示“无数据时返回的值”默认NULL。实战场景1查询每个学生的当前科目分数以及上一个科目、下一个科目的分数SELECT student_no, subject, score, LAG(score, 1, 0) OVER(PARTITION BY student_no ORDER BY subject) AS prev_score, -- 上一个科目分数 LEAD(score, 1, 0) OVER(PARTITION BY student_no ORDER BY subject) AS next_score -- 下一个科目分数 FROM score WHERE student_no 2024001; -- 只看张三的记录 -- 执行结果 -- student_no subject score prev_score next_score -- 2024001 数学 92 0 88 -- 2024001 语文 88 92 0 -- 2024001 英语 NULL 88 0实战场景2判断学生是否连续两天参加考试假设exam_date为考试日期-- 示例查询每个学生的考试日期判断是否连续 SELECT student_no, exam_date, LAG(exam_date, 1) OVER(PARTITION BY student_no ORDER BY exam_date) AS prev_date, DATEDIFF(exam_date, LAG(exam_date, 1) OVER(PARTITION BY student_no ORDER BY exam_date)) AS date_diff, CASE WHEN DATEDIFF(exam_date, LAG(exam_date, 1) OVER(PARTITION BY student_no ORDER BY exam_date)) 1 THEN 连续 ELSE 不连续 END AS is_continuous FROM score; -- 执行逻辑用LAG获取上一次考试日期计算两次日期差若差为1则连续 -- 执行结果假设张三两天连续考试 -- student_no exam_date prev_date date_diff is_continuous -- 2024001 2024-06-10 NULL NULL 不连续 -- 2024001 2024-06-11 2024-06-10 1 连续3.4 聚合窗口SUM() OVER() / AVG() OVER()将聚合函数SUM、AVG等与窗口函数结合用于“计算累计值、移动平均值”无需分组保留每行数据同时显示聚合结果。实战场景1计算每个科目的分数累计和SELECT subject, student_no, score, SUM(score) OVER(PARTITION BY subject ORDER BY score DESC) AS cumulative_sum FROM score; -- 执行结果数学科目 -- subject student_no score cumulative_sum -- 数学 2024001 92 92 -- 数学 2024006 92 184 -- 9292 -- 数学 2024005 80 264 -- 18480 -- 数学 2024002 75 339 -- 26475实战场景2计算每个学生的分数移动平均值前1行当前行后1行的平均SELECT student_no, subject, score, AVG(score) OVER(PARTITION BY student_no ORDER BY subject ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg FROM score WHERE student_no 2024001; -- 说明ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 表示“窗口范围为前1行、当前行、后1行” -- 执行结果 -- student_no subject score moving_avg -- 2024001 数学 92 90.0000 -- (9288)/2无前1行只取当前和后1行 -- 2024001 语文 88 88.0000 -- (9288NULL)/2NULL自动忽略取前1行和当前行 -- 2024001 英语 NULL 88.0000 -- (88NULL)/2无后1行只取前1行3.4.1 聚合窗口与普通聚合的区别很多人会混淆“聚合窗口函数”和“普通聚合函数”核心区别在于1. 普通聚合函数SUM、AVG等 GROUP BY会将分组后的数据合并为一行只返回聚合结果2. 聚合窗口函数 OVER()不会合并行保留每行原始数据同时在每行后追加聚合结果按窗口范围计算。-- 普通聚合GROUP BY按学生分组只返回每个学生的平均成绩一行一个学生 SELECT student_no, AVG(score) AS avg_score FROM score WHERE student_no 2024001 GROUP BY student_no; -- 聚合窗口保留每行成绩同时追加该学生的平均成绩一行一个科目 SELECT student_no, subject, score, AVG(score) OVER(PARTITION BY student_no) AS avg_score FROM score WHERE student_no 2024001;3.5 窗口函数的注意事项避坑重点窗口函数虽好用但使用时容易踩坑以下4个注意事项必须牢记1. 窗口函数只能用于 SELECT 和 ORDER BY 子句不能用于 WHERE、GROUP BY、HAVING 子句因为窗口函数是在这些子句执行之后才执行的-- 错误示例WHERE中使用窗口函数会报错 SELECT student_no, subject, score, ROW_NUMBER() OVER(PARTITION BY subject ORDER BY score DESC) AS row_num FROM score WHERE row_num 2; -- 正确示例先子查询获取窗口函数结果再在WHERE中过滤 SELECT * FROM ( SELECT student_no, subject, score, ROW_NUMBER() OVER(PARTITION BY subject ORDER BY score DESC) AS row_num FROM score ) t WHERE t.row_num 2;2. 窗口函数的执行顺序FROM → WHERE → GROUP BY → HAVING → SELECT窗口函数→ ORDER BY → LIMIT3. PARTITION BY 可省略省略后表示“整个结果集作为一个窗口”不分组对所有数据进行分析-- 省略PARTITION BY对所有成绩进行排名 SELECT student_no, subject, score, ROW_NUMBER() OVER(ORDER BY score DESC) AS global_row_num FROM score;4. MySQL 8.0 才支持窗口函数若使用5.7及以下版本需用子查询、自连接替代如TopN问题用子查询LIMIT。四、CTE公共表表达式简化复杂查询提升可读性CTECommon Table Expression公共表表达式是MySQL 8.0 新增的功能用于“定义临时结果集”可以替代复杂的子查询嵌套让SQL代码更简洁、可读性更高尤其适合多次复用同一子查询结果的场景。核心语法WITH 临时表名 AS (子查询)然后在后续查询中使用该临时表。4.1 基本用法单CTE示例用CTE查询每个学生的平均成绩筛选出平均成绩大于85分的学生替代表子查询-- 定义CTE临时表t存储每个学生的平均成绩 WITH t AS ( SELECT student_no, AVG(score) AS avg_score FROM score GROUP BY student_no ) -- 使用CTE和student表连接筛选 SELECT t.student_no, s.name, t.avg_score FROM t JOIN student s ON t.student_no s.student_no WHERE t.avg_score 85; -- 等价于之前的表子查询但代码更简洁可读性更高4.2 进阶用法多CTE、嵌套CTE1. 多CTE用逗号分隔多个CTE可相互引用前一个CTE可被后一个CTE使用2. 嵌套CTE在CTE内部再定义CTE适用于更复杂的查询场景。-- 多CTE示例查询每个科目分数前2名的学生信息结合CTE和窗口函数 WITH -- 第一个CTE给每个科目的分数排名 score_rank AS ( SELECT subject, student_no, score, ROW_NUMBER() OVER(PARTITION BY subject ORDER BY score DESC) AS row_num FROM score ), -- 第二个CTE筛选出每个科目前2名的记录 top2_score AS ( SELECT * FROM score_rank WHERE row_num 2 ) -- 使用第二个CTE关联student表获取学生信息 SELECT t.subject, t.student_no, s.name, s.class, t.score FROM top2_score t LEFT JOIN student s ON t.student_no s.student_no; -- 嵌套CTE示例在CTE内部嵌套CTE WITH parent_cte AS ( -- 内部嵌套CTE查询高一学生的学号 WITH child_cte AS ( SELECT student_no FROM student WHERE grade 高一 ) -- 父CTE使用子CTE的结果查询高一学生的成绩 SELECT sc.student_no, sc.subject, sc.score FROM score sc JOIN child_cte c ON sc.student_no c.student_no ) -- 使用父CTE统计高一学生各科目平均分 SELECT subject, AVG(score) AS avg_score FROM parent_cte GROUP BY subject;4.3 CTE与子查询、临时表的区别对比项CTE子查询临时表可读性高代码结构化可复用低嵌套过深易混乱中等需手动创建和删除复用性高可在同一查询中多次引用低每次使用需重复编写高可在整个会话中复用性能和子查询基本一致MySQL优化后嵌套过深会影响性能性能较好但创建和删除有开销选择原则简单查询用子查询复杂查询、需要复用临时结果集时优先用CTE简洁易维护需要跨会话复用时用临时表。