下面介绍 SQL 中的 10 个核心技巧，涵盖公共表表达式（CTEs）、递归查询、临时函数、数据枢转等实用方法。通过具体代码示例与模拟场景，解析如何优化查询结构、处理层级数据、实现数据转换等常见需求，帮助我们共同提升 SQL 编写效率与逻辑清晰度。

1. 公共表表达式（CTEs）(公用表表达式)

CTEs 创建临时命名的结果集，提升复杂查询的可读性和复用性。它通过 WITH 子句定义，将子查询模块化，类似于临时视图。

代码示例：

WITH toronto_ppl AS (
    SELECT DISTINCT name
    FROM population
    WHERE country = 'Canada' AND city = 'Toronto'
),
avg_female_salary AS (
    SELECT AVG(salary) AS avgSalary
    FROM salaries
    WHERE gender = 'Female'
)
SELECT p.name, p.salary
FROM People p
WHERE p.name IN (SELECT name FROM toronto_ppl)
  AND p.salary >= (SELECT avgSalary FROM avg_female_salary);

说明：

• 明确别名 p 避免歧义：主查询运用表别名 FROM People p，让列引用更加清晰。
• 子查询直接引用 CTE 名称，逻辑更清晰：子查询 toronto_ppl 借助 SELECT DISTINCT name 确保只返回不重复的名字。WHERE p.name IN (SELECT name FROM toronto_ppl) 清晰地表明了子查询要返回的列是 name。
• 保留 DISTINCT 确保数据唯一性

模拟示例：

# population 表
| name    | country | city    |
|---------|---------|---------|
| Alice   | Canada  | Toronto |
| Bob     | Canada  | Toronto |
| Charlie | USA     | New York|

# salaries 表
| salary | gender |
|--------|--------|
| 50000  | Female |
| 60000  | Female |
| 70000  | Male   |

# People 表
| name    | salary |
|---------|--------|
| Alice   | 60000  |
| Bob     | 50000  |
| Charlie | 80000  |

# 输出结果
| name  | salary |
|-------|--------|
| Alice | 60000  |  -- 满足多伦多居民且薪资>=女性平均薪资(55000)

关键点：
CTEs 将多层嵌套子查询拆解为独立模块（toronto_ppl, avg_female_salary），提升可维护性。临时表仅存在于查询生命周期内，不占用存储空间。

2. 递归 CTEs

递归 CTE 处理分层数据（如组织结构），包含三部分：

1. 锚点查询：初始结果集（如顶层管理者）
2. 递归成员：引用自身 CTE（如查找下属）
3. 终止条件：无新结果时停止

代码示例：

WITH RECURSIVE org_structure AS (
    -- 锚点：顶层管理者
    SELECT id, manager_id, 1 AS level
    FROM staff_members
    WHERE manager_id IS NULL
    UNION ALL
    -- 递归：逐级向下查找
    SELECT sm.id, sm.manager_id, os.level + 1
    FROM staff_members sm
    INNER JOIN org_structure os ON os.id = sm.manager_id
)
SELECT * FROM org_structure;

说明：

• 递归特性：使用了WITH RECURSIVE语法，这表明它是一个递归 CTE。它能够反复执行UNION ALL后面的查询部分，直到没有新的记录产生为止，从而完整地构建出整个组织架构。
• 层级表示：引入了level列，该列的值从 1 开始，每下一层级就递增 1。通过这个level列，能够清晰地反映出组织的层级结构。
• 查询结果：查询结果会包含整个组织的层级结构，从顶层管理者开始，一直到最底层的员工，所有层级的人员都会被列出。
• 虽然在语法上是正确的，但要确保staff_members表中不存在循环引用的情况（例如 A 是 B 的经理，同时 B 又是 A 的经理），否则会导致无限递归。

如果你需要查询整个组织的层级结构，就应该选择这个代码片段，因为它使用了递归 CTE，能够完整地展示从顶层到底层的所有员工及其层级关系。

模拟示例：

# staff_members 表
| id | manager_id |
|----|------------|
| 1  | NULL       |  -- CEO
| 2  | 1          |  -- 直属CEO
| 3  | 1          |  -- 直属CEO
| 4  | 2          |  -- 直属id=2

# 输出结果
| id | manager_id | level |
|----|------------|-------|
| 1  | NULL       | 1     |  -- 层级1
| 2  | 1          | 2     |  -- 层级2
| 3  | 1          | 2     |  -- 层级2
| 4  | 2          | 3     |  -- 层级3

关键点：
UNION ALL 合并迭代结果，level 列动态记录层级深度。适用于无限深度的树状结构查询。

3. 临时函数

临时函数封装复杂逻辑，提升代码复用性。在 BigQuery 中通过 CREATE TEMPORARY FUNCTION 定义。

代码示例：

CREATE TEMPORARY FUNCTION get_seniority(tenure INT64) 
RETURNS STRING AS (
    CASE 
        WHEN tenure < 1 THEN "analyst"
        WHEN tenure BETWEEN 1 AND 3 THEN "associate"
        WHEN tenure BETWEEN 3 AND 5 THEN "senior"
        WHEN tenure > 5 THEN "vp"
        ELSE "n/a"
    END
);

SELECT name, get_seniority(tenure) AS seniority
FROM employees;

说明：

• 通过 RETURNS STRING 显式声明返回类型，在需要明确指定返回类型的 SQL 方言中使用，提高代码可读性和兼容性。

模拟示例：

# employees 表
| name  | tenure |
|-------|--------|
| Alice | 0      |
| Bob   | 2      |
| Carol | 4      |
| Dave  | 6      |

# 输出结果
| name  | seniority |
|-------|-----------|
| Alice | analyst   |
| Bob   | associate |
| Carol | senior    |
| Dave  | vp        |

关键点：

• 函数仅当前会话有效
• 简化 CASE WHEN 逻辑，支持多参数（如 get_bonus(tenure, performance)）
• 兼容 SQL 标准函数（如 SUM/AVG）

4. 使用 CASE WHEN 枢转数据

行转列（Pivot）通过 CASE WHEN 配合聚合函数实现，将分类值转换为新列。

代码示例：

SELECT 
    id,
    SUM(CASE WHEN month = 'Jan' THEN revenue END) AS Jan_Revenue,
    SUM(CASE WHEN month = 'Feb' THEN revenue END) AS Feb_Revenue,
    SUM(CASE WHEN month = 'Mar' THEN revenue END) AS Mar_Revenue
FROM revenue_data
GROUP BY id;

说明：这段 SQL 查询的作用是对 revenue_data 表中的数据进行 行转列（Pivot）操作，把按月记录的收入数据转换为按 ID 聚合的横向月份格式。下面为你详细解释：

核心功能

• 数据透视：借助 CASE 表达式，把原本纵向排列的月度收入数据，转换为横向的列，像 Jan_Revenue、Feb_Revenue 等。
• 聚合统计：运用 SUM() 函数对每个 ID 在特定月份的收入进行求和。就算某个 ID 在某些月份没有收入数据，也会返回 NULL（可通过 COALESCE() 函数将其转换为 0）。

执行流程

1. 分组：按照 id 对数据进行分组，保证每个 ID 只生成一行结果。
2. 条件求和：
• 针对 month = 'Jan' 的记录，对其 revenue 进行求和，结果存入 Jan_Revenue 列。
• 同理，对 Feb 和 Mar 月份进行相同操作。
3. 结果展示：最终结果的每一行包含 id 以及对应每个月的收入数据。

模拟示例：

# revenue_data 表
| id | revenue | month |
|----|---------|-------|
| 1  | 8000    | Jan   |
| 2  | 9000    | Jan   |
| 3  | 10000   | Feb   |
| 1  | 7000    | Feb   |
| 1  | 6000    | Mar   |

# 输出结果
| id | Jan_Revenue | Feb_Revenue | Mar_Revenue |
|----|-------------|-------------|-------------|
| 1  | 8000        | 7000        | 6000        |
| 2  | 9000        | NULL        | NULL        |
| 3  | NULL        | 10000       | NULL        |

关键点：

• SUM() 聚合确保多行合并为单行
• NULL 处理缺失值（可用 COALESCE 替换为 0）
• 动态生成列需预处理月份列表（如通过元数据查询）

5. EXCEPT vs NOT IN

两者均用于比较数据集差异，但行为不同：

• EXCEPT：集合运算，去重并比较所有列
• NOT IN：单列过滤，需处理 NULL 值

代码示例：

-- EXCEPT 示例
SELECT name FROM employees 
EXCEPT 
SELECT name FROM managers;  -- 返回在employees但不在managers的名字（去重）

-- NOT IN 示例
SELECT name FROM employees
WHERE name NOT IN (SELECT name FROM managers); -- 可能受NULL值影响

说明：差异在于NULL处理逻辑：

1. EXCEPT自动过滤NULL：结果仅包含非NULL值，且自动去重。
2. NOT IN遇NULL失效：若子查询（managers.name）含NULL，整个条件返回UNKNOWN，导致主查询结果为空。
   示例：若managers存在name=NULL，则WHERE name NOT IN (...)会过滤所有记录。
   建议：用NOT IN (SELECT ... WHERE name IS NOT NULL)或NOT EXISTS规避。

模拟示例：

# employees 表       # managers 表
| name    |         | name    |
|---------|         |---------|
| Alice   |         | Bob     |
| Bob     |         | NULL    |
| Charlie |         

# EXCEPT 输出
| name    |
|---------|
| Alice   |
| Charlie |

# NOT IN 输出：无结果（因 managers 含 NULL）

关键点：

• NOT IN 子查询含 NULL 时返回空集（因 X NOT IN (NULL, ...) 恒为 UNKNOWN）
• EXCEPT 默认去重，需用 EXCEPT ALL 保留重复项

6. 自联结

自联结（Self Join）将表与自身连接，用于比较同一表内的关联数据（如员工与经理）。

代码示例：

SELECT
    emp.name AS Employee,
    emp.salary AS Employee_Salary,
    mgr.salary AS Manager_Salary
FROM Employee emp
JOIN Employee mgr ON emp.manager_id = mgr.id
WHERE emp.salary > mgr.salary;

说明：这段SQL的作用是查询薪资高于其管理者的员工，下面为你详细说明：

1. 自连接（Self-Join）

• 逻辑：把Employee表分别以员工（emp）和管理者（mgr）的身份进行两次引用，通过emp.manager_id = mgr.id建立关联。
• 目的：让每个员工记录都能关联到对应的管理者记录，从而实现薪资的对比。

2. 查询条件

• 过滤条件：WHERE emp.salary > mgr.salary，筛选出员工薪资高于其管理者薪资的记录。
• 结果：返回满足条件的员工姓名、员工薪资以及管理者薪资。

模拟输出：

# Employee 表
| id | name  | salary | manager_id |
|----|-------|--------|------------|
| 1  | Joe   | 70000  | 3          |
| 2  | Henry | 80000  | 4          |
| 3  | Sam   | 60000  | NULL       |
| 4  | Max   | 90000  | NULL       |

# 输出结果
| Employee | Employee_Salary | Manager_Salary |
|----------|-----------------|----------------|
| Joe      | 70000           | 60000          |  -- Joe薪资 > 经理Sam

关键点：

• 通过别名区分不同角色（emp vs mgr）
• 内连接过滤无经理的员工（如 CEO），管理者必须存在：JOIN默认是INNER JOIN，这意味着只有当员工的manager_id能匹配到管理者的id时，才会返回记录。若想包含管理者为空的员工，需使用LEFT JOIN。
• 数据验证：计算层级差异（如 mgr.level - emp.level）。该查询可用于发现薪资结构可能存在的异常情况，比如员工薪资不合理地高于其管理者。
• 性能优化：在manager_id和id列上创建索引，能够加快自连接的速度。

7. Rank vs Dense Rank vs Row Number

窗口函数为结果集分配序号，区别在于处理重复值的方式：

• ROW_NUMBER()：唯一序号（重复值任意排序）
• RANK()：重复值同序号，后续序号跳空
• DENSE_RANK()：重复值同序号，后续序号连续

代码示例：

SELECT 
    name,
    GPA,
    ROW_NUMBER() OVER (ORDER BY GPA DESC) AS row_num,
    RANK() OVER (ORDER BY GPA DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY GPA DESC) AS dense_rank
FROM student_grades;

再解读一下：
功能：查询学生成绩并生成排名。
窗口函数区别：

1. ROW_NUMBER()：生成唯一连续序号（1,2,3…），即使分数相同也不重复。
2. RANK()：相同分数排名相同，后续排名跳过重复数（如并列1后是3）。
3. DENSE_RANK()：相同分数排名相同，后续排名连续（如并列1后是2）。

示例：若两人GPA=4.0并列第一，ROW_NUMBER返回1、2，RANK返回1、1，DENSE_RANK返回1、1。
用途：精准定位（ROW_NUMBER）或分组统计（RANK/DENSE_RANK）。

模拟输出：

# student_grades 表
| name  | GPA |
|-------|-----|
| Alice | 3.9 |
| Bob   | 3.9 |
| Carol | 3.7 |
| Dave  | 3.5 |

# 输出结果
| name  | GPA | row_num | rank | dense_rank |
|-------|-----|---------|------|------------|
| Alice | 3.9 | 1       | 1    | 1          |
| Bob   | 3.9 | 2       | 1    | 1          |
| Carol | 3.7 | 3       | 3    | 2          |  -- RANK() 跳过2
| Dave  | 3.5 | 4       | 4    | 3          |  -- DENSE_RANK() 连续

关键点：

• OVER (ORDER BY ...) 定义排序规则
• 结合 PARTITION BY 分组计算（如按班级排名）
• 应用场景：Top N 排名（WHERE dense_rank <= 3）

8. 计算 Delta 值

LAG()/LEAD() 访问前后行数据，用于计算环比/同比差异。

代码示例：

-- 计算月环比增长
SELECT 
    month,
    revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) AS month_over_month
FROM monthly_revenue;

-- 计算年同比增长
SELECT 
    month,
    revenue,
    revenue - LAG(revenue, 12) OVER (ORDER BY month) AS year_over_year
FROM monthly_revenue;

说明：这两段 SQL 均使用窗口函数 LAG() 计算环比/同比增长率：

1. 月环比（Month-over-Month）

• LAG(revenue, 1) 取上月收入，当前月收入减去上月收入得增长额。
• 示例：若 2 月收入 120（1 月为 100），则 month_over_month = 120-100=20。

2. 年同比（Year-over-Year）

• LAG(revenue, 12) 取去年同月收入，当前月收入减去去年同月收入得增长额。
• 示例：若 2023 年 2 月收入 120（2022 年 2 月为 100），则 year_over_year = 120-100=20。

注意：

• 首月/首年数据因无历史值返回 NULL。
• 需确保 month 列按时间顺序排序（如 YYYY-MM 格式）。

模拟输出：

# monthly_revenue 表
| month   | revenue |
|---------|---------|
| 2023-01 | 100     |
| 2023-02 | 150     |
| 2024-01 | 120     |

# 月环比输出
| month   | revenue | month_over_month |
|---------|---------|------------------|
| 2023-01 | 100     | NULL             |
| 2023-02 | 150     | 50               |  -- 150 - 100
| 2024-01 | 120     | -30              |  -- 120 - 150 (需跨年)

# 年同比输出
| month   | revenue | year_over_year |
|---------|---------|----------------|
| 2023-01 | 100     | NULL           |
| 2024-01 | 120     | 20             |  -- 120 - 100

关键点：

• LAG(column, N) 取前第 N 行，LEAD() 取后第 N 行
• 跨周期计算需确保数据连续（如缺失月份需补零）

9. 计算运行总数

累积统计通过 SUM() OVER (ORDER BY) 实现，动态计算运行总数。

代码示例：

SELECT 
    month,
    revenue,
    SUM(revenue) OVER (ORDER BY month) AS cumulative_revenue
FROM monthly_revenue;

再啰嗦一下：
功能：计算每月累计收入。
窗口函数：SUM(revenue) OVER (ORDER BY month)

• 按月份排序，逐行累加当前行及之前所有月份的收入。
• 生成截至当月的总收入（即累计收入）。

示例：

用途：展示收入增长趋势，辅助财务分析。

模拟输出：

# monthly_revenue 表
| month   | revenue |
|---------|---------|
| Jan     | 100     |
| Feb     | 150     |
| Mar     | 200     |

# 输出结果
| month | revenue | cumulative_revenue |
|-------|---------|---------------------|
| Jan   | 100     | 100                 |
| Feb   | 150     | 250                 |  -- 100+150
| Mar   | 200     | 450                 |  -- 100+150+200

关键点：

• ORDER BY 决定累加顺序
• 结合 PARTITION BY 分组累计（如按产品类别）
• 扩展应用：计算移动平均值（AVG() OVER (ROWS N PRECEDING)）

10. 日期时间操纵

日期函数处理时间逻辑，常用函数包括：

• DATEDIFF(end, start)：计算日期差
• DATE_ADD(date, INTERVAL N DAY)：日期加减
• EXTRACT(YEAR FROM date)：提取时间部分

代码示例：

-- 查找温度高于前一天的日子
SELECT 
    a.id
FROM Weather a
JOIN Weather b 
    ON a.record_date = DATE_ADD(b.record_date, INTERVAL 1 DAY)
WHERE a.temperature > b.temperature;

模拟输出：

# Weather 表
| id | record_date | temperature |
|----|-------------|-------------|
| 1  | 2023-01-01  | 10          |
| 2  | 2023-01-02  | 25          |  -- 比前一天高
| 3  | 2023-01-03  | 20          |
| 4  | 2023-01-04  | 30          |  -- 比前一天高

# 输出结果
| id |
|----|
| 2  |
| 4  |

关键点：

• 使用显式 JOIN 替代隐式连接，避免笛卡尔积
• 日期函数需适配数据库语法（如 PostgreSQL 用 b.record_date + INTERVAL '1 day'）
• 索引 record_date 提升性能

这些 SQL 技巧从模块化查询到复杂数据处理，覆盖了日常开发中的关键场景。掌握 CTEs 的模块化、递归查询的层级处理、窗口函数的动态计算等方法，能有效简化复杂逻辑，提升代码可读性与性能。我们在实践中需注意函数兼容性与 NULL 值处理，以应对不同业务场景的需求。

阅读原文：原文链接