从多表查询到窗口函数让你的SQL水平再上一个台阶前言如果你已经掌握了基础的增删改查和简单的多表关联那么恭喜你——你已经是一名合格的SQL初学者了。但如果你想更进一步成为团队里那个SQL写得又快又好的人那就必须掌握今天要讲的这几个进阶技能✅自连接同一张表自己跟自己玩省市区三级联动全靠它✅子查询查询里面套查询解决复杂业务逻辑的利器✅窗口函数保留明细的同时做聚合计算数据分析必备✅CTE公共表达式让复杂SQL变得清晰可读本文基于真实教学笔记整理包含大量可直接运行的案例建议收藏遇到复杂查询时回来查阅一、自连接Self Join1.1 什么是自连接自连接就是一张表自己关联自己。左表和右表是同一张表只是起了不同的别名。为什么要用自连接因为有些数据存在层级关系比如省 → 市 → 区三级地区员工 → 经理上下级关系商品 → 父分类 → 子分类树形分类如果把这些层级数据放在同一张表里查子级的时候就需要自己连自己。1.2 场景地区表设计传统设计会把省、市、区分成三张表text省份表 市级表 区级表 ┌──────┐ ┌──────┐ ┌──────┐ │ 省id │ │ 市id │ │ 区id │ │ 省名 │ │ 市名 │ │ 区名 │ └──────┘ │ 省id │ │ 市id │ └──────┘ └──────┘但实际开发中通常会合并成一张表用pid父级ID来表示层级关系textareas 表一张表存所有省市区 ┌────┬──────────┬──────┐ │ id │ title │ pid │ ├────┼──────────┼──────┤ │ 1 │ 中国 │ 0 │ ← 根节点 │ 2 │ 山西省 │ 1 │ ← pid1 表示属于中国 │ 3 │ 太原市 │ 2 │ ← pid2 表示属于山西省 │ 4 │ 迎泽区 │ 3 │ ← pid3 表示属于太原市 └────┴──────────┴──────┘1.3 自连接语法sqlSELECT * FROM 表名 a -- 左表父级 JOIN 表名 b -- 右表子级 ON a.id b.pid; -- 连接条件⚠️关键点自连接必须起别名否则MySQL分不清哪边是哪边。1.4 实战案例练习1查询山西省下的所有市sqlSELECT * FROM areas a1 -- a1 代表省级数据 JOIN areas a2 -- a2 代表市级数据 ON a1.id a2.pid -- a1的id a2的父级id WHERE a1.title 山西省;练习2查询江苏省下的所有市自定义字段sqlSELECT a2.id AS 市id, a2.title AS 市名称, a1.id AS 省id, a1.title AS 省名称 FROM areas a1 JOIN areas a2 ON a1.id a2.pid WHERE a1.title 江苏省;练习3查询南京市下的所有区sqlSELECT * FROM areas a1 JOIN areas a2 ON a1.id a2.pid WHERE a1.title 南京市;练习4查询安徽省下的所有市及区县三级联动sqlSELECT * FROM areas a1 -- 省 JOIN areas a2 -- 市 ON a1.id a2.pid JOIN areas a3 -- 区 ON a2.id a3.pid WHERE a1.title 安徽省;1.5 自连接的应用场景场景示例地区层级省市区三级查询组织架构查询某部门下的所有子部门员工上下级查询某经理的所有下属商品分类查询某分类下的所有子分类二、子查询Subquery2.1 什么是子查询子查询就是在一个SELECT语句中嵌套了另一个SELECT语句。被嵌套的叫子查询外层的叫主查询。2.2 子查询的三种用法位置作用示例WHERE 后作为条件查询价格高于平均价的商品FROM 后作为临时表把子查询结果当成一张表来用SELECT 后作为查询字段直接返回子查询结果作为一列2.3 作为条件WHERE后sql-- 需求查询价格高于平均价的所有商品 SELECT * FROM products WHERE price (SELECT AVG(price) FROM products);案例1查询价格最高的商品sql-- ❌ 错误写法只能看到最高价看不到商品信息 SELECT MAX(price) FROM products; -- ✅ 正确写法子查询查出最高价再查商品 SELECT * FROM products WHERE price (SELECT MAX(price) FROM products);案例2查询河北省下的所有城市sql-- 第1步查出河北省的id子查询 SELECT id FROM areas WHERE title 河北省; -- 第2步查出所有pid 该id 的城市主查询 SELECT * FROM areas WHERE pid (SELECT id FROM areas WHERE title 河北省);案例3查询邯郸市下的所有区县sqlSELECT * FROM areas WHERE pid (SELECT id FROM areas WHERE title 邯郸市);2.4 作为临时表FROM后理解临时表永久表是超市的大仓库临时表是你手里的购物车。查询结束购物车就清空了。sql-- 需求查询每个分类的平均价格并关联分类名称 -- 第1步先求各分类的平均价子查询 SELECT category_id, AVG(price) AS 平均价 FROM products GROUP BY category_id; -- 第2步子查询结果作为临时表关联分类表 SELECT t2.id, t2.name, t1.平均价 FROM ( SELECT category_id, AVG(price) AS 平均价 FROM products GROUP BY category_id ) AS t1 JOIN category t2 ON t2.id t1.category_id;2.5 子查询 vs 自连接对比子查询自连接本质先算出中间结果再套到主查询同一张表分成两个角色进行关联适用场景查询结果作为条件或数据源同一张表有层级关系可读性嵌套多时容易混乱逻辑清晰易于理解三、MySQL常用内置函数3.1 数值函数函数作用示例ROUND(x, n)四舍五入保留n位小数ROUND(1.333, 2)→1.33CEIL(x)向上取整CEIL(2.1)→3FLOOR(x)向下取整FLOOR(1.5)→1MOD(x, y)取余MOD(5, 3)→2RAND()随机数0-1之间RAND()→0.123456sql-- 实战案例 -- 1. 计算 123.4567 保留2位小数 SELECT ROUND(123.4567, 2); -- 123.46 -- 2. 一个盒子装5个苹果17个苹果需要几个盒子 SELECT CEIL(17 / 5); -- 4 -- 3. 每页显示10条83条记录需要多少页 SELECT CEIL(83 / 10); -- 93.2 字符串函数函数作用示例UPPER()/LOWER()大小写转换UPPER(hello)→HELLOCONCAT()字符串拼接CONCAT(a,b)→abREPLACE()替换REPLACE(abc,b,x)→axcSUBSTR()截取SUBSTR(abcde,2,3)→bcdCHAR_LENGTH()字符个数CHAR_LENGTH(中国)→2sql-- 实战案例 SELECT UPPER(MySql String Functions); -- MYSQL STRING FUNCTIONS SELECT REVERSE(楚河汉界); -- 界汉河楚 SELECT CONCAT(王, 特大); -- 王特大 SELECT REPEAT(危险!, 3); -- 危险!危险!危险! SELECT CHAR_LENGTH(仙人指路); -- 43.3 日期函数函数作用示例NOW()当前日期时间NOW()→2024-01-15 14:30:00DATE_ADD()/DATE_SUB()日期加减DATE_ADD(2024-01-01, INTERVAL 10 DAY)DATEDIFF()天数差DATEDIFF(2024-01-10,2024-01-01)→9YEAR()/MONTH()/DAY()提取年月日YEAR(NOW())→2024DATE_FORMAT()日期格式化DATE_FORMAT(NOW(), %Y-%m-%d)sql-- 实战案例查询2020年登录过的每个用户的最后一次登录时间 SELECT user_id, MAX(time_stamp) AS last_time FROM logins WHERE YEAR(time_stamp) 2020 GROUP BY user_id;3.4 流程控制函数CASE WHEN条件分支sqlSELECT *, CASE WHEN score 90 THEN 优秀 WHEN score 80 THEN 良好 WHEN score 60 THEN 及格 ELSE 不及格 END AS level FROM tb_score;分组条件计数sql-- 统计各科目中 90 和 90 的人数 SELECT course, SUM(CASE WHEN score 90 THEN 1 ELSE 0 END) AS 优秀人数, SUM(CASE WHEN score 90 THEN 1 ELSE 0 END) AS 待提升人数 FROM tb_score GROUP BY course;IF 简化写法sql-- IF(条件, 值1, 值2) SELECT course, COUNT(IF(score 90, 1, NULL)) AS 优秀人数, COUNT(IF(score 90, 1, NULL)) AS 待提升人数 FROM tb_score GROUP BY course;四、窗口函数Window Functions窗口函数的核心价值保留每一行明细数据的同时还能看到聚合结果。4.1 OVER()——关联全表sql-- 需求计算每个学生的分数和整体平均分的差值 SELECT id, name, gender, score, AVG(score) OVER() AS 平均分, score - AVG(score) OVER() AS 差值 FROM students;4.2 PARTITION BY——分区计算sql-- 需求展示每人各科分数、科目最高分、占比 SELECT *, MAX(score) OVER(PARTITION BY course) AS 最高分, CONCAT(ROUND(score / MAX(score) OVER(PARTITION BY course) * 100, 2), %) AS 占比 FROM tb_score;4.3 GROUP BY vs PARTITION BY对比GROUP BYPARTITION BY输出行数分组后压缩行数多进一出保留所有行一进一出使用场景统计汇总报表保留明细的同时做运算配合函数聚合函数COUNT、SUM等窗口函数RANK、OVER等4.4 排名函数函数特点示例结果RANK()有并列时跳过名次1, 2, 2, 4DENSE_RANK()有并列时不跳过名次1, 2, 2, 3ROW_NUMBER()唯一编号不并列1, 2, 3, 4sql-- 按科目排名连续可重复 SELECT name, course, score, DENSE_RANK() OVER( PARTITION BY course ORDER BY score DESC ) AS 排名 FROM tb_score;4.5 CTE公共表达式CTECommon Table Expression就是给子查询起个名字让SQL更清晰。sql-- 需求获取每个科目排名第2的学生 WITH tb_tmp AS ( SELECT name, score, course, DENSE_RANK() OVER( PARTITION BY course ORDER BY score DESC ) AS 排名 FROM tb_score ) SELECT name, score, course FROM tb_tmp WHERE 排名 2;五、知识速查表自连接 vs 子查询 vs 窗口函数技术核心作用适用场景自连接同一张表自己关联自己层级数据省市区、上下级子查询WHERE后查询结果作为条件比较值、存在性检查子查询FROM后查询结果作为临时表多步聚合、中间结果复用窗口函数保留明细聚合计算排名、占比、累计、差值窗口函数速查关键字作用OVER()关联全表数据OVER(PARTITION BY 列)按指定列分区OVER(ORDER BY 列)按指定列排序RANK()排名跳号DENSE_RANK()排名不跳号ROW_NUMBER()唯一行号六、总结本文系统讲解了MySQL进阶查询的四大核心技能自连接同一张表自己关联自己解决层级数据查询子查询查询嵌套查询灵活处理复杂业务逻辑内置函数数值、字符串、日期、流程控制让数据处理更高效窗口函数保留明细的同时做聚合计算数据分析利器一句话建议自连接管层级子查询管嵌套函数管处理窗口管分析——四者结合SQL无难事如果这篇文章对你有帮助欢迎点赞 收藏⭐ 转发有疑问欢迎在评论区留言我会第一时间回复