刷完力扣SQL50题后，我总结了这8个高频‘坑点’和避坑指南

刷完力扣SQL50题后我总结了这8个高频‘坑点’和避坑指南去年夏天我给自己定了个小目标完整刷完力扣SQL50题。本以为凭借多年的数据库使用经验这个过程会像散步一样轻松。但现实给了我一记响亮的耳光——在看似简单的题目里我反复掉进同样的陷阱。今天我想把这些血泪教训整理成8个典型坑点希望能帮你少走弯路。1. NULL值的幽灵你以为的‘空’可能并不空在176题《第二高的薪水》中我花了半小时调试一个看似完美的查询SELECT salary FROM employee ORDER BY salary DESC LIMIT 1 OFFSET 1直到发现当表中只有一条记录时这个查询不会返回NULL而是直接无结果。正确的做法应该是SELECT ( SELECT DISTINCT salary FROM employee ORDER BY salary DESC LIMIT 1 OFFSET 1 ) AS SecondHighestSalaryNULL处理的三个黄金法则任何与NULL的比较、、都返回UNKNOWN而非TRUE/FALSE聚合函数如COUNT(*)计入NULL但COUNT(字段)会忽略NULL使用COALESCE()或IFNULL()时要考虑业务逻辑是否允许替换NULL注意在584题《寻找用户推荐人》中WHERE referee_id ! 2 会排除referee_id为NULL的记录这是最常见的NULL陷阱之一。2. GROUP BY的认知偏差分组≠筛选当我第一次看到197题《上升的温度》时自信地写下了SELECT w1.id FROM Weather w1 JOIN Weather w2 ON DATEDIFF(w1.recordDate, w2.recordDate) 1 WHERE w1.Temperature w2.Temperature GROUP BY w1.id -- 这个GROUP BY完全多余实际上这里根本不需要分组操作。GROUP BY的常见误解包括误解事实分组后只能选择分组字段可以选聚合函数处理的其他字段HAVING是WHERE的替代品WHERE在分组前过滤HAVING在分组后过滤分组会自动排序除非显式使用ORDER BY3. 窗口函数的性能黑洞优雅但可能致命在185题《部门工资前三高的所有员工》中我最初这样写SELECT department, employee, salary FROM ( SELECT d.name AS department, e.name AS employee, e.salary, DENSE_RANK() OVER (PARTITION BY d.id ORDER BY e.salary DESC) AS rnk FROM Employee e JOIN Department d ON e.departmentId d.id ) t WHERE rnk 3这个解法虽然正确但在大数据量时效率堪忧。后来发现用自连接可能更高效SELECT d.name AS department, e1.name AS employee, e1.salary FROM Employee e1 JOIN Department d ON e1.departmentId d.id WHERE 3 ( SELECT COUNT(DISTINCT e2.salary) FROM Employee e2 WHERE e2.departmentId e1.departmentId AND e2.salary e1.salary )窗口函数虽强大但要注意避免在OVER()中使用不必要的排序分区字段的选择直接影响性能考虑是否真的需要RANK()而不是ROW_NUMBER()4. 日期处理的跨数据库陷阱在197题《上升的温度》中MySQL的DATEDIFF在Oracle中要改成-- MySQL DATEDIFF(w1.recordDate, w2.recordDate) 1 -- Oracle w1.recordDate - w2.recordDate 1 -- PostgreSQL (w1.recordDate - w2.recordDate) INTERVAL 1 day日期处理的避坑清单日期加减MySQL用DATE_ADDPostgreSQL用 INTERVAL日期提取YEAR()在SQL Server中要改用DATEPART(year,...)日期格式化MySQL用DATE_FORMATOracle用TO_CHAR5. JOIN的笛卡尔积灾难在180题《连续出现的数字》中我犯过这样的错误SELECT DISTINCT l1.num AS ConsecutiveNums FROM Logs l1, Logs l2, Logs l3 -- 这是隐式交叉连接 WHERE l1.id l2.id - 1 AND l2.id l3.id - 1 AND l1.num l2.num AND l2.num l3.num虽然能得到正确结果但三表笛卡尔积的性能极差。更好的写法是显式JOINSELECT DISTINCT l1.num AS ConsecutiveNums FROM Logs l1 JOIN Logs l2 ON l1.id l2.id - 1 AND l1.num l2.num JOIN Logs l3 ON l2.id l3.id - 1 AND l2.num l3.numJOIN使用的经验法则永远使用显式JOIN语法而非逗号连接确保ON条件包含所有必要的关联条件多表连接时考虑是否真的需要所有表的全部数据6. 子查询的认知误区IN vs EXISTS在1045题《买下所有产品的客户》中这两种写法看似等效但性能迥异-- 写法1使用IN SELECT customer_id FROM Customer WHERE product_key IN (SELECT product_key FROM Product) -- 写法2使用EXISTS SELECT DISTINCT customer_id FROM Customer c WHERE EXISTS ( SELECT 1 FROM Product p WHERE p.product_key c.product_key )子查询选择指南场景推荐方案原因外层表大内层表小IN内层查询只需执行一次外层表小内层表大EXISTS对每行外层记录检查存在性需要处理NULL值NOT EXISTSNOT IN对NULL处理有问题7. 字符串比较的隐藏规则在1527题《患某种疾病的患者》中这个条件可能让你栽跟头WHERE conditions LIKE %DIAB1% -- 会匹配到ADIAB100这类字符串更精确的写法应该是WHERE conditions LIKE DIAB1% OR conditions LIKE % DIAB1%字符串处理的注意事项LIKE通配符%和_的位置影响匹配结果区分大小写取决于数据库配置空字符串与NULL是不同的概念字符串函数如SUBSTRING的索引从1而非0开始8. 分页查询的方言差异在不同数据库中实现分页简直是场噩梦-- MySQL SELECT * FROM table LIMIT 10 OFFSET 20 -- SQL Server SELECT * FROM table ORDER BY column OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY -- Oracle SELECT * FROM ( SELECT t.*, ROWNUM rn FROM ( SELECT * FROM table ORDER BY column ) t WHERE ROWNUM 30 ) WHERE rn 20 -- PostgreSQL (与MySQL语法相同但更推荐标准写法) SELECT * FROM table ORDER BY column LIMIT 10 OFFSET 20分页最佳实践永远与ORDER BY一起使用确保结果确定性大数据量时考虑使用WHERE条件替代OFFSET前端传递页码时要转换为正确的OFFSET值