企业官网建设流程全解析

1. 力扣高频 SQL 50 题阶段总结（二）概述

作为一名长期奋战在数据领域的老兵，我深知SQL技能对程序员的重要性。力扣（LeetCode）作为技术面试的"练兵场"，其SQL题库的质量和实用性在业内是有口皆碑的。这次我将继续分享高频SQL 50题的第二部分实战总结，重点聚焦那些让无数面试者"又爱又恨"的中高级查询场景。

与基础篇不同，这部分题目更注重考察对SQL特性的深入理解和灵活运用能力。窗口函数、复杂子查询、多表连接优化等核心知识点频繁出现，很多题目看似简单，实则暗藏玄机。我在实际刷题过程中发现，即使是工作多年的开发者也常在这些题目上"翻车"。

2. 核心题型与解题思路拆解

2.1 窗口函数的进阶应用

窗口函数是SQL高级查询的"瑞士军刀"，在力扣高频题中占比超过30%。与基础篇介绍的ROW_NUMBER()不同，这部分更侧重：

• LEAD/LAG的时间序列分析：典型如第178题"分数排名"，需要计算当前行与前后行的差值。关键点在于理解FRAME子句的默认行为：

  LAG(salary, 1, 0) OVER(PARTITION BY department ORDER BY hire_date) -- 第三个参数0表示默认值

• DENSE_RANK与RANK的微妙差异：第185题"部门工资前三高的员工"完美展示了这个区别。当存在并列时，RANK会产生间隔而DENSE_RANK不会：

  /* 错误示范 */ SELECT * FROM ( SELECT *, RANK() OVER(PARTITION BY dept ORDER BY salary DESC) AS rnk FROM employee ) t WHERE rnk <= 3 -- 可能漏掉实际需要的数据 /* 正确方案 */ SELECT * FROM ( SELECT *, DENSE_RANK() OVER(PARTITION BY dept ORDER BY salary DESC) AS drnk FROM employee ) t WHERE drnk <= 3

提示：窗口函数性能陷阱 - 当OVER子句中的PARTITION BY列基数很高时，可能导致内存溢出。我曾在一个500万行的表上使用PARTITION BY user_id，直接导致OOM。解决方案是先用WHERE缩小数据范围。

2.2 复杂子查询的优化策略

力扣第262题"行程和用户"是典型的子查询难题，要求计算取消率。常见误区包括：

1. 在WHERE中使用相关子查询：导致Nested Loop性能灾难

   /* 低效写法 */ SELECT request_at, COUNT(IF(status LIKE 'cancelled%', 1, NULL)) / COUNT(*) FROM trips WHERE client_id IN (SELECT users_id FROM users WHERE banned = 'No') AND driver_id IN (SELECT users_id FROM users WHERE banned = 'No') GROUP BY request_at /* 优化方案 */ WITH valid_users AS ( SELECT users_id FROM users WHERE banned = 'No' ) SELECT request_at, ROUND(SUM(status LIKE 'cancelled%') / COUNT(*), 2) AS cancellation_rate FROM trips t JOIN valid_users v1 ON t.client_id = v1.users_id JOIN valid_users v2 ON t.driver_id = v2.users_id GROUP BY request_at

2. 忽略NULL值处理：当除数为0时，MySQL返回NULL而非错误。安全写法应加入：

   IF(COUNT(*) > 0, SUM(...)/COUNT(*), 0)

2.3 递归CTE解决层次查询

第1270题"所有人的会议"展示了递归CTE的强大之处。关键步骤：

1. 基础查询确定起始点
2. 递归部分通过JOIN扩展关系
3. 终止条件避免循环引用

WITH RECURSIVE meeting_path AS ( -- 基础查询：找出所有直接向CEO汇报的人 SELECT employee_id FROM Employees WHERE manager_id = 1 AND employee_id != 1 UNION ALL -- 递归查询：找出下属的下属 SELECT e.employee_id FROM Employees e JOIN meeting_path mp ON e.manager_id = mp.employee_id ) SELECT * FROM meeting_path;

踩坑记录：MySQL 8.0之前不支持递归CTE，面试时若遇到旧版本环境，需要用存储过程模拟。我曾用临时表+循环实现，代码量暴涨且性能下降明显。

3. 高频题型实战解析

3.1 第184题"部门最高工资"

题干：找出每个部门工资最高的员工。

典型错误：

-- 错误方案1：GROUP BY后直接SELECT非聚合列 SELECT departmentId, name, MAX(salary) FROM Employee GROUP BY departmentId; -- MySQL可能不报错但结果随机 -- 错误方案2：先GROUP再JOIN可能重复 WITH max_sal AS ( SELECT departmentId, MAX(salary) AS max_salary FROM Employee GROUP BY departmentId ) SELECT e.* FROM Employee e JOIN max_sal m ON e.departmentId = m.departmentId WHERE e.salary = m.max_salary; -- 当多人同薪时会重复

最优解：

SELECT d.name AS Department, e.name AS Employee, e.salary FROM Employee e JOIN Department d ON e.departmentId = d.id WHERE (e.departmentId, e.salary) IN ( SELECT departmentId, MAX(salary) FROM Employee GROUP BY departmentId );

执行计划分析：

• MySQL 8.0+对IN子查询有优化，会先执行子查询物化
• 比窗口函数方案节省了排序开销

3.2 第180题"连续出现的数字"

题干：找出所有至少连续出现三次的数字。

解决方案对比：

推荐方案：

SELECT DISTINCT num AS ConsecutiveNums FROM ( SELECT num, @counter := IF(@prev = num, @counter + 1, 1) AS cnt, @prev := num FROM Logs, (SELECT @prev := NULL, @counter := 1) AS init ) AS t WHERE cnt >= 3;

注意事项：

1. 变量初始化必须在同一语句中完成
2. 执行顺序：FROM → WHERE → SELECT，因此变量赋值要在SELECT完成
3. MySQL 8.0+建议改用窗口函数，变量方案在复杂查询中可能产生意外结果

4. 性能优化专项

4.1 索引使用黄金法则

通过第197题"上升的温度"（日期差值计算）分析索引失效场景：

-- 题目：找出温度比前一天高的日期 SELECT w1.id FROM Weather w1 JOIN Weather w2 ON DATEDIFF(w1.recordDate, w2.recordDate) = 1 WHERE w1.Temperature > w2.Temperature;

问题诊断：

1. DATEDIFF函数导致无法使用recordDate索引
2. 自连接产生N²中间结果

优化方案：

-- 方案1：利用日期连续性（假设无缺失日期） SELECT w1.id FROM Weather w1 JOIN Weather w2 ON w1.recordDate = DATE_ADD(w2.recordDate, INTERVAL 1 DAY) WHERE w1.Temperature > w2.Temperature; -- 方案2：窗口函数（MySQL 8.0+） SELECT id FROM ( SELECT id, Temperature - LAG(Temperature) OVER(ORDER BY recordDate) AS diff FROM Weather ) t WHERE diff > 0;

4.2 执行计划解读技巧

以第601题"体育馆的人流量"为例，分析EXPLAIN关键指标：

-- 查询人流量连续三天≥100的记录 WITH consecutive AS ( SELECT *, id - ROW_NUMBER() OVER(ORDER BY id) AS grp FROM Stadium WHERE people >= 100 ) SELECT id, visit_date, people FROM consecutive WHERE grp IN ( SELECT grp FROM consecutive GROUP BY grp HAVING COUNT(*) >= 3 );

EXPLAIN输出关键点：

1. Using temporary：出现临时表，可能成为瓶颈
2. Using filesort：排序操作，考虑添加合适索引
3. rows列：估算扫描行数，与实际差距大时需要ANALYZE TABLE

5. 面试实战技巧

5.1 白板编码注意事项

1. 明确需求边界：

   • 处理NULL的规则（比较/聚合时）
   • 重复数据的处理逻辑（DISTINCT/GROUP BY选择）
   • 结果排序要求（即使题目未明确说明）

2. 代码风格建议：

   • CTE优先于嵌套子查询
   • 列显式命名（AS别名）
   • 适当添加注释解释复杂逻辑

3. 常见Follow-up问题：

   • "如果数据量扩大100倍会怎样？"
   • "如何验证查询结果的正确性？"
   • "请解释你选择的JOIN类型"

5.2 高频考点速查表

6. 刷题路线建议

根据面试岗位调整侧重点：

1. 数据分析岗：

   • 强化窗口函数（70%）
   • 熟悉日期处理（20%）
   • 了解PIVOT等高级特性（10%）

2. 后端开发岗：

   • 深入JOIN优化（50%）
   • 掌握索引设计（30%）
   • 理解事务隔离级别（20%）

3. 全栈工程师：

   • 平衡简单查询与复杂查询（各50%）
   • 注意SQL注入防御方案
   • 了解ORM转换原理

我个人的刷题节奏是每天3-5题，每道题至少尝试两种解法。对于特别复杂的题目，会用真实数据在本地MySQL环境验证，往往能发现理论分析时忽略的性能问题。