PostgreSQL中不使用窗口函数实现LAG/LEAD的替代方案

假设你的表名为emp_salary，且需明确部门内的行排序规则（示例中按employee字段排序，你可根据实际需求替换为入职日期等业务字段），"连续行"必须有明确排序逻辑，否则结果无意义。

测试数据（可选，用于验证） #

-- 创建测试表
CREATE TABLE emp_salary (
    employee VARCHAR(50),
    department VARCHAR(50),
    salary NUMERIC
);

-- 插入测试数据
INSERT INTO emp_salary VALUES
('Alice', 'HR', 5000),
('Bob', 'HR', 5500),
('Charlie', 'HR', 5200),
('Dave', 'Engineering', 7000),
('Eve', 'Engineering', 7500);

核心查询语句 #

SELECT
    main.employee,
    main.department,
    main.salary,
    -- 首行显示0，若要显示NULL则直接用main.salary - prev.salary
    COALESCE(main.salary - prev.salary, 0) AS difference
FROM (
    -- 子查询：为每个部门内的员工生成行号（按employee排序）
    SELECT
        e1.employee,
        e1.department,
        e1.salary,
        (SELECT COUNT(*) FROM emp_salary e2 
         WHERE e2.department = e1.department AND e2.employee < e1.employee) + 1 AS row_num
    FROM emp_salary e1
) main
-- 自连接匹配同部门的上一行记录
LEFT JOIN (
    SELECT
        e1.employee,
        e1.department,
        e1.salary,
        (SELECT COUNT(*) FROM emp_salary e2 
         WHERE e2.department = e1.department AND e2.employee < e1.employee) + 1 AS row_num
    FROM emp_salary e1
) prev ON main.department = prev.department AND main.row_num = prev.row_num + 1
ORDER BY main.department, main.row_num;

逻辑说明 #

1. 生成行号：通过关联子查询COUNT(*)统计同部门内排序字段小于当前记录的行数，加1后得到当前记录在部门内的行号，以此模拟窗口函数的排序效果。
2. 自连接匹配上一行：左连接同一个带行号的子查询，匹配条件为同部门且行号比当前行小1，从而获取上一行的薪资数据。
3. 计算差值：用当前薪资减去上一行薪资，首行因无匹配记录会返回NULL，通过COALESCE转为0，若需保留NULL可直接去掉该函数。

注意事项 #

如果表中没有唯一且稳定的排序字段（如存在重复employee值），生产环境建议添加自增主键或使用业务上的唯一排序字段（如入职时间）；临时测试可使用PostgreSQL内部字段ctid，但不推荐用于生产环境。

内容的提问来源于stack exchange，提问作者Nurulloh