自连接是将同一张表视为两个逻辑独立的表进行关联，必须使用AS指定别名以区分字段来源；常见错误是未加别名和关联条件直接WHERE比较。
什么是自连接，为什么不能直接用 WHERE 比较同一行
自连接本质是把一张表当作两个逻辑独立的表来用，必须通过 AS 给别名，否则 SQL 引擎无法区分字段来源。常见错误是写成 SELECT * FROM sales WHERE amount > amount —— 这种写法既没别名也没关联条件，数据库根本不知道你在比哪两行。
做环比（比如本月 vs 上月）时，核心在于：要让当前行能“看到”它前一行的数据。这需要按时间排序后，给每行打序号或用窗口函数，再让序号错开一格去 join。
实际场景中，date 字段常有重复或缺失，不能简单靠 date = date - INTERVAL '1 month'，因为不是所有日期都存在，也不保证每月 1 号对齐。
用 ROW_NUMBER() + 自连接实现稳定环比
这是最可控的方式，尤其适合日期不连续、数据有缺失的业务表。关键点是先用窗口函数生成严格递增的序号，再让主表和副表的序号错开 1 位。
-
ROW_NUMBER() OVER (ORDER BY sale_date)必须明确排序依据，且该字段需唯一或配合其他字段去重（如ORDER BY sale_date, id） - 自连接时用
ON t1.rn = t2.rn + 1表示 t1 是“当前”，t2 是“上一条” - 别名不可省略，
t1.amount - t2.amount才能明确计算差值
SELECT t1.sale_date, t1.amount AS curr_amount, t2.amount AS prev_amount, t1.amount - COALESCE(t2.amount, 0) AS diff FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY sale_date, id) AS rn FROM sales ) t1 LEFT JOIN ( SELECT *, ROW_NUMBER() OVER (ORDER BY sale_date, id) AS rn FROM sales ) t2 ON t1.rn = t2.rn + 1;
用 LAG() 窗口函数替代自连接更简洁
如果数据库支持（PostgreSQL、SQL Server、Oracle、MySQL 8.0+），LAG() 是更优解 —— 它天然处理“向前取值”，避免显式 join 和重复子查询，性能更好、可读性更强。
注意：LAG(amount, 1) 的第二个参数是偏移量，默认就是 1，但显式写出更稳妥；第三个参数是默认值，建议用 0 或 NULL 明确控制空值行为。
- 排序必须严格一致，否则
LAG()返回结果会错乱 - 如果按月环比（非自然日序），需先用
DATE_TRUNC('month', sale_date)或YEAR(sale_date)*100 + MONTH(sale_date)归一化周期 -
LAG()不会产生额外行，而自连接在LEFT JOIN下可能带出 null 行，业务含义不同
SELECT sale_date, amount AS curr_amount, LAG(amount, 1, 0) OVER (ORDER BY sale_date, id) AS prev_amount, amount - LAG(amount, 1, 0) OVER (ORDER BY sale_date, id) AS diff FROM sales;
容易被忽略的边界问题
环比分析最容易翻车的地方不在语法，而在数据语义。比如：
- 首条记录的
prev_amount是NULL或默认值，但业务上是否允许用 0 填充？有些场景必须保留NULL表示“无可比基期” - 同一天多笔销售，
ORDER BY sale_date不足以确定顺序，会导致ROW_NUMBER()随机分配序号 —— 必须补上唯一字段如id或created_at - 使用
LAG()时若排序字段含NULL，不同数据库处理方式不同（PostgreSQL 把NULL排最前，MySQL 可能排最后），结果不可控 - 跨年环比（如 2024-01 vs 2023-01）不能靠
LAG()实现，得换JOIN或WITH RECURSIVE
真正难的从来不是写出自连接语句，而是确认“上一条”在业务上到底指什么 —— 是上一笔订单？上一个自然日？还是上一个财务周期？这个定义错了，后面全白算。
