1. SQL 基础语法与查询优化力扣LeetCode数据库题目大多基于 MySQL 语法熟练掌握 SQL 基础是解题的前提。1.1 SELECT 语句与聚合函数SELECT数据查询的核心配合WHERE、GROUP BY、HAVING、ORDER BY、LIMIT使用。聚合函数COUNT()、SUM()、AVG()、MAX()、MIN()常用于统计类题目。DISTINCT去重常与聚合函数结合如COUNT(DISTINCT column)。1.2 多表连接JOININNER JOIN返回两个表中匹配的行。LEFT/RIGHT JOIN返回左表/右表的所有行即使另一表没有匹配。自连接Self Join同一表内连接常用于比较行间关系。1.3 子查询与 CTE标量子查询返回单个值的子查询可用于SELECT、WHERE、HAVING。关联子查询子查询引用外层查询的列性能需注意。公用表表达式CTE使用WITH定义临时结果集提高复杂查询的可读性。2. 窗口函数Window Functions窗口函数是解决排名、累计、移动平均等问题的利器不减少原表行数。2.1 排名函数排名函数是 SQL 窗口函数中最常用的一类用于为结果集中的行分配一个排名序号。它们必须与OVER()子句一起使用通过PARTITION BY分组和ORDER BY排序来定义排名规则。语法概览ROW_NUMBER() OVER ( [PARTITION BY partition_expression] ORDER BY order_expression [ASC|DESC] ) AS row_num RANK() OVER ( [PARTITION BY partition_expression] ORDER BY order_expression [ASC|DESC] ) AS rank DENSE_RANK() OVER ( [PARTITION BY partition_expression] ORDER BY order_expression [ASC|DESC] ) AS dense_rank函数详解与对比ROW_NUMBER()为每一行分配一个连续且不重复的序号即使排序值相同并列也会得到不同的序号如 1, 2, 3, 4。RANK()为每一行分配一个排名序号。如果排序值相同并列则并列的行获得相同的排名并且后续排名会跳过并列占用的序号如 1, 1, 3, 4。DENSE_RANK()与RANK()类似并列的行获得相同的排名但后续排名不会跳过序号保持连续如 1, 1, 2, 3。典型应用场景ROW_NUMBER()为结果集生成一个唯一的行号。实现分页查询配合WHERE row_num BETWEEN ...。标记重复记录配合PARTITION BY所有列row_num 1即为重复。RANK() / DENSE_RANK()计算排名如“销售额排名”、“成绩排名”。解决“第N高/第N大”问题如“第二高的薪水”。分析数据分布如“前10%的用户”。示例学生成绩排名-- 假设有学生成绩表 scores(student_id, subject, score) SELECT student_id, subject, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num, RANK() OVER (ORDER BY score DESC) AS rank, DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank FROM scores WHERE subject Math ORDER BY score DESC;假设成绩为 [100, 100, 95, 90]查询结果将如下所示student_idsubjectscorerow_numrankdense_rank101Math100111102Math100211103Math95332104Math90443关键观察两个100分并列第一。ROW_NUMBER()给了它们不同的序号1和2。RANK()给了它们相同的排名1但下一个分数95分的排名跳过了2变成了3。DENSE_RANK()也给了它们相同的排名1但下一个分数95分的排名是连续的2。在力扣中的应用第N高薪水第177题使用DENSE_RANK()或ROW_NUMBER()配合子查询或CTE来找出第N高的唯一薪水。部门工资最高的员工第184题使用RANK()或DENSE_RANK()按部门分区按工资降序排名然后筛选排名为1的记录。删除重复邮箱第196题使用ROW_NUMBER()按邮箱分区按id排序标记重复行row_num 1后删除。2.2 聚合窗口函数SUM() OVER()、AVG() OVER()计算分区内的累计或移动平均值。常与PARTITION BY和ORDER BY搭配使用。2.3 前后行函数LAG()和LEAD()是 SQL 窗口函数中用于访问当前行之前LAG或之后LEAD行数据的函数常用于比较相邻记录、计算差值或检测连续性问题。语法LAG(column, offset, default_value) OVER ( [PARTITION BY partition_expression] ORDER BY order_expression [ASC|DESC] ) LEAD(column, offset, default_value) OVER ( [PARTITION BY partition_expression] ORDER BY order_expression [ASC|DESC] )column要访问的列。offset可选向前或向后偏移的行数默认为 1。default_value可选当偏移超出分区范围时返回的默认值默认为NULL。PARTITION BY可选将数据分成多个分区函数在每个分区内独立计算。ORDER BY必需定义分区内行的顺序。核心用途比较相邻行计算当前行与上一行/下一行的差值如温度变化、销售额环比。检测连续性判断日期、ID 是否连续如连续登录、连续出现。填充缺失值用前一行或后一行的值填充当前行的NULL。此查询会为每一行添加前一日的温度prev_temp以及温度差temp_diff。LAG() 与 LEAD() 的区别LAG()访问之前的行“向后看”。LEAD()访问之后的行“向前看”。两者语法和参数完全一致只是方向相反。注意事项必须与OVER()子句一起使用且ORDER BY是必需的。合理使用PARTITION BY可以按组如部门、用户分别计算前后行。当数据边界如第一行没有前一行时注意处理default_value或NULL。示例连续出现的数字LAG(num,1) OVER(ORDER BY id) AS pre num:要拿的那列的值1往前偏移1行默认为1可以省略按照id移动3. 日期处理与条件判断3.1 日期函数DATE_FORMAT()格式化日期。DATEDIFF()、TIMESTAMPDIFF()计算日期差。DATE_ADD()、DATE_SUB()日期加减。YEAR()、MONTH()、DAY()提取日期部分。DATEDIFF() 函数详解DATEDIFF(date1, date2)函数用于计算两个日期之间的天数差。语法DATEDIFF(end_date, start_date)返回值返回end_date减去start_date的天数整数。如果end_date早于start_date则返回负数。参数参数可以是日期、日期时间或可隐式转换为日期的字符串。注意它只计算日期部分的天数差忽略时间部分。DATEDIFF() 与 TIMESTAMPDIFF() 的区别DATEDIFF()仅返回两个日期之间的天数差整数。TIMESTAMPDIFF(unit, start_datetime, end_datetime)返回两个日期时间之间指定单位如 SECOND, MINUTE, HOUR, DAY, MONTH, YEAR的差值整数计算更精确。示例计算订单间隔天数-- 假设有订单表 orders(order_id, user_id, order_date) -- 计算每个用户相邻订单之间的天数差 SELECT user_id, order_date, LAG(order_date) OVER (PARTITION BY user_id ORDER BY order_date) AS prev_order_date, DATEDIFF( order_date, LAG(order_date) OVER (PARTITION BY user_id ORDER BY order_date) ) AS days_since_last_order FROM orders ORDER BY user_id, order_date;此查询会为每一行订单添加上一笔订单的日期并计算两次订单间隔的天数。3.2 条件表达式CASE WHEN实现条件分支是复杂逻辑处理的必备。IF()、IFNULL()、COALESCE()处理空值和简单条件。4. 高级技巧与解题思路4.1 第N高/第N大问题使用DISTINCT、LIMIT、OFFSET或窗口函数DENSE_RANK()连续排名解决。LIMIT 1 OFFSET N ---- 跳过N条取1条连续排名4.2 连续出现/连续登录问题常用自连接、窗口函数LAG/LEAD或变量法判断连续性。4.3 行列转换Pivot使用CASE WHEN配合聚合函数实现。4.4 删除重复记录使用ROW_NUMBER()标记重复行后删除或使用DELETE配合自连接。5. 性能优化与索引避免 SELECT *只查询需要的列。合理使用索引在WHERE、JOIN、ORDER BY涉及的列上建立索引。理解执行计划使用EXPLAIN分析查询效率。6. 常见题型与例题推荐题型力扣题号核心知识点第N高薪水177DISTINCT, LIMIT, 窗口函数连续出现N次的数字180自连接/窗口函数部门工资最高的员工184关联子查询/窗口函数删除重复邮箱196DELETE, 自连接/ROW_NUMBER()上升的温度197日期函数, LAG()行程和用户262CASE WHEN, 聚合, 日期过滤
