如何用SQL按行值条件对前置行的Value字段进行区间求和

原始数据 #

需求说明 #

针对start_row不为空的行，计算满足以下两个条件的行的value总和：

1. 目标行的id介于当前行start_row和end_row之间（包含两端）
2. 目标行的from或to字段与当前行的from字段一致

实现方案 #

方案1：自连接查询（通用SQL写法） #

通过表自连接筛选符合条件的记录，再分组求和，适配绝大多数数据库：

SELECT
    main.id AS current_id,
    main.from AS current_from,
    SUM(target.value) AS total_value
FROM
    your_table main
LEFT JOIN
    your_table target ON
        target.id BETWEEN main.start_row AND main.end_row
        AND (target.from = main.from OR target.to = main.from)
WHERE
    main.start_row IS NOT NULL
GROUP BY
    main.id, main.from;

执行结果验证：

• ID=5的行：匹配ID1（from=A）、ID4（to=A）、ID5（from=A），总和为20+50+70=140
• ID=6的行：匹配ID2（from=C）、ID3（to=C）、ID6（from=C），总和为30+100+20=150

方案2：窗口函数实现（适用于支持窗口函数的数据库） #

如果你的数据库支持窗口函数（如PostgreSQL、MySQL 8.0+、SQL Server等），可以用以下写法避免自连接：

SELECT
    id,
    `from`,
    SUM(CASE
        WHEN id BETWEEN start_row AND end_row
             AND (`from` = current_from OR `to` = current_from)
        THEN value
        ELSE 0
    END) OVER (PARTITION BY id) AS total_value
FROM (
    SELECT
        *,
        `from` AS current_from
    FROM your_table
) t
WHERE start_row IS NOT NULL;

这个方法先将当前行的from字段缓存为current_from，再通过窗口函数针对每一行计算符合条件的value总和，结果和自连接完全一致。

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