如何在SQLite中按指定列总和选取末尾N行并计算累计求和？

现有包含id、qty（数量）、price（价格）字段的foo表，建表与插入数据的SQL语句如下：

CREATE TABLE foo (id INT, qty INT, price int);
INSERT INTO foo (id, qty, price) VALUES (1, 100, 10), (2, 120, 11), (3, 150, 12), (4, 170, 13), (5, 200, 14), (6, 210, 15);

表中数据如下：

+----+-----+-------+
| id | qty | price |
+----+-----+-------+
|  1 | 100 |    10 |
|  2 | 120 |    11 |
|  3 | 150 |    12 |
|  4 | 170 |    13 |
|  5 | 200 |    14 |
|  6 | 210 |    15 |
+----+-----+-------+

针对任意数值N，需按以下规则计算derivedsum：

• 当N = 210时，derivedsum = 210*15（取id=6的记录）
• 当N = 300时，derivedsum = 210*15（id=6） + 90*14（id=5，剩余300-210=90）
• 当N = 430时，derivedsum = 210*15（id=6） + 200*14（id=5） + 20*13（id=4，剩余430-210-200=20）

核心规则：

• 优先选取id最大的记录的全部数量，用对应价格计算
• 若N大于当前记录的数量，减去该数量后继续选取下一个id次大的记录，直到剩余数值为0

请问能否通过单条SQL语句实现该需求？

可以通过单条SQL实现，核心思路是利用累积求和和条件判断计算每个记录的贡献值，最后求和得到derivedsum。

以下是适用于MySQL的实现语句（示例中N取300，可直接替换为任意目标数值）：

SELECT SUM(
    CASE
        WHEN running_total <= N THEN qty * price
        ELSE (N - (running_total - qty)) * price
    END
) AS derivedsum
FROM (
    SELECT 
        id, qty, price,
        SUM(qty) OVER (ORDER BY id DESC) AS running_total
    FROM foo
) AS t
WHERE running_total - qty < N;

逻辑说明： #

1. 子查询按id降序计算累积求和running_total，得到从最大id开始的累计数量
2. 外层通过CASE判断：
   • 若累计总量不超过N，该记录全部数量计入，计算qty*price
   • 若累计总量超过N，仅计算剩余部分的数量乘以价格，即(N - (running_total - qty))*price
3. WHERE条件过滤掉累计总量减去当前数量后仍大于等于N的记录（这类记录无需参与计算）

其他SQL方言（如PostgreSQL）语法基本一致，仅窗口函数写法略有差异，核心逻辑通用。

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