SQL笔试核心考点解析：从JOIN原理到窗口函数实战-北京尧图网络

1. 从“背答案”到“懂原理”一份不一样的SQL笔试通关手册又到了招聘季后台和社群里关于SQL笔试的咨询又多了起来。大家发来的题目五花八门从基础的增删改查到复杂的窗口函数、性能调优甚至还有结合业务场景的案例分析。我发现一个普遍现象很多朋友尤其是刚入行或准备转行的同学面对SQL笔试第一反应是去网上找“经典题目及答案整理”然后开始死记硬背。这种做法在应付一些极其基础的题目时或许有效但一旦遇到稍微灵活、需要理解底层原理或结合具体业务逻辑的题目就很容易“翻车”。我做了十多年数据相关的工作从写SQL脚本到设计数据仓库也面试过上百位候选人。我可以负责任地说面试官想考察的绝不仅仅是你能否默写出某个JOIN的语法。他们真正想看到的是你对数据关系的理解、对查询逻辑的拆解能力以及写出高效、清晰、可维护的SQL代码的思维习惯。今天我就抛开那些简单的“题目-答案”对照表带你从几个最常见的、也是笔试中最容易失分的核心考点入手深入聊聊背后的“为什么”并分享一些我实战中总结的避坑经验和解题思路。我们的目标不是背会几道题而是建立一套应对绝大多数SQL笔试问题的思维框架。2. 联表查询JOIN的“灵魂三问”与性能陷阱几乎所有SQL笔试都绕不开JOIN联表查询但很多人只记住了INNER JOIN,LEFT JOIN这几个关键词却说不清它们的本质区别和应用场景。这就像你知道螺丝刀能拧螺丝却分不清一字和十字的区别遇到特殊螺丝就束手无策。2.1 超越语法理解JOIN的集合论本质首先我们必须从集合的角度理解JOIN。假设我们有两张表Employees员工表含emp_id,name,dept_id和Departments部门表含dept_id,dept_name。INNER JOIN内连接求的是两个表的交集。结果集中只包含那些在Employees表中有dept_id并且这个dept_id在Departments表中也存在记录的行。如果一个员工属于一个不存在的部门dept_id在Departments表里没有或者一个部门还没有任何员工那么这些记录都不会出现在结果中。-- 查询所有有明确部门的员工及其部门信息 SELECT e.name, d.dept_name FROM Employees e INNER JOIN Departments d ON e.dept_id d.dept_id;笔试常见坑点题目可能描述为“列出所有员工及其所属部门”粗心的同学会直接用INNER JOIN。但如果存在“未分配部门”的员工dept_id为NULL他们就会被漏掉。这时必须用LEFT JOIN。LEFT JOIN左外连接以左表Employees为基准返回左表的所有行即使右表Departments中没有匹配的行。如果右表没有匹配则结果集中右表的部分全部为NULL。-- 查询所有员工以及他们所属的部门信息如果没有部门部门信息显示为NULL SELECT e.name, d.dept_name FROM Employees e LEFT JOIN Departments d ON e.dept_id d.dept_id;核心思考LEFT JOIN的关键在于明确“主表”是谁。你要的结果是以哪个表的记录为“全集”。FULL OUTER JOIN全外连接返回两个表的并集。只要某个记录在左表或右表中存在就会出现在结果集里。缺失匹配的部分用NULL填充。需要注意的是MySQL不直接支持FULL OUTER JOIN但可以通过LEFT JOIN UNION RIGHT JOIN来模拟。-- 查询所有员工和所有部门展示其对应关系 SELECT e.name, d.dept_name FROM Employees e LEFT JOIN Departments d ON e.dept_id d.dept_id UNION SELECT e.name, d.dept_name FROM Employees e RIGHT JOIN Departments d ON e.dept_id d.dept_id;实操心得拿到一道JOIN相关的题不要急着写先问自己这三个问题结果集以哪个表为基准决定用LEFT/RIGHT还是INNER如果匹配不上我需要保留哪边的数据决定NULL值出现在哪一侧是否存在一对多或多对多的关系这关系到结果集的行数是否会膨胀以及是否需要使用DISTINCT去重2.2 性能陷阱ON条件与WHERE条件的执行顺序之谜这是一个高频考点也是实际工作中容易写出低效SQL的地方。看下面两个查询它们的结果一样吗-- 查询A SELECT e.name, d.dept_name FROM Employees e LEFT JOIN Departments d ON e.dept_id d.dept_id AND d.dept_name 技术部; -- 查询B SELECT e.name, d.dept_name FROM Employees e LEFT JOIN Departments d ON e.dept_id d.dept_id WHERE d.dept_name 技术部;答案是完全不一样查询A在LEFT JOIN的ON子句中过滤。它的逻辑是“将员工表和部门表连接连接条件除了dept_id匹配还要求部门的名称必须是‘技术部’”。对于左表员工的每一行它会去右表找同时满足这两个条件的行。如果找不到右表字段依然为NULL但左表该员工的记录会被保留。所以结果会列出所有员工但只有部门是“技术部”的员工才有部门信息其他员工的dept_name为NULL。查询B在WHERE子句中过滤。它的逻辑是“先进行左连接保留所有员工得到一个中间结果集然后再从这个大的结果集中筛选出dept_name 技术部的行”。这里有一个关键点WHERE条件会对LEFT JOIN产生的NULL值进行判断。那些没有匹配到部门的员工其dept_name本身就是NULLNULL 技术部这个条件的结果是UNKNOWN在SQL中被视为FALSE因此这些行会被过滤掉。最终查询B的结果等价于一个INNER JOIN只返回属于“技术部”的员工。避坑指南记住这个原则——ON条件用于定义表之间的连接关系发生在连接JOIN的过程中WHERE条件用于对连接后形成的整个结果集进行过滤发生在连接之后。在写LEFT JOIN时如果你希望过滤右表但又不丢失左表记录把条件放在ON里如果你希望过滤最终结果并且不关心左表是否被保留就把条件放在WHERE里。笔试中经常通过互换ON和WHERE的条件来考察你对连接过程的理解深度。3. 聚合与分组GROUP BY的“静默杀手”与HAVING的妙用GROUP BY是数据分析的利器但也是错误的重灾区。最常见的错误就是SELECT的字段列表与GROUP BY的分组字段不匹配。3.1 “列必须出现在GROUP BY中”的真正含义考虑一个订单表Orders(order_id, customer_id, product_id, amount, order_date)。一个经典问题是“计算每个客户的总消费金额”。-- 错误写法在某些数据库如MySQL的宽松模式下可能不出错但逻辑错误 SELECT customer_id, product_id, SUM(amount) as total_spent FROM Orders GROUP BY customer_id;这个查询在严格模式的SQL如PostgreSQL、新版本MySQL的ONLY_FULL_GROUP_BY模式中会直接报错。为什么因为product_id没有包含在GROUP BY子句中也没有被聚合函数如SUM, AVG, MAX等包裹。当按照customer_id分组后一个客户可能对应多条订单记录多个product_id数据库无法确定在结果集的这一行里应该显示哪一个product_id。正确写法-- 正确写法1只显示分组字段和聚合结果 SELECT customer_id, SUM(amount) as total_spent FROM Orders GROUP BY customer_id; -- 正确写法2如果想看到具体产品则需要按customer_id和product_id共同分组 SELECT customer_id, product_id, SUM(amount) as total_spent FROM Orders GROUP BY customer_id, product_id;笔试技巧遇到GROUP BY的题目先在脑子里或草稿上画一下按照我指定的字段分组后同一组内的其他字段是不是都变成了“一对多”的关系如果是那么这些“多值字段”就不能直接SELECT必须用聚合函数把它们变成一个值如求和、求平均、取最大值或者把它们也加到GROUP BY里。3.2 HAVING聚合后的“守门人”WHERE和HAVING的区别是另一个必考点。简单说WHERE在分组前过滤行HAVING在分组后过滤组。继续用上面的订单表问题升级“找出总消费金额超过1000元的客户”。-- 错误不能在WHERE中使用聚合函数SUM(amount) SELECT customer_id, SUM(amount) as total_spent FROM Orders WHERE SUM(amount) 1000 GROUP BY customer_id; -- 正确使用HAVING对分组后的结果进行过滤 SELECT customer_id, SUM(amount) as total_spent FROM Orders GROUP BY customer_id HAVING SUM(amount) 1000;WHERE SUM(amount) 1000之所以错误是因为在执行WHERE子句时数据库还没有进行分组和聚合计算它不知道SUM(amount)是多少。HAVING子句则是在GROUP BY生成分组聚合结果之后才执行的所以它可以基于聚合值进行过滤。一个实用的记忆口诀“WHERE管行HAVING管组WHERE不能用聚合HAVING专门筛聚合”。4. 子查询与窗口函数从“绕弯子”到“一步到位”对于复杂查询初学者倾向于写多层嵌套的子查询Subquery而高手则会优先考虑使用更清晰、性能往往更好的窗口函数Window Function。笔试中能否使用窗口函数优雅地解题是区分能力等级的重要标志。4.1 子查询的典型场景与性能考量子查询主要分两类标量子查询返回单个值和行/表子查询返回一个集合。场景一作为计算字段标量子查询问题“在员工表旁边显示该员工所在部门的平均工资。”SELECT e.emp_id, e.name, e.salary, e.dept_id, (SELECT AVG(salary) FROM Employees WHERE dept_id e.dept_id) as dept_avg_salary FROM Employees e;这种关联子查询子查询引用了外部查询的字段e.dept_id对于每一行外部记录都要执行一次在数据量大时性能很差。更好的方式是用JOIN配合GROUP BY。场景二作为过滤条件IN, EXISTS问题“找出从来没有下过订单的客户。”-- 使用 NOT IN SELECT customer_id, name FROM Customers WHERE customer_id NOT IN (SELECT DISTINCT customer_id FROM Orders); -- 使用 NOT EXISTS (通常性能更好特别是当子查询结果可能包含NULL时) SELECT customer_id, name FROM Customers c WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.customer_id c.customer_id);NOT EXISTS在很多数据库优化器中效率更高因为它一旦在子查询中找到一条匹配记录就会停止搜索。而NOT IN需要处理完整的子查询结果集且如果子查询结果中有NULL值整个NOT IN条件会返回未知UNKNOWN导致查不出任何结果这是一个巨坑4.2 窗口函数的降维打击窗口函数能在不减少行数的情况下进行聚合、排序等计算功能强大。看一个经典笔试/面试题“计算每个部门内员工的工资排名以及该员工工资与部门平均工资的差值。”用子查询和JOIN的“绕弯子”写法SELECT e.emp_id, e.name, e.dept_id, e.salary, -- 排名需要通过自连接或相关子查询实现非常繁琐 (SELECT COUNT(DISTINCT e2.salary) FROM Employees e2 WHERE e2.dept_id e.dept_id AND e2.salary e.salary) as dept_rank, -- 计算与平均值的差 e.salary - dept_avg.avg_salary as diff_from_avg FROM Employees e JOIN (SELECT dept_id, AVG(salary) as avg_salary FROM Employees GROUP BY dept_id) dept_avg ON e.dept_id dept_avg.dept_id ORDER BY e.dept_id, dept_rank;用窗口函数的“一步到位”写法SELECT emp_id, name, dept_id, salary, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as dept_rank, salary - AVG(salary) OVER (PARTITION BY dept_id) as diff_from_avg FROM Employees ORDER BY dept_id, dept_rank;高下立判窗口函数OVER (PARTITION BY dept_id ...)定义了数据窗口的范围按部门分区RANK()和AVG()在这个窗口内进行计算。代码简洁逻辑清晰而且执行效率通常更高。必须掌握的窗口函数排序ROW_NUMBER()连续唯一序号,RANK()并列会跳跃,DENSE_RANK()并列不跳跃聚合SUM(),AVG(),MAX(),MIN(),COUNT()配合OVER分布NTILE(n)分桶前后值LAG(column, n)前n行,LEAD(column, n)后n行在笔试中如果题目涉及“分组内排序”、“累计计算”、“移动平均”、“Top N问题”第一时间就要想到窗口函数。5. 索引与性能优化写出让面试官眼前一亮的SQL对于中高级岗位的笔试经常会出现简单的表结构然后问“如何优化这条SQL”或“在哪些列上建立索引能提升查询性能”这考察的是你对数据库底层工作原理的理解。5.1 索引如何工作一个简单的类比可以把数据库表想象成一本书数据就是书的内容。如果没有索引目录你要找某个特定主题比如“窗口函数”只能一页一页翻全表扫描。而索引就像书后面的索引页它按字母顺序列出了关键词和对应的页码。通过查索引你能快速定位到关键词所在的页面。数据库索引如B-Tree索引也是类似原理。它创建了一个独立的数据结构存储了索引列的值和指向实际数据行的指针。当执行WHERE dept_id 10这样的查询时数据库会先去索引中快速找到dept_id10的所有指针然后根据指针去取数据避免了扫描整张表。5.2 索引最左前缀原则与失效场景这是笔试和面试的绝对高频考点。假设我们在Orders(customer_id, order_date, status)表上建立了一个复合索引idx_cus_date_status (customer_id, order_date, status)。哪些查询能用上这个索引WHERE customer_id 100能用匹配最左列。WHERE customer_id 100 AND order_date 2023-01-01能用匹配最左列和次左列。WHERE customer_id 100 AND status SHIPPED能用但只用到customer_id因为跳过了order_date索引对status的查找就失效了只能基于customer_id过滤后再扫描过滤出的数据行来匹配status。WHERE order_date 2023-01-01可能用不上或效率低没有从最左列customer_id开始数据库可能选择全表扫描而不走这个索引。WHERE customer_id 100 AND order_date 2023-01-01 AND status SHIPPED能用完美匹配三列。索引失效的常见场景在索引列上使用函数或计算WHERE YEAR(order_date) 2023会导致索引失效。应改为WHERE order_date 2023-01-01 AND order_date 2024-01-01。使用!或NOT IN大多数情况下无法有效利用索引。使用OR连接多个条件如果OR两边的列不是同一个索引索引可能失效。WHERE customer_id 100 OR status SHIPPED如果只有idx_cus_date_status索引这个查询用不上索引对status的查找。列类型不匹配如果customer_id是字符串类型但查询写成了WHERE customer_id 100数字会发生隐式类型转换导致索引失效。模糊查询以通配符开头LIKE %keyword%无法使用索引但LIKE keyword%可以使用。笔试答题思路当被问到如何优化或设计索引时遵循以下步骤分析查询的WHERE子句和JOIN条件哪些是高频的过滤条件考虑复合索引将最常用、区分度最高唯一值多的列放在最左边。考虑覆盖索引如果查询只需要返回索引中包含的列数据库可以直接从索引中取数据避免回表去主键索引查数据行这是极大的性能提升。例如SELECT customer_id, order_date FROM Orders WHERE customer_id 100如果索引是(customer_id, order_date)这就是一个覆盖索引查询。警惕索引的代价索引会占用空间并降低INSERT、UPDATE、DELETE的速度因为数据变更时需要同步更新索引。不是越多越好。6. 事务与并发控制ACID不是四个字母那么简单对于后端开发或数据工程师岗位笔试中可能会出现关于事务隔离级别、锁、并发问题脏读、不可重复读、幻读的题目。这需要理解概念而不是死记硬背。6.1 并发问题的生动比喻假设有一个银行账户表Accounts(id, balance)初始余额为1000元。脏读Dirty Read事务A修改了余额比如500变成1500但还没提交。事务B这时读取余额读到了1500。然后事务A回滚了余额变回1000。事务B读到的就是一个根本不存在的数据“脏数据”。不可重复读Non-repeatable Read事务B第一次读取余额是1000。此时事务A提交了一个更新将余额改为500。事务B再次读取余额发现变成了500。同一个事务内两次读取同一行数据结果不一致。幻读Phantom Read事务B查询“余额大于800的账户”返回了id1的账户。此时事务A插入了一个新账户id2余额900并提交。事务B再次执行同样的查询结果返回了两条记录id1和id2。同一个事务内两次相同的范围查询返回的记录数不一致。6.2 隔离级别如何解决这些问题SQL标准定义了四种隔离级别从宽松到严格读未提交Read Uncommitted什么都不防。可能发生脏读、不可重复读、幻读。读已提交Read Committed防止脏读。这是Oracle等数据库的默认级别。但可能发生不可重复读和幻读。可重复读Repeatable Read防止脏读和不可重复读。这是MySQL InnoDB的默认级别。通过“快照读”MVCC多版本并发控制来实现。在同一个事务中多次读取同一行数据看到的是事务开始时的快照版本。但可能发生幻读InnoDB通过间隙锁在一定程度上防止了幻读。串行化Serializable最高级别完全串行执行事务防止所有并发问题。但性能最差。笔试答题要点能清楚解释每种隔离级别能防止什么问题不能防止什么问题。知道常见数据库的默认隔离级别。理解“可重复读”通常通过MVCC实现而“串行化”通过加锁实现。7. 实战案例拆解一道综合笔试题的完整思考过程最后我们用一个稍微综合的题目把前面的知识点串起来。题目描述“有一张销售记录表 sales(sale_id, product_id, sale_date, amount)请写出SQL找出每个月销售额均超过该月所有产品平均销售额的产品ID。”第一步拆解问题需要按月统计。需要计算每个月的产品平均销售额。需要找出那些在每个其有销售的月份里销售额都大于当月平均额的产品。注意“每个月”和“均超过”这两个关键词意味着产品不能有任何一个月不达标。第二步逐步构建查询步骤1计算每个月的总销售额和平均销售额SELECT DATE_FORMAT(sale_date, %Y-%m) as month, -- 按月分组 AVG(amount) as month_avg_amount FROM sales GROUP BY DATE_FORMAT(sale_date, %Y-%m);这得到了一个月的平均销售额。步骤2计算每个产品在每个月的销售额SELECT product_id, DATE_FORMAT(sale_date, %Y-%m) as month, SUM(amount) as product_month_amount FROM sales GROUP BY product_id, DATE_FORMAT(sale_date, %Y-%m);步骤3将产品月销售额与当月平均销售额关联比较我们需要把步骤1的结果作为子查询和步骤2的结果进行关联。SELECT p.product_id, p.month, p.product_month_amount, m.month_avg_amount FROM ( SELECT product_id, DATE_FORMAT(sale_date, %Y-%m) as month, SUM(amount) as product_month_amount FROM sales GROUP BY product_id, DATE_FORMAT(sale_date, %Y-%m) ) p JOIN ( SELECT DATE_FORMAT(sale_date, %Y-%m) as month, AVG(amount) as month_avg_amount FROM sales GROUP BY DATE_FORMAT(sale_date, %Y-%m) ) m ON p.month m.month WHERE p.product_month_amount m.month_avg_amount;现在我们得到了所有“在某个月份销售额超过该月平均额”的产品月份记录。步骤4筛选出每月都达标的产品关键点来了。我们需要的是那些产品其有销售的所有月份都出现在上一步的结果集里。换句话说对于某个产品它销售的月份数它达标的月份数。这可以用聚合和HAVING子句来实现。SELECT p.product_id FROM ( SELECT product_id, DATE_FORMAT(sale_date, %Y-%m) as month, SUM(amount) as product_month_amount FROM sales GROUP BY product_id, DATE_FORMAT(sale_date, %Y-%m) ) p JOIN ( SELECT DATE_FORMAT(sale_date, %Y-%m) as month, AVG(amount) as month_avg_amount FROM sales GROUP BY DATE_FORMAT(sale_date, %Y-%m) ) m ON p.month m.month WHERE p.product_month_amount m.month_avg_amount GROUP BY p.product_id HAVING COUNT(p.month) ( -- 子查询计算该产品总共有多少个月有销售记录 SELECT COUNT(DISTINCT DATE_FORMAT(sale_date, %Y-%m)) FROM sales s2 WHERE s2.product_id p.product_id );HAVING COUNT(p.month) ...确保了该产品达标的月份数等于它总的有销售的月份数从而满足了“每个月均超过”的条件。第三步思考优化与替代方案上面的查询逻辑正确但包含了一个关联子查询在数据量大时可能影响性能。我们可以尝试用窗口函数更优雅地解决吗可以思路先计算每个产品每个月的销售额以及该月的平均销售额用窗口函数AVG(amount) OVER (PARTITION BY month)然后筛选出销售额大于平均额的行最后再按产品分组判断是否所有月份都达标。WITH monthly_stats AS ( SELECT product_id, DATE_FORMAT(sale_date, %Y-%m) as month, SUM(amount) as product_month_amount, AVG(SUM(amount)) OVER (PARTITION BY DATE_FORMAT(sale_date, %Y-%m)) as month_avg_amount FROM sales GROUP BY product_id, DATE_FORMAT(sale_date, %Y-%m) ) SELECT product_id FROM monthly_stats GROUP BY product_id HAVING MIN(CASE WHEN product_month_amount month_avg_amount THEN 1 ELSE 0 END) 1;这个写法更简洁。WITH子句CTE提高了可读性。窗口函数AVG(...) OVER (...)直接在每个分区月内计算了平均值。最后的HAVING子句用了点技巧CASE WHEN将达标情况转为1或0MIN(...) 1意味着对于这个产品在所有月份中最小的达标标识是1即所有月份都达标没有0出现。通过这个案例你可以看到解决复杂SQL问题是一个“分解-关联-聚合”的过程。先理清业务逻辑拆分成简单的中间步骤再用JOIN和子查询组合起来最后思考是否有更优的窗口函数写法。在笔试中即使不能一步写出最优解清晰地展示出这个思考过程也远比只写一个干巴巴的正确答案更有价值。

SQL笔试核心考点解析：从JOIN原理到窗口函数实战

相关推荐

双向带头循环链表：C语言数据结构实现与LRU缓存应用

CAN总线设计哲学：从差分信号到无损仲裁的工程实践

Android 15 16 wifi热点选项缺少Dual Band

更多资讯

【第四周特刊】多智能体协同、因果推理与黄金评测实战全景复盘

【第四周特刊】五大极端现场交付攻坚手记：在泥泞中打赢商业硬仗

【第四周特刊】DPO、Unsloth 与 BitNet：大模型微调与量化前沿实践精萃

王协瑞版《计算机网络技术》课件精读：OSI分层与命令配置实战

OpenCV全景图像无缝拼接：从特征对齐到多频段融合的完整实战

YOLOv11遥感建筑物检测：多尺度小目标优化实战

今日更新

开源模型端侧落地实战：量化、推理加速与Agent上下文管理

AI Evals实战指南：从零搭建LLM应用评估体系与CI/CD集成

Java采购管理系统实战：从数据库设计到事务一致性

本周精选

如何彻底离线使用 Laya-CoreML：Hugging Face 下载、缓存机制与 local_files_only 详解

基于1Panel 部署MySQL MCP 服务：TaoToken 统一 Key 接入与 config.toml 配置实战

崇州园区营销网站建设避坑指南：5大注意事项保备案安全

本月热门

基于YOLO的焊缝缺陷检测：从数据标注到工业部署全流程实践

分布式服务的决策记录

创业产品的首版范围

读完文章，想聊聊您的网站？