编写PostgreSQL查询：返回累加值超限时前一位人员姓名

嘿，我来帮你搞定这个问题！需求很清晰：按id的顺序累加value字段，当总和超过指定limit时，返回导致超限的那一行的前一位人员姓名。结合你的示例数据，我们可以用PostgreSQL的窗口函数轻松实现这个逻辑。

首先，先确认你的表结构和示例数据（可以直接运行这段代码创建测试表）：

CREATE TABLE staff_values (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    value INT NOT NULL
);

-- 插入示例数据
INSERT INTO staff_values VALUES
(1, 'bernard', 250),
(2, 'bernice', 300),
(3, 'bob', 250),
(4, 'buddha', 250),
(5, 'cheesy', 200),
(6, 'dog', 200);

接下来是核心查询，我会分步骤解释逻辑：

WITH running_totals AS (
    -- 第一步：计算按id顺序的累计和
    SELECT 
        id,
        name,
        SUM(value) OVER (ORDER BY id) AS cumulative_sum
    FROM staff_values
),
first_over_limit AS (
    -- 第二步：找到第一个累计和超过指定limit的行的id
    SELECT MIN(id) AS first_over_id
    FROM running_totals
    WHERE cumulative_sum > :your_limit
)
-- 第三步：关联原表，取超限行的前一位姓名
SELECT sv.name
FROM staff_values sv
JOIN first_over_limit fol ON sv.id = fol.first_over_id - 1;

使用说明 #

把:your_limit替换成你实际的限制数值即可：

• 当limit为500时，查询返回bernard（因为250+300=550超过500，超限的是bernice，前一位是bernard）
• 当limit为1000时，查询返回bob（250+300+250+250=1050超过1000，超限的是buddha，前一位是bob）

边界情况优化 #

如果所有人员的value累加后都没超过limit，上面的查询会返回空结果。如果你希望这种场景下返回最后一个人的姓名，可以用COALESCE优化：

WITH running_totals AS (
    SELECT 
        id,
        name,
        SUM(value) OVER (ORDER BY id) AS cumulative_sum
    FROM staff_values
),
first_over_limit AS (
    SELECT MIN(id) AS first_over_id
    FROM running_totals
    WHERE cumulative_sum > :your_limit
)
SELECT COALESCE(
    (SELECT sv.name FROM staff_values sv JOIN first_over_limit fol ON sv.id = fol.first_over_id - 1),
    (SELECT name FROM staff_values ORDER BY id DESC LIMIT 1)
) AS result_name;

这样不管有没有超限，都能得到符合预期的结果啦！

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