写“SQL每日一题”这个系列其实最早是被逼出来的。团队里新人写SQL的水平参差不齐面试的时候问窗口函数、去重、慢SQL优化十个人里有八个答不到点子上。我每天抽十分钟从真实开发场景里扒一个SQL小问题把解法、原理、坑一次性讲透。坚持了大半年之后覆盖了日常工作中90%的高频痛点后来发现很多朋友也靠这套思路准备面试、写数据清洗脚本、做性能优化。这篇文章就把我整个系列的设计思路和几个最有代表性的题目完整拆开从命题、解题到避坑全流程复盘一遍适合正在学SQL的初学者也适合想系统梳理知识点的开发者和数据分析师。1. 内容整体设计与思路拆解1.1 为什么坚持用“每日一题”的形式沉淀知识SQL这东西有个特点语法看着简单但一到真实场景就变形。同样一句SELECT有人写出来能跑有人写出来能把数据库拖死。我自己带人的经验是靠看文档、背语法效果很差必须靠一个个具体的问题场景来逼大脑思考。每日一题天然符合这个规律——它强迫你每天只面对一个小问题问题足够聚焦解法足够清晰零碎时间就能消化掉不用专门腾出大块时间去啃。另外一个原因是知识遗忘曲线。SQL的技能点太多窗口函数、去重策略、索引优化、安全防范哪个不练都会手生。每天一道题等于每天做一次小规模的主动回忆效果比周末猛学一天好得多。而且一旦把题目串联起来就会发现自己脑子里慢慢有了一张SQL知识地图看到任何一句查询语句能自动反应出它属于哪个知识点、有什么潜在的坑。我做这个系列还有一个更实际的目的沉淀一套可以直接复用的面试题和代码片段。很多朋友私信问我SQL面试怎么准备我直接告诉他们去把我这个系列刷一遍比临时背题靠谱。因为每一道题都不是那种网上遍地都是的套路题而是我在开发中真正踩过坑、真正用过的场景。1.2 题库覆盖的方向与题目选型的逻辑我在选题的时候给自己定了一个原则每个题目必须能回答三个问题——它解决什么场景、它比常见写法好在哪、它有什么代价或坑。凡是只讲语法不讲场景的题一律不选。从整个知识覆盖来看我的系列主要铺了几个方向第一是数据查询的进阶能力比如窗口函数、分组排序、行列转换这部分对应日常工作里的报表、榜单、同环比计算第二是数据质量治理比如去重、空值处理、数据清洗这部分在ETL和数据迁移中特别常见第三是性能优化包括慢SQL定位、执行计划分析、索引设计很多生产事故都出在这里第四是SQL安全与数据库维护包括防止注入、处理密码过期、安装和配置问题。这五个方向基本覆盖了从写SQL到优化SQL、再到管SQL的全链路也是我平时被问到最多、踩坑最深的几个领域。选好题目之后我还会刻意控制难度梯度。一周五天周一到周三是基础到中等难度周四周五会安排一些需要深入思考的题目周末给一个综合实战题。这样做的目的是让学习节奏有波峰波谷不至于一开始就被劝退也不会到后期觉得太简单而失去兴趣。接下来我把几个最有代表性的专题展开来讲都是整个系列里被转发最多、讨论最热烈的部分。2. 窗口函数从“会写”到“写得巧”2.1 窗口函数的本质与一个经典错误窗口函数是我整个系列里最早做的一个专题原因很直接它是从传统GROUP BY思维过渡到现代SQL思维的一道分水岭。很多人写了几年SQL遇到“查每个部门工资最高的员工”这种经典问题第一反应就是GROUP BY取MAX(salary)可是查出来之后根本拿不到那个员工是谁。为什么因为GROUP BY会把组内多行压成一行你要的“工资最高的员工姓名”这个信息在分组之后已经消失了。窗口函数解决这个问题的方式是在不改变原始行数的情况下为每一行额外计算一个聚合值。它的核心逻辑是“给每一行开一扇窗口在窗口内做计算”。OVER (PARTITION BY department_id ORDER BY salary DESC)这段结构意思就是“按部门划分窗口每个窗口内按薪资排序”。配合ROW_NUMBER()就能给每个部门的人标上序号然后取序号为1的行就是部门最高薪员工。我见过很多新手在理解窗口时卡住其实就是没有抓住“每行保留窗口内计算”这八个字。我在题目里特意安排了一个对比写法让大家先写一遍GROUP BY版本的错误答案再写窗口函数版本的正确解法这样前后对照印象才深。这也是我整个系列一贯的做法哪怕你知道正确答案也先把错误路径走一遍知道错在哪才能理解为什么对。2.2 完整案例每个部门薪资最高的员工这个题目我用了一张经典的员工表结构如下CREATE TABLE employee ( employee_id INT PRIMARY KEY, employee_name VARCHAR(50), department_id INT, salary DECIMAL(10,2) ); INSERT INTO employee VALUES (1, 张三, 1, 8000), (2, 李四, 1, 9500), (3, 王五, 1, 9500), (4, 赵六, 2, 7000), (5, 孙七, 2, 8800), (6, 周八, 3, 12000);需求是查出每个部门薪资最高的员工信息。标准解法是SELECT employee_id, employee_name, department_id, salary FROM ( SELECT employee_id, employee_name, department_id, salary, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn FROM employee ) t WHERE rn 1;运行结果employee_idemployee_namedepartment_idsalary2李四195005孙七288006周八312000这里有一个非常容易忽略的细节部门1里张三和李四薪资相同都是最高。如果我用的窗口函数是ROW_NUMBER()那么两个人会随机排一个第一、一个第二造成“只返回一个人”的问题。如果需要并列返回就要换成DENSE_RANK()。这三个排序窗口函数的区别ROW_NUMBER()是即使相同值也硬排一个顺序RANK()是会留下空号DENSE_RANK()不会留空号。实际工作中遇到“查TOP N”的需求先确认业务要不要并列再选函数这是经验之谈。窗口函数另一个常见的收益是性能。如果同一张表你既要明细数据又要分组聚合结果窗口函数可以一次扫描完成传统写法往往要GROUP BY加JOIN回原表逻辑复杂还容易错。比如要算“每个员工薪资占部门总薪资的百分比”窗口函数一行代码就能搞定SELECT employee_id, employee_name, department_id, salary, SUM(salary) OVER (PARTITION BY department_id) AS dept_total, ROUND(salary / SUM(salary) OVER (PARTITION BY department_id), 4) AS pct FROM employee;这个能力在实际的报表开发里太好用了尤其是在MySQL 8.0和SQL Server 2012以上版本里都属于标配语法值得熟练掌握。3. 数据去重从清洗到删除的完整方案3.1 去重的三个层次查询去重、清洗去重、物理删除去重是我系列里留言最多的话题之一热搜词里“sql语句去重”“清洗---sql语句去重”反复出现说明这是很多人实际工作中的痛点。但我发现大家说“去重”时往往把三件完全不同的事混在一起。第一类是查询时去重只要结果里不出现重复行就行用SELECT DISTINCT或者GROUP BY第二类是数据清洗时去重比如从接口采集的数据有重复要把重复记录过滤掉只留一份第三类是物理删除表里已经有脏数据了要直接把重复行删掉让表恢复干净。这三种需求对应的解法完全不同混着用就会出事故。我见过最典型的事故是有人为了清洗数据直接用DISTINCT建了张临时表然后删掉原表再改名。这个方法在数据量小的时候能用但一旦表有外键引用或者业务正在读写就会引发大麻烦。更合理的做法是先用查询找到重复项分析清楚重复的判定规则再做清洗或删除。3.2 经典题目如何删除重复数据只保留一条我的题目是这样的有一张订单表订单号order_no加客户号customer_id理论上应该唯一但因为接口重复推送里面有很多重复记录需要删除重复项每个重复组只保留最早的那一条。CREATE TABLE orders ( id INT PRIMARY KEY, order_no VARCHAR(20), customer_id INT, create_time DATETIME ); INSERT INTO orders VALUES (1, SO20240101, 101, 2024-01-01 10:00:00), (2, SO20240101, 101, 2024-01-01 10:05:00), (3, SO20240101, 101, 2024-01-01 10:10:00), (4, SO20240102, 102, 2024-01-02 09:00:00), (5, SO20240102, 102, 2024-01-02 09:30:00);需求是删除重复数据只保留每个order_no customer_id组合中create_time最早的那条。第一步先看重复情况SELECT order_no, customer_id, COUNT(*) AS cnt FROM orders GROUP BY order_no, customer_id HAVING COUNT(*) 1;第二步用窗口函数标出每个重复组内的序号SELECT id, order_no, customer_id, create_time, ROW_NUMBER() OVER (PARTITION BY order_no, customer_id ORDER BY create_time ASC) AS rn FROM orders;第三步删除rn 1的记录。由于SQL Server不允许直接删除子查询里的窗口函数结果需要再包一层我用的写法是DELETE FROM orders WHERE id IN ( SELECT id FROM ( SELECT id, ROW_NUMBER() OVER (PARTITION BY order_no, customer_id ORDER BY create_time ASC) AS rn FROM orders ) t WHERE rn 1 );如果数据库是MySQL同样是这句逻辑完全通用。执行完再查一次重复数据已经清理干净只剩id为1和4的两行。3.3 不同去重方案的适用场景对比我在系列里专门画过一个对比这里用文字给大家整理成一张速查表方便不同场景直接对号入座方案适用场景优点局限SELECT DISTINCT查询结果去重不改动原表写法简单无副作用只能针对完整行去重不能控制保留哪条GROUP BY按某些字段去重并取聚合信息灵活可搭配聚合函数会丢失非分组字段明细ROW_NUMBER() 删除法物理删除重复行可自定义保留规则精确控制保留哪一条数据量大时注意锁表建议分批删临时表替换法一次性清洗小表思路直观风险高生产环境不推荐加唯一索引从源头防止重复数据写入根治问题数据库层面拦截需要业务容忍重复时报错或配合INSERT IGNORE这张表我建议直接收藏遇到去重需求先想清楚自己属于哪一种再去选方案。尤其是“保留最早那一条”这个需求用ROW_NUMBER()窗口函数加ORDER BY create_time ASC是最可控、最好理解的方案。不过有一点要提醒删数据之前务必先把结果集导出备份我叫它“删前必导出”。这个习惯救了我太多次了有一次线上误删了一批订单就是因为提前备份了WHERE条件对应的所有记录十分钟之内恢复了数据没有酿成事故。4. 慢SQL优化一整套从定位到验证的实操流程4.1 定位慢SQL的关键手段执行计划怎么看慢SQL优化是我系列里含金量最高的一个专题也是热搜里“慢sql优化”“oracle sql性能优化”反复出现的原因。很多开发者的状态是SQL能跑出结果就行压根不知道它扫描了多少行、走了什么索引、响应为什么慢。等到线上报警才着急结果连从哪下手都不知道。我的建议是遇到慢SQL第一步不是改SQL而是先看执行计划。不管是MySQL的EXPLAIN、SQL Server的“显示执行计划”还是Oracle的执行计划核心信息其实就几个type访问类型、rows预估扫描行数、Extra额外信息。我遇到过最典型的坏消息是type ALL意思是全表扫描一般几万行以上就会开始卡。加索引后往往变成type ref或者range效率就上来了。我在题目里设置了一个非常典型的场景查询语句长这样SELECT customer_id, SUM(amount) FROM payment_record WHERE pay_time 2024-01-01 AND pay_time 2024-02-01 AND status SUCCESS GROUP BY customer_id;执行计划显示rows 93210全表扫描耗时2.3秒。分析之后发现pay_time和status都没有索引数据库只能把所有记录读一遍再过滤扫描了近十万行。优化方案就是建立一个联合索引把等值条件的字段放前面范围条件的字段放后面ALTER TABLE payment_record ADD INDEX idx_status_time (status, pay_time);加上这个索引之后执行计划的rows瞬间降到几百行查询时间从2.3秒降到0.04秒而且Extra从Using where; Using filesort变成了Using index condition说明索引已经真正被用上了。4.2 索引生效原理与最容易被忽视的失效场景索引为什么能这么快我用一个生活化类比来解释一本几百页的书如果没有目录想找“去重”这个知识点只能一页一页翻有了目录直接翻到对应章节。数据库索引和书的目录是一个道理它把字段值按顺序排好查询时通过B树快速定位把全表扫描的次数省下来。但目录也有失效的时候数据库索引同样有失效场景我把最常见的几个列成速查失效场景示例原因对索引列使用函数WHERE DATE(pay_time) 2024-01-01列值被函数处理后索引顺序失效隐式类型转换WHERE phone 13800138000列是VARCHAR索引列被自动转换无法匹配前导模糊查询WHERE name LIKE %张%通配符在最前面索引无法定位起点联合索引不满足最左前缀索引是(status, pay_time)只用pay_time查询跳过了第一个等值条件索引无法利用对索引列做计算WHERE salary * 2 10000索引列参与了运算无法直接比较最坑的是隐式类型转换因为很多时候你根本感觉不到它发生了。我在开发中遇到过很多次看起来字段是字符串但代码传了数字数据库悄悄做了类型转换索引就废了。而且这种问题在数据量小的时候完全没感觉一到线上数据量上来就爆发。优化的核心就是在写SQL的时候明确知道字段的类型永远不要让索引列参与函数、运算或隐式转换。4.3 设计层优化分页深翻页与避免SELECT *我每次讲完执行计划和索引之后都会追加一个容易被忽略的层面SQL本身的设计。最典型的是分页查询很多系统里分页是这么写的SELECT * FROM operation_log ORDER BY create_time DESC LIMIT 100000, 20;这行SQL的意思是跳过前面10万条再取20条。数据库不得不先把10万条数据全部读出来、排好序再扔掉前10万条。深翻页会越翻越慢这是必经之路。优化思路是用上一页最后一条记录的create_time和id做条件继续往后翻这叫“游标分页”SELECT * FROM operation_log WHERE create_time 2024-06-01 00:00:00 AND id 100000 ORDER BY create_time DESC LIMIT 20;这样数据库只需要从索引定位到指定位置直接往后取20条彻底跳过了前10万条数据。另外一个设计层面的细节是尽量别用SELECT *。我见过很多项目是SELECT *打天下其实你查询一张宽表时所有的列都会被读入内存再丢弃不需要的列白白浪费了I/O和内存。优化方式是只查询需要的列配合覆盖索引效果立竿见影。比如只查customer_id和amount建一个(status, pay_time, customer_id, amount)的索引甚至可以直接在索引层面完成查询不用回表这种方式叫“覆盖索引”是优化SQL时性价比非常高的手段。5. 开发中常见的SQL问题与安全、维护经验5.1 一套SQL常见问题速查表很多读者留言说平时写SQL的时候遇到的其实都是零碎的小问题并不像慢SQL那样需要系统分析但又找不到现成的答案。我把整个系列里高频出现的问题整理成一张速查表方便随时查阅问题场景描述解决方案查询结果有重复行联表查询时一对多产生重复使用DISTINCT或先用GROUP BY去重分析业务上是否需要去重分组后需要保留明细报表需要同时有分组汇总和明细行窗口函数配合OVER(PARTITION BY)实现某字段有空值导致统计偏差COUNT(列)会忽略NULL明确需求用COUNT(*)统计行数用IFNULL处理展示日期范围查询不准用了pay_time 2024-01-01漏掉当天部分数据使用半开区间 pay_time 开始 AND pay_time 次日大量IN子查询性能差WHERE id IN (SELECT id FROM ...)改用JOIN替代或改为EXISTS关联子查询拼接字符串有NULL结果出现NULL而不是想要的内容用COALESCE或CONCAT函数处理NULL我特别想展开说日期范围查询这个坑因为它是业务系统里最常见的定时任务和报表问题。很多人写某天的数据会用WHERE pay_time 2024-01-01看起来没问题但pay_time是DATETIME类型等于只匹配到凌晨零点零分零秒那一瞬间的记录其他所有该天的数据全部漏掉。正确的写法是WHERE pay_time 2024-01-01 AND pay_time 2024-01-02这种半开区间写法是业界标准能把你从无数个“数据少了一截”的诡异Bug里解救出来。5.2 SQL注入的原理与三条防御铁律SQL注入是另一个我反复强调的专题热搜里“sql注入”“sql注入万能密码绕过”的出现频率很高说明很多人对这个话题感兴趣但角度往往偏了。我以前在安全论坛和CTF比赛里见过各种注入手法但作为开发者的重点应该是“如何让自己的代码不产生这种漏洞”而不是去研究怎么绕过。注入的原理本质上就一句话把用户输入当成SQL代码来执行了。一条查询本来是想查用户信息SELECT * FROM users WHERE account admin AND password 123456;如果用户输入的账号里带了一个单引号和一段拼接代码整个SQL语句的逻辑就会改变。这就是为什么任何直接把用户输入拼进SQL的行为都是一颗定时炸弹。解决注入问题我总结为三条铁律第一使用参数化查询或预编译语句。不管是MySQL的PREPARE、Python的%s占位符还是Java的PreparedStatement核心都是把SQL结构提前编译好用户输入只作为参数传进去数据库不会把参数内容当成新的SQL结构去解析。第二对不可信输入做白名单校验。尤其是排序字段、表名这类无法用参数绑定的标识符必须校验是否在允许的列表里绝对不能直接拼接。第三遵循最小权限原则。应用连接的数据库账号不要用DBA权限只给必要的表的增删改查权限这样即使某条语句出问题损失也能控制到最小。我见过不少团队用ORM框架之后以为就安全了其实不对。ORM确实默认使用参数化查询但一旦有人为了方便写了原生SQL或者用了raw查询风险就回来了。所以我的建议是常规操作走ORM参数化原生SQL必须过代码评审这一点可以写进团队的开发规范里。5.3 数据库安装与客户端工具的避坑经验最后一个部分聊一聊热搜里大量出现的SQL Server安装类问题。不知道为什么身边总有朋友在装SQL Server的时候翻车。我自己装过很多次总结下来最常见的几个问题安装报错、服务起不来、密码过期连不上、工具连数据库报错。SQL Server安装失败时很多人直接懵在第一个弹窗。正确姿势是先在“安装规则检查”阶段就把所有失败项截图存下来然后去C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\Log目录下看最新的日志文件。大部分安装问题集中在几个原因缺少.NET Framework、防火墙拦截、已有旧版本服务冲突、或者安装账号权限不足。有一次我帮朋友排查安装失败折腾了整个下午最后发现是Windows Defender拦了SQL Server的安装进程。关掉实时保护之后一次就过了。这里也提醒大家装之前先把安全软件排查一遍能省很多时间。密码过期是另一个高频问题。SQL Server默认的sa密码和登录策略有有效期设置一旦过期就会出现“密码已过期”的错误。解决办法很简单ALTER LOGIN sa WITH PASSWORD 新密码; ALTER LOGIN sa WITH CHECK_POLICY OFF; ALTER LOGIN sa WITH CHECK_EXPIRATION OFF;把密码策略和过期检查关掉开发环境一般就不会再出这个提示了。生产环境当然不建议关闭过期检查但开发环境用这个方式解决问题很常见。还有很多人问我Navicat连SQL Server报错的排查思路说到底无非几个点SQL Server有没有开启TCP/IP协议、端口是不是1433、防火墙有没有放行、目标实例是不是命名实例、账号是否存在且密码正确。建议先在本机用命令行sqlcmd -S localhost -U sa -P 密码测试能连通说明Server本身没问题那问题就出在客户端配置或者网络层面。这个排查顺序能帮你省下大量盲目试错的工夫。最后分享一点实操心得整个“SQL每日一题”系列做下来我最大的感触是SQL能力的提升不是看你背了多少语法和函数而是看你在真实场景里能不能第一时间想到正确的解法。我见过太多人在网上收藏了一堆“SQL面试100题”真到用的时候依然无从下手。所以个人建议是把“每日一题”当成一个习惯来培养每天用点心思考一个真实场景哪怕只有十分钟长期积累下来的效果会非常惊人。做这个系列之后我还有一个明显的收获凡是自己踩过的坑用题目沉淀下来之后就再也没踩过第二次。