写SQL写到一定阶段，你会发现单靠SELECT加WHERE已经不够用了。排名、分组汇总、条件分支、多表合并这些需求，往往要靠一些相对特殊的语句才能优雅地解决。这篇笔记把平时常用的几类特殊SQL语法整理出来，配上示例和踩坑记录，方便随时翻阅。
窗口函数：让每一行都能看到全局信息
窗口函数是SQL中非常强大但也容易被忽视的一类特殊语句。普通的GROUP BY聚合会把多行压缩成一行，而窗口函数可以在保留每一行明细的同时，计算出与该行相关的聚合值或排名值。它的语法结构是在函数后面加上OVER子句，通过PARTITION BY指定分区，ORDER BY指定排序规则。
最典型的场景是排名。假设有一张学生成绩表student，包含姓名和分数两列，想给每个学生按分数排名，用GROUP BY是做不到的，但窗口函数可以轻松实现：
SELECT
name,
score,
ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
RANK() OVER (ORDER BY score DESC) AS rank_num,
DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank_num
FROM student;这三个函数的区别值得注意：ROW_NUMBER给出严格连续的行号，即使分数相同也会依次编号；RANK在遇到相同分数时会并列，但会跳过后续名次，比如两个并列第一后，下一个直接是第三名；DENSE_RANK同样并列，但不跳号，下一个是第二名。另外，取每个分组的前N条记录也是窗口函数的经典用法，先用PARTITION BY分组并用ROW_NUMBER编号，再在外层查询中过滤编号小于等于N的行即可，这比子查询嵌套的写法清晰得多。
除了排名，聚合窗口函数也很实用，比如SUM(...) OVER(PARTITION BY ...)可以计算累计值。常见误区是忘记分区条件导致全表聚合，结果每行都拿到一样的总数，排查时要重点检查OVER子句里的PARTITION BY是否写对。
WITH子句：给复杂查询起个名字
当查询逻辑变复杂时，一层层嵌套的子查询会让SQL变得难以阅读。WITH子句（也叫CTE，公共表表达式）允许你先定义一个临时的命名结果集，再在主查询中反复引用它。它只在当前语句执行期间存在，不会像临时表那样真正落盘。
比如要统计每个部门的平均薪资，再找出高于本部门平均值的员工，用WITH写法结构非常清晰：
WITH dept_avg AS (
SELECT dept_id, AVG(salary) AS avg_salary
FROM employee
GROUP BY dept_id
)
SELECT e.name, e.salary, e.dept_id
FROM employee e
JOIN dept_avg d ON e.dept_id = d.dept_id
WHERE e.salary > d.avg_salary;WITH还可以定义多个CTE，用逗号分隔，后面的CTE可以引用前面定义过的CTE，形成链条式的处理逻辑。在处理递归数据时（比如组织架构树、菜单树），还可以使用RECURSIVE关键字实现递归查询，从根节点出发逐层向下查找，这是普通子查询几乎无法优雅完成的任务。需要注意，部分数据库（如MySQL 8.0之前的版本）不支持CTE，跨库迁移时要做兼容性确认。
CASE WHEN与集合操作：条件逻辑与结果合并
CASE WHEN是SQL中的条件分支语句，相当于编程语言里的if-else。它有两种形式：简单CASE对某个字段做等值匹配，搜索CASE则可以写任意条件表达式，实际开发中搜索CASE用得更多。典型场景包括行转列、条件统计等，比如按性别统计人数：
SELECT
SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS male_count,
SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female_count
FROM student;再看集合操作。UNION和UNION ALL都能把多个查询结果纵向拼接，区别在于UNION会去重并隐式排序，开销较大；UNION ALL保留所有行，包括重复数据，性能明显更好。如果确定两个结果集没有交集，或者业务上允许重复，优先用UNION ALL。此外，在子查询判断存在性时，EXISTS和IN也容易混淆：EXISTS走的是存在性判断，找到一条匹配就停止扫描，通常在大表驱动小表时表现更好；IN则更适合子查询结果集较小且外部表较大的情况。两者在遇到NULL值时语义还有细微差别，写SQL时最好显式处理NULL，避免出现结果不符合预期的隐蔽问题。
总的来说，这些特殊语句各有分工：窗口函数解决行级计算，CTE简化复杂逻辑，CASE WHEN处理条件分支，集合操作合并多组结果。掌握它们之后，很多原本要靠存储过程或者应用程序代码完成的逻辑，都可以直接下沉到SQL层，既减少了数据传输，也提升了整体执行效率。