SQL单表多表各四套题单：从WHERE到窗口函数与JOIN实战

简介这份SQL练习资源面向MySQL初学者与希望巩固查询能力的开发者围绕单表与多表两大主题各提供四套练习题帮助读者在动手实践中理解SELECT、WHERE、ORDER BY、GROUP BY及聚合函数等基础用法并进一步掌握INNER JOIN、LEFT JOIN、子查询等多表关联技巧。资源包共19个文件以13个txt文本、5个sql脚本和1个zip压缩包为主sql文件可直接导入MySQL环境执行txt多用于题目说明与答案对照整体约22KB轻量便于携带。目前已有915人学习下载说明其在SQL入门练习场景中具有一定参考价值。通过逐套完成单表过滤、排序、分组统计与多表联接、嵌套查询等题目读者可系统梳理SQL语句的执行逻辑积累从建库建表到复杂查询的完整实操经验适合作为日常刷题与查漏补缺的练习材料。1. 从一张员工表和一张订单表说起为什么单表练完还是写不出多表很多人学 SQL 的路径都差不多装好数据库打开 SSMS 或者 Navicat对着教程把SELECT * FROM 表名敲一遍再练几个WHERE、ORDER BY、GROUP BY感觉自己会了。结果一到真实场景——比如「查出每个部门工资最高的那个人」「统计每个客户最近一笔订单的金额」——立刻卡住不知道从哪张表下手JOIN写出来结果条数对不上GROUP BY一加就报错。问题不在语法在于练习的颗粒度。单表查询练的是「过滤、排序、聚合」这套肌肉记忆多表查询练的是「关系代数 执行顺序」这套思维方式两者需要的题单结构完全不同。标题里的「单表多表各四套」本质上是把这两类能力拆开各用四组递进式题目打透单表四套覆盖WHERE条件组合、聚合函数、GROUP BY分组、窗口函数多表四套覆盖INNER JOIN、LEFT JOIN、自连接、子查询与EXISTS。这篇文章不空谈「SQL 很重要」而是把八套题单的设计逻辑、每套题该练什么、参考答案怎么写、跑出来结果不对时怎么排查一条条讲清楚。适合已经能写基本SELECT、但一遇到多表就发怵的从业者也适合想给自己团队出一套内部 SQL 训练题的工程师。下面从题单怎么设计开始一路讲到窗口函数和慢 SQL 排查。2. 单表四套题单怎么设计从 WHERE 到窗口函数的递进路径单表题单最容易犯的错是四套题难度差不多全是WHERE加几个AND。这样练完聚合和窗口函数还是不会。合理的做法是让四套题各自锁定一个能力层前一套的产出是后一套的输入。2.1 第一套WHERE 条件组合与 NULL 的坑第一套题只练过滤但要把AND、OR、IN、BETWEEN、LIKE、IS NULL混在一起逼你处理优先级和空值。建一张员工表-- 建表员工表故意留一些 NULL 用来练空值判断 CREATE TABLE emp ( emp_id INT PRIMARY KEY, emp_name VARCHAR(50) NOT NULL, dept VARCHAR(30), salary DECIMAL(10,2), hire_date DATE, bonus DECIMAL(10,2) -- 允许为 NULL表示没有奖金 ); INSERT INTO emp VALUES (1,张伟,研发,18000,2020-03-01,5000), (2,李娜,研发,22000,2019-07-15,NULL), (3,王强,销售,12000,2021-01-10,8000), (4,赵敏,销售,15000,2018-11-20,NULL), (5,孙磊,市场,9000,2022-05-06,3000), (6,周洁,市场,11000,2020-09-01,NULL);第一套的典型题目查出「研发或销售部门中工资大于 12000 且没有奖金的员工」。这里有两个坑一是AND优先级高于OR必须加括号二是bonus NULL永远不成立必须写IS NULL。-- 正确写法括号保证 OR 的范围IS NULL 判断空值 SELECT emp_name, dept, salary FROM emp WHERE (dept 研发 OR dept 销售) AND salary 12000 AND bonus IS NULL;逻辑说明WHERE子句按行过滤AND先于OR求值不加括号会变成「研发部门全部或者销售部门中满足后两个条件的」结果多出研发的人。参数上salary 12000是数值比较bonus IS NULL是空值判断两者不能互换。跑出来如果发现研发的低薪员工也出现了就是括号漏了。提示NULL参与任何算术运算结果还是NULLNULL 1不等于 1写聚合时要特别注意。2.2 第二套聚合函数与 GROUP BY 的执行顺序第二套练聚合核心是理解「先分组、再聚合、最后过滤」这个顺序。题目比如「统计每个部门的平均工资、最高工资、人数只保留平均工资大于 12000 的部门」。-- 分组聚合HAVING 过滤分组后的结果WHERE 过滤分组前的行 SELECT dept, COUNT(*) AS 人数, AVG(salary) AS 平均工资, MAX(salary) AS 最高工资 FROM emp GROUP BY dept HAVING AVG(salary) 12000;逻辑说明GROUP BY dept把行按部门分成若干组COUNT、AVG、MAX在每组内计算HAVING在分组之后过滤。参数上COUNT(*)统计组内行数COUNT(bonus)只统计非空值两者结果可能不同。常见翻车点是把条件写进WHERE比如WHERE AVG(salary) 12000这会直接报错因为WHERE执行时还没有聚合结果。2.3 第三套子查询与相关子查询第三套练子查询分两种不相关子查询子查询独立执行一次和相关子查询子查询依赖外层每一行。题目比如「查出工资高于全公司平均工资的员工」。-- 不相关子查询子查询先算出全局平均外层再比较 SELECT emp_name, salary FROM emp WHERE salary (SELECT AVG(salary) FROM emp); -- 相关子查询查出每个部门工资最高的员工 SELECT emp_name, dept, salary FROM emp e WHERE salary ( SELECT MAX(salary) FROM emp WHERE dept e.dept );逻辑说明第一条子查询只执行一次返回一个标量外层拿它做比较。第二条子查询对外层每一行都执行一次e.dept是外层当前行的部门所以能实现「组内最大」。参数上相关子查询性能通常更差数据量大时要考虑改成窗口函数。跑出来如果每个部门返回多个人说明该部门有并列最高工资这是正常的不是 bug。2.4 第四套窗口函数一次算清排名与累计第四套上窗口函数这是单表练习里最容易被跳过、但实际工作最常用的部分。题目比如「给每个部门内部按工资排名并算出累计工资」。-- 窗口函数PARTITION BY 分组ORDER BY 排序不减少行数 SELECT emp_name, dept, salary, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS 部门排名, SUM(salary) OVER (PARTITION BY dept ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 累计工资 FROM emp;逻辑说明PARTITION BY dept把数据按部门分区ORDER BY salary DESC决定排名顺序RANK()遇到并列会跳号DENSE_RANK()不跳号。累计工资用ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW定义窗口范围从分区第一行累加到当前行。参数上ROWS和RANGE的区别在于并列行的处理默认RANGE会把并列行算进同一窗口容易和预期不符建议显式写ROWS。注意窗口函数在WHERE、GROUP BY、HAVING之后执行所以不能直接在WHERE里用RANK()要套一层子查询或 CTE。3. 多表四套题单怎么设计JOIN、自连接与 EXISTS 的实战边界多表题单的核心不是「会写 JOIN」而是「知道该用哪种 JOIN、结果条数为什么对不上」。四套题分别锁定INNER JOIN、LEFT JOIN、自连接、EXISTS与子查询每套都要有能暴露笛卡尔积和空值问题的题目。3.1 第一套INNER JOIN 与多表关联的字段歧义先建两张表部门表和员工表员工表里存部门 ID。-- 部门表 CREATE TABLE dept ( dept_id INT PRIMARY KEY, dept_name VARCHAR(30) ); INSERT INTO dept VALUES (10,研发),(20,销售),(30,市场),(40,财务); -- 员工表dept_id 关联 dept 表 CREATE TABLE employee ( emp_id INT PRIMARY KEY, emp_name VARCHAR(50), dept_id INT, salary DECIMAL(10,2) ); INSERT INTO employee VALUES (1,张伟,10,18000), (2,李娜,10,22000), (3,王强,20,12000), (4,赵敏,20,15000), (5,孙磊,30,9000), (6,周洁,30,11000), (7,吴昊,NULL,8000); -- 没有部门第一套题目「查出每个员工的姓名、部门名称、工资」。用INNER JOIN-- INNER JOIN只返回两表都匹配上的行 SELECT e.emp_name, d.dept_name, e.salary FROM employee e INNER JOIN dept d ON e.dept_id d.dept_id;逻辑说明ON e.dept_id d.dept_id是连接条件只有两边都匹配的行才出现。参数上INNER JOIN可以简写为JOIN效果一样。跑出来只有 6 行吴昊因为dept_id是NULL被排除这是INNER JOIN的正常行为。常见坑是连接条件写错字段比如写成e.emp_id d.dept_id结果会变成笛卡尔积的一部分条数暴涨。3.2 第二套LEFT JOIN 与「查没有」的经典写法第二套练LEFT JOIN重点在「保留左表全部行」和「查左表有、右表没有的记录」。题目「查出所有员工包括没有部门的部门名称为空时显示未分配」。-- LEFT JOIN左表全部保留右表没匹配的补 NULL SELECT e.emp_name, COALESCE(d.dept_name, 未分配) AS 部门 FROM employee e LEFT JOIN dept d ON e.dept_id d.dept_id;逻辑说明LEFT JOIN以左表employee为准吴昊也会出现d.dept_name为NULL用COALESCE转成「未分配」。参数上COALESCE返回第一个非空参数也可以用ISNULLSQL Server或IFNULLMySQL。经典坑是把右表的过滤条件写在WHERE里比如WHERE d.dept_name 研发这会把LEFT JOIN退化成INNER JOIN因为NULL不满足等值条件。正确做法是把条件放到ON后面。-- 查「有员工但没在部门表登记的 dept_id」左连接 右表主键 IS NULL SELECT DISTINCT e.dept_id FROM employee e LEFT JOIN dept d ON e.dept_id d.dept_id WHERE d.dept_id IS NULL AND e.dept_id IS NOT NULL;3.3 第三套自连接与层级数据的处理第三套练自连接典型场景是员工和经理在同一张表里。给employee加一列manager_id-- 加经理列经理也是员工 ALTER TABLE employee ADD manager_id INT; UPDATE employee SET manager_id 1 WHERE emp_id IN (2,3); UPDATE employee SET manager_id 3 WHERE emp_id IN (4,5);题目「查出每个员工及其经理的姓名」。-- 自连接同一张表起两个别名分别代表员工和经理 SELECT e.emp_name AS 员工, m.emp_name AS 经理 FROM employee e LEFT JOIN employee m ON e.manager_id m.emp_id;逻辑说明employee出现两次用e和m区分角色连接条件是「员工的经理 ID 经理的员工 ID」。用LEFT JOIN是为了让没有经理的顶层员工也出现。参数上自连接必须起别名否则字段引用会有歧义。跑出来如果经理列大量为空先检查manager_id是否真的更新成功UPDATE的WHERE条件写错是高频翻车点。3.4 第四套EXISTS、IN 与多表子查询的取舍第四套练EXISTS和IN重点是理解两者语义差异和性能差异。题目「查出至少有一名员工工资大于 15000 的部门」。-- EXISTS相关子查询只要存在一行就返回 true SELECT d.dept_name FROM dept d WHERE EXISTS ( SELECT 1 FROM employee e WHERE e.dept_id d.dept_id AND e.salary 15000 ); -- IN先算出子查询结果集再判断是否在其中 SELECT dept_name FROM dept WHERE dept_id IN ( SELECT dept_id FROM employee WHERE salary 15000 );逻辑说明EXISTS对外层每一行执行子查询一旦找到匹配就短路返回适合子查询结果集大的场景。IN先执行子查询得到结果集再逐行判断子查询结果集小的时候更直观。参数上IN的子查询如果返回NULL整个条件可能不成立这是经典陷阱EXISTS不受NULL影响。跑出来两者结果应该一致如果不一致优先检查子查询里有没有NULL。提示NOT IN遇到子查询结果含NULL时整个条件永远为假查不出任何行。需要「查不存在」时优先用NOT EXISTS或LEFT JOIN ... IS NULL。4. 八套题单跑不通时的排查清单从报错到结果对不上的定位方法题单设计得再好跑的时候照样会翻车。这一章按「现象 → 原因 → 解决」整理五条高频问题都是我在带人练题时反复见到的。4.1 现象GROUP BY 报「列未包含在聚合函数中」原因SELECT列表里出现了既不在GROUP BY中、也没被聚合函数包住的列。比如SELECT dept, emp_name, AVG(salary) FROM emp GROUP BY deptemp_name既没分组也没聚合数据库不知道每个部门该显示哪个员工名。解决要么把emp_name加进GROUP BY要么用聚合函数包起来如MAX(emp_name)要么改用窗口函数。标准 SQL 要求严格MySQL 在宽松模式下可能不报错但结果随机别依赖这个行为。4.2 现象JOIN 之后行数比预期多很多原因连接条件不唯一产生了笛卡尔积。比如员工表和部门表连接时部门表里同一个dept_id有多行或者连接条件写成了非唯一字段。解决先单独跑SELECT COUNT(*) FROM 左表和SELECT COUNT(*) FROM 右表再跑连接后的COUNT(*)。如果连接后行数远大于左表检查ON条件是否唯一。可以在连接前对右表按连接键去重或者确认业务上是否真的存在一对多。4.3 现象LEFT JOIN 结果和 INNER JOIN 一样原因把右表的过滤条件写在了WHERE里。WHERE d.dept_name 研发会把右表为NULL的行过滤掉LEFT JOIN退化成INNER JOIN。解决把右表的过滤条件移到ON子句。如果确实要过滤右表但保留左表全部行用ON d.dept_name 研发 OR d.dept_name IS NULL或者用子查询先过滤右表再连接。4.4 现象窗口函数结果和 GROUP BY 混用时报错原因窗口函数和GROUP BY同时出现时执行顺序是GROUP BY先聚合窗口函数在聚合结果上再计算。如果SELECT里既有聚合列又有窗口函数容易混淆。解决把聚合和窗口函数分层用 CTE 先做聚合再在外层用窗口函数。-- 先聚合到部门级别再用窗口函数排名 WITH dept_avg AS ( SELECT dept_id, AVG(salary) AS avg_sal FROM employee GROUP BY dept_id ) SELECT dept_id, avg_sal, RANK() OVER (ORDER BY avg_sal DESC) AS 排名 FROM dept_avg;4.5 现象慢 SQL 跑几分钟不出结果原因多表关联时没有走索引或者子查询被反复执行。相关子查询在大表上尤其致命外层每一行都要跑一次子查询。解决先看执行计划确认连接字段有没有索引。把相关子查询改写成JOIN或窗口函数让数据库只扫一遍。数据量大的时候EXISTS通常比IN快但前提是连接字段有索引。别一上来就加WITH (NOLOCK)那是掩盖问题不是解决问题。5. 把八套题单变成长期能力用执行计划和结果集自检题单练完不是终点能自己出题、自己验证才算真的会了。我一般用两个习惯收尾一是每写完一条查询先跑COUNT(*)看行数是否符合业务预期二是对多表查询看执行计划确认没有全表扫描和嵌套循环失控。一个具体的进阶技巧用EXPLAINMySQL或「显示估计的执行计划」SQL Server对比IN和EXISTS的差异。下面这条查询在员工表数据量大时EXISTS通常能提前短路-- 对比两种写法的执行计划重点看 type 和 rows EXPLAIN SELECT d.dept_name FROM dept d WHERE EXISTS ( SELECT 1 FROM employee e WHERE e.dept_id d.dept_id AND e.salary 15000 );逻辑说明EXPLAIN输出里type列如果是ALL表示全表扫描ref或eq_ref表示走了索引rows列是预估扫描行数越小越好。参数上EXISTS子查询里的e.dept_id d.dept_id如果dept_id有索引数据库能快速定位没索引就会退化成逐行扫描。我自己的习惯是每套题单至少留一道「故意写错」的题比如把LEFT JOIN的条件写进WHERE然后观察结果差异。这种对比比看十遍文档都管用。八套题单不用一次刷完单表四套打基础多表四套练关系思维中间穿插执行计划自检两周下来多表查询基本不会再发怵。希望帮到你。本文还有配套的精品资源点击获取