你可能已经能用一个SQL解决日常开发或数据分析里的大部分问题了几个表JOIN一下、加上WHERE和GROUP BY、再ORDER BY排序返回结果看起来什么需求都能搞定。但真往“中级SQL”这个层级逼一把的时候你会发现事情没那么简单——同样是取“每个部门工资最高的员工”有人一条窗口函数十几行收工有人却要套两层子查询还不一定对同样是处理重复数据有人一句DISTINCT了之结果业务数据对不上账还查不出原因。我理解的“Intermediate-SQL”不是某个课程编号也不是某种证书等级而是这样一种状态你不再满足于“能查出结果”开始关注“为什么这样查是对的”“为什么这条SQL跑这么慢”“换一个数据库引擎还能不能跑”。这篇内容就是给正处在这个阶段的同学写的不念语法手册也不灌水按照实际工作中会遇到的几类典型场景把中级SQL必须跨过去的门槛一个个拆开讲。1. 中级SQL和初级SQL的分界线到底在哪1.1 从“能查出结果”到“能解释结果”初学SQL的阶段大家关心的核心问题只有一个怎么把数据查出来。JOIN怎么写、WHERE怎么过滤、GROUP BY怎么分组、ORDER BY怎么排序这些语法只要练上一个月基本都能熟练。但这也恰恰是初级最大的舒适区——只要结果集看起来对任务就结束了。到了中级评判标准完全变了。同样一条需求你要能回答至少四个问题这条SQL如果把数据量放大一百倍还能不能在一分钟内跑完为什么这个结果里会出现重复行是业务本来就这样还是JOIN产生的笛卡尔积这段查询换到MySQL能跑换到SQL Server或者Hive还能不能跑需要改哪里写出来的SQL别人能不能看懂三个月后的自己还能不能看懂这四个问题分别对应了中级SQL的几个核心能力性能意识、数据语义理解、跨方言迁移能力和可维护性。如果只是把语法练熟但完全不接触这几个维度那无论写了多少条SQL水平大概率还是停留在初级。1.2 我判断一个人SQL水平只看三个习惯第一拿到需求是先写SQL还是先想逻辑。初级同学通常打开编辑器就开始敲边敲边试。习惯好的中级开发者会先在脑子里把数据结构过一遍数据源头在哪几张表JOIN之后粒度会不会变要得到的目标粒度是什么中间需要几步变换。这个顺序看似浪费时间实际上能避免大量返工。第二遇到重复数据第一反应是什么。初级多半直接DISTINCT因为那是他们唯一学过的去重方式。但中级会先思考重复是怎么产生的是数据源本身有重复还是因为多表JOIN之后粒度变大导致的重复这两个原因对应的处理方式完全不同后面我会专门展开讲。第三有没有看执行计划的意识。初级阶段基本不关心查询性能因为数据量小跑多快都感觉不到差别。可一旦到生产环境一张表几百万甚至上亿行一条没走索引的查询能把数据库拖到报警。中级SQL和初级SQL一个显著分水岭就是遇到慢查询时会不会打开执行计划去看而不是傻乎乎地反复重跑碰运气。2. 窗口函数中级SQL的第一道门槛2.1 窗口函数和GROUP BY到底差在哪窗口函数也就是常说的开窗函数是很多人进入中级SQL遇到的第一道坎。原因很简单它和GROUP BY看起来都在做“分组统计”但行为完全不一样。GROUP BY会把多行压缩成一行分组之后的明细信息就没了。比如销售表里有员工、部门、销售额三列你想看每个部门的最高销售额用GROUP BY能得到每个部门的MAX值但拿不到“这个最高销售额是哪位员工创造的”。想拿员工信息就得再写一个子查询去关联非常绕。窗口函数则不同。它同样可以按部门分组但结果集不会压缩每一行还保留着只是在每一行旁边多出一列计算结果。这种“不丢明细”的特性让窗口函数在处理分组内排名、累计求和、同比环比、移动平均时拥有天然优势。基本语法结构是这样的函数名() OVER ( PARTITION BY 分组列 ORDER BY 排序列 窗口范围 )PARTITION BY负责分组ORDER BY负责组内排序窗口范围是可选的高级用法。理解了这个结构窗口函数的大半功能你都能自己推导出来。2.2 ROW_NUMBER、RANK、DENSE_RANK三个排名的选择三个排名函数的语法几乎一样但含义不同这是新手最容易搞混的地方。SELECT employee, department, amount, ROW_NUMBER() OVER(PARTITION BY department ORDER BY amount DESC) AS rn, RANK() OVER(PARTITION BY department ORDER BY amount DESC) AS rk, DENSE_RANK() OVER(PARTITION BY department ORDER BY amount DESC) AS drk FROM sales;假设某个部门有四个员工的销售额分别是100、80、80、60三个函数的结果差异立刻就能看出来函数结果序列适用场景ROW_NUMBER1, 2, 3, 4强制唯一编号比如取Top 3会自动淘汰并列者RANK1, 2, 2, 4并列占用名次下一个名次会跳号DENSE_RANK1, 2, 2, 3并列不占用名次下一个名次不跳号实战里有个经典需求“取每个部门销售额最高的员工”。这个需求要求每个部门只出一条记录即使有并列也只取其中一个。这时候用ROW_NUMBER最合适因为它能保证每组都有唯一的序号SELECT employee, department, amount FROM ( SELECT employee, department, amount, ROW_NUMBER() OVER(PARTITION BY department ORDER BY amount DESC) AS rn FROM sales ) t WHERE rn 1;注意我包了一层子查询这是因为SQL执行顺序里WHERE条件是在SELECT计算之前的窗口函数算出来的别名不能直接在WHERE里用必须从外层过滤。这个细节不搞清楚写出来的SQL会一直报“列不存在”的错。2.3 累计求和与移动平均是窗口函数的高级用法排名只是入门窗口函数更值钱的地方在于“基于窗口范围做计算”。比如按月份累计销售额只需要SELECT sale_month, amount, SUM(amount) OVER(ORDER BY sale_month) AS cumulative_amount FROM monthly_sales;这里没有写PARTITION BY默认全表一整个窗口ORDER BY sale_month表示按月份逐行累计。如果想看最近三个月的移动平均就用ROWS BETWEEN指定窗口范围SELECT sale_month, amount, AVG(amount) OVER( ORDER BY sale_month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS moving_avg FROM monthly_sales;ROWS BETWEEN 2 PRECEDING AND CURRENT ROW的意思就是“从当前行往前数两行到当前行结束”构成一个三行的滑动窗口。搞懂这个语法之后同比、环比、累计占比、组内最值全都能用窗口函数一套思路解决。这也是我认为中级SQL必须先把窗口函数吃透的原因——它真的能替代一大坨复杂的自连接和嵌套子查询。3. CTE公共表表达式把复杂逻辑从“洋葱”变成“阶梯”3.1 子查询嵌套为什么难读初级SQL风格有一个典型病根喜欢把子查询一层套一层套到最后整个SQL像洋葱一样从外面根本看不清里面在干什么。SELECT ... FROM ( SELECT ... FROM ( SELECT ... FROM table_a ) t1 JOIN table_b ON ... ) t2 WHERE ...这种写法不是不能用但问题很现实第一排错困难最内层一旦结果不对你很难定位是哪一层出了问题第二可读性差别人接手你的SQL第一反应是想骂人第三子查询在部分数据库里可能有物化或优化限制性能不一定比CTE好。CTECommon Table Expression解决的就是这个问题语法也很简单用WITH把每一段逻辑先“命名”出来然后再在后面的查询里引用它。WITH department_sales AS ( SELECT department, SUM(amount) AS total_amount FROM sales GROUP BY department ), top_departments AS ( SELECT department FROM department_sales ORDER BY total_amount DESC LIMIT 5 ) SELECT ... FROM top_departments;每一段逻辑都有名字像搭台阶一样一级一级往下走而不是把所有逻辑全揉成一个巨大的嵌套块。我在实际工作里凡是超过三个JOIN或者有两个以上子查询的SQL基本都会优先考虑CTE。3.2 递归CTE对付树形结构的关键工具CTE还有一种初级阶段很少接触的进阶形态叫递归CTE。它专门用来处理树形结构最常见的就是组织架构、商品分类、评论回复这类“节点有父子关系”的数据。比如一张部门表包含id、parent_id、name三个字段想查出某个部门下面所有层级的子部门用普通SQL写起来极其痛苦因为你不知道树有几层。递归CTE可以这样写WITH RECURSIVE dept_tree AS ( SELECT id, parent_id, name, 1 AS depth FROM department WHERE id 1 UNION ALL SELECT d.id, d.parent_id, d.name, dt.depth 1 FROM department d INNER JOIN dept_tree dt ON d.parent_id dt.id ) SELECT * FROM dept_tree;第一段SELECT是递归的起点也就是“种子行”第二段SELECT通过JOIN自己查自己的子级然后一层层往下扩。UNION ALL把每一层的结果都追加进来直到没有新的子级为止。这里有一个坑得提醒递归CTE如果数据里存在循环引用比如A的父节点是BB的父节点又是A那递归会无限循环下去直到数据库资源耗尽。生产环境使用递归CTE时务必确认数据里没有环或者在能递归次数有限的实现里加上深度限制。3.3 别让CTE成为新的“大泥球”CTE好用但也容易被滥用。见过有人把一段长达几百行的逻辑全部拆成十几个CTE块一个WITH里面挂着一长串链条中间任何一环的逻辑错误都很难发现。我的建议是CTE适合用来拆解“有明确业务含义的中间步骤”比如“先算出每个用户最近一次登录时间”“再算出每个渠道的有效转化数”。如果一个CTE只是为了拆一段计算而拆拆完还是让读者一头雾水那不如老老实实写临时表或者把复杂的中间结果先落到临时表里加个索引再继续算。4. 去重与空值看着简单做对很不容易4.1 DISTINCT不是神器是偷懒工具写SQL的人百分之百都用过DISTINCT但真的理解它语义的没那么多。DISTINCT作用是对结果集中的完全重复行做合并它回答的是“这一行是不是完全一样”而不是“为什么会有重复”。实际业务里常见的重复来源有两种。一种确实是数据源本身有重复记录比如用户表因为上游同步逻辑问题同一个用户出现两行。另一种是JOIN出来的重复——订单表JOIN订单明细表一笔订单有三条明细那订单表的信息就复制成了三行这时候如果你只想知道订单数量直接COUNT(DISTINCT order_id)当然也行但如果你把订单所有字段都DISTINCT一把然后拿去算订单金额SUM很可能会把同一笔订单的金额算重复多次。我见过太多因为一句DISTINCT掩盖了JOIN粒度问题最后统计报表数字对不上的案例。处理这种重复正确思路是先搞清楚这张表的粒度也就是“一行到底代表什么”然后再决定怎么去重。4.2 保留“最想要的那一条”记录去重场景里最难的一种是有重复数据但每一行内容不完全相同你想保留其中最该保留的一条。比如操作日志表里同一个用户有多条操作记录你想取每个人最新的一条。这时候用DISTINCT完全无能为力正确做法是用窗口函数ROW_NUMBER来编号再取第一条WITH ranked_logs AS ( SELECT user_id, op_time, content, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY op_time DESC) AS rn FROM operation_logs ) SELECT user_id, op_time, content FROM ranked_logs WHERE rn 1;如果同一个人在同一秒产生了多条记录光按时间排序依然不稳定建议ORDER BY里再加一个唯一递增的ID列作为决胜排序字段保证每次跑出来的结果一致。4.3 空值处理组合拳COUNT、COALESCE、NULLIFNULL是SQL里最反直觉的东西。很多人学SQL时都背过“NULL和任何值比较都返回NULL”但实战里还是不断踩坑。先说COUNT。COUNT()统计的是行数COUNT(column)统计的是该列非NULL的个数。这俩看起来只差一个星号结果可能差很多。如果你想知道某个字段到底有多少条缺失值正确的写法是COUNT() - COUNT(column)而不是想当然地COUNT(column)。再说日常清洗。COALESCE函数可以按顺序返回第一个非NULL值是处理空值默认值最常用的工具SELECT user_id, COALESCE(nickname, 未设置昵称) AS display_name FROM users;NULLIF则是反过来当两个参数相等时返回NULL常用于防止除零错误SELECT total_amount, NULLIF(order_count, 0) AS safe_count FROM stats;把COALESCE和NULLIF组合用很多空值判断和除零保护都能写得非常干净比一层层CASE WHEN要清爽得多。5. 慢SQL排查不是玄学执行计划与索引的使用顺序5.1 一条慢SQL的典型症状与第一步动作数据库慢查询是中级SQL必修课因为不管你SQL写得再花哨跑不动就等于零。遇到慢SQL第一反应不是去猜而是去看执行计划。MySQL里是EXPLAINSQL Server里是SET SHOWPLAN_ALL ON或者直接用图形化执行计划Oracle有EXPLAIN PLAN FOR。执行计划的核心信息是这么几项走的是全表扫描还是索引查找、预估扫描多少行、有没有排序和临时表、表之间的连接顺序是什么。举个例子一条查询跑了两秒EXPLAIN结果里显示typeALL意思是全表扫描一张表有上百万行每一行都被翻了一遍。这时候优化方向很明确给WHERE条件涉及的列加索引。5.2 导致索引失效的四个常见写法加了索引不等于索引一定能被用上下面这几种写法是常见的“索引杀手”对索引列使用函数。比如WHERE DATE(create_time) 2024-01-01一旦把列包进函数里数据库就无法直接使用该列的索引。正确的写法是WHERE create_time 2024-01-01 AND create_time 2024-01-02。LIKE以通配符开头。WHERE name LIKE %张%无法走索引因为索引是按前缀匹配的。但name LIKE 张%可以。隐式类型转换。比如column是字符串类型却跟数字比较数据库可能把每一行的列都转一遍再比索引自然失效。这也引出一个设计教训类型为文本的编号字段就老老实实存字符串应用程序传入时也别转成数字。OR两边不是同一套索引覆盖。OR条件往往会让优化器放弃使用索引优先考虑用UNION ALL改写或者确认OR两边都能独立走索引。5.3 一个从“跑不动”到“秒回”的排查顺序我处理慢SQL的习惯是固定一套顺序第一步拿到SQL先看WHERE和JOIN条件涉及哪些列。第二步用EXPLAIN确认哪些表在走全表扫描估算扫描行数是多少。第三步直接看有没有可以加的复合索引。所谓复合索引就是覆盖多个查询条件的索引比如同时查department和status两个字段建一个(department, status)的联合索引通常比建两个单列索引效果更好。第四步看SELECT返回的列是不是真的都需要。有人习惯写SELECT *多返回的列会带来更多IO开销也容易破坏覆盖索引的命中。第五步加上索引或者改写SQL之后重新EXPLAIN对比扫描行数和耗时确认改善效果。这个流程走一遍绝大多数慢SQL都能有立竿见影的优化空间。6. 换数据库时的方言差异SQL Server、MySQL、Hive的常见分歧6.1 时间函数的差异比想象中大热搜里能看到大量时间函数相关的问题比如sql server 时间函数、mysql常用的sql语句这些背后其实是同一个痛点换一个数据库引擎之前背熟的一套时间函数全部失效。同样表达“当前时间加一天”三家的写法就不一样功能SQL ServerMySQLHive当前时间GETDATE()NOW()current_timestamp日期加一天DATEADD(day, 1, col)DATE_ADD(col, INTERVAL 1 DAY)date_add(col, 1)日期格式化FORMAT(col, yyyy-MM-dd)DATE_FORMAT(col, %Y-%m-%d)date_format(col, yyyy-MM-dd)函数名不同只是一方面参数顺序不同才是最大的坑。DATEADD是“时间单位在前数量在中列在后”DATE_ADD是“列在前INTERVAL关键字数量单位在后”。写惯了SQL Server的人切到MySQL很容易把参数顺序搞反还不容易发现。我的建议不是去背所有数据库的日期函数而是记住一个思路先确定当前在什么引擎上再按引擎查官方文档。跨库操作的中间层最好统一把日期先转成标准格式字符串再传递避免底层方言差异污染到上层的业务代码。6.2 分页、字符串拼接的空值行为差异分页也是三方言差异的重灾区。SQL Server用OFFSET ... FETCH NEXT ROWS ONLYMySQL用LIMIT offset, countHive的LIMIT不支持带大偏移量的高效分页更推荐按排序字段过滤的方式翻页。字符串拼接的差异同样明显。SQL Server里写CONCAT或者用加号MySQL里加号只做数值运算字符串拼接得用CONCAT函数Orcale里则是双竖线。如果一个团队同时维护多套数据库这些差异就得靠统一封装来规避而不是靠每个人去记。另外还有一个容易被忽视的坑NULL的默认排序位置在不同数据库里不一样。有的数据库认为NULL是最小值排在最前有的认为NULL是未知值排在最后。如果查询结果对排序位置有严格要求不要指望默认行为直接显式写“NULLS FIRST”或“NULLS LAST”能写的地方就写清楚。6.3 长数字文本变科学计数法的问题Excel里出现“科学计数法”几乎是每个人都遇到过的破事。比如源库里一条文本类型的编号导出到CSV再用Excel打开一长串数字就变成了1.23E12这种鬼样子。这看起来是Excel的问题但根因往往在上游建表时就把这类本应该是文本的编号字段定义成了数值类型。类似身份证号、银行卡号、工单号这类“看起来像数字但不是数字”的业务编号建表时应该一律用VARCHAR或CHAR而不是INT或BIGINT。如果字段已经是数值类型导出时可以用文本格式导出或者SQL里先转成字符串同时确保前端或报表工具接收时也按文本处理。很多看起来是“导出格式问题”的故障往深了查都是数据建模阶段埋下的雷。7. 写SQL的人都要有的防御习惯输入数据与SQL边界问题7.1 一句话说清楚风险来自哪里在业务系统里SQL经常要接收外部输入作为查询条件。如果这些输入被直接拼接进SQL字符串里就可能出现一种情况原本只是作为“值”的内容被数据库解释成了“语法的一部分”从而改变了整条查询的逻辑。这个问题的本质是数据和代码的边界被打破了也就是常说的SQL注入风险。它不是一个只在安全测试里才会遇到的概念而是在任何把外部输入拼进SQL的地方都可能出现。中级SQL开发者应该具备的基本安全意识就是绝对避免自己去拼这种串而是使用参数化查询。7.2 参数化查询和动态内容的处理习惯参数化查询的基本写法是用占位符代替直接拼接# 不安全的方式不推荐 cursor.execute(SELECT * FROM users WHERE username username ) # 安全的方式推荐 cursor.execute(SELECT * FROM users WHERE username %s, (username,))参数化查询为什么安全因为数据库收到的是SQL结构和参数值两部分参数只作为值参与运算永远不会变成新的SQL语法。这是从机制上杜绝了一整类输入注入的问题比任何过滤函数都可靠。比较难处理的场景是动态排序。ORDER BY后面的列名通常不能直接用参数绑定因为列名属于SQL结构而不是值。处理习惯是做白名单映射前端传一个标识符后端根据标识符查映射表得到允许排序的列名而不是直接把用户输入拼进ORDER BY。宁可多写几行映射也不要图省事去拼字符串。另外还有两个习惯值得一提。一个是生产库账号要用最小权限应用系统连接数据库的账号只给必要表的增删改查权限不要用管理员账号跑所有应用逻辑另一个是日志里不要打印带原始输入拼好的完整SQL既减少敏感信息扩散也避免排查问题时误把构造语句直接当成线上执行语句。8. 从中级继续往上走把SQL当成数据建模能力8.1 你写的SQL默认下一次还会被别人读中级SQL和高手的差距不在会不会某个冷门函数而在写出来的东西能不能让人快速读懂。我自己的习惯是超过三层嵌套的逻辑一定用CTE拆每个有业务含义的筛选条件都尽量写成可读的命名多表关联时把小的结果集放前面还是把主表放前面要注意连接顺序和结果集的把握能不用SELECT *就绝不用。这不是强迫症而是协作成本问题。SQL一旦进入团队协作你的查询会被review、被复用、被基于它继续改需求。如果一段SQL连你自己第二天都看不明那它本质上是在给团队制造隐性债务。8.2 下一步可以接触的几个方向走到中级SQL之后继续提升的方向其实很清晰。事务、锁和隔离级别是一些后台开发者天然需要补的一块它们能解释为什么不同会话看到的数据可能不一样。索引设计与执行计划深挖是数据库性能优化的延展。窗口函数和聚合函数的高级组合还能继续玩出更多花样。还有一点容易被忽略把SQL当成理解业务的工具。把你目前生产库的核心表结构摸一遍搞清楚每一张表的主键、外键关系和粒度甚至可以借助工具把现有查询转成ER图来辅助理解。能画出表之间的关系图下一步做数据模型设计、写复杂报表、做性能调优都会有完全不同的视野。我自己带人的时候最常说的一句话是中级SQL不是背出来的是被业务问题逼出来的。你手头那些需要连续取TopN、要算累计占比、要去重保最新、要调性能的需求每一个都是升级的好机会。把这些需求背后通用的解题思路沉淀下来比刷一百道SQL题都管用。