PostgreSQL 如何基于同表前序最高值更新指定行字段

以下代码中请将your_table_name替换为你的实际表名

方法1：窗口函数法（支持MySQL 8.0+/PostgreSQL/Oracle/SQL Server等所有支持窗口函数的数据库） #

核心思路：先提取所有不重复的high_value，为每个值计算对应的low_value，再关联回原表完成更新，避免重复值计算出错。
先运行查询语句验证结果是否符合预期：

WITH distinct_high AS (
    SELECT 
        high_value,
        LAG(high_value, 1, 0) OVER (ORDER BY high_value) AS calc_low
    FROM your_table_name
    GROUP BY high_value
)
SELECT 
    t.id,
    dh.calc_low AS low_value,
    t.high_value
FROM your_table_name t
JOIN distinct_high dh ON t.high_value = dh.high_value
ORDER BY t.id;

验证结果正确后执行更新语句：

MySQL更新语法： #

UPDATE your_table_name t
JOIN (
    SELECT 
        high_value,
        LAG(high_value, 1, 0) OVER (ORDER BY high_value) AS calc_low
    FROM your_table_name
    GROUP BY high_value
) dh ON t.high_value = dh.high_value
SET t.low_value = dh.calc_low;

PostgreSQL/Oracle更新语法： #

UPDATE your_table_name t
SET low_value = dh.calc_low
FROM (
    SELECT 
        high_value,
        LAG(high_value, 1, 0) OVER (ORDER BY high_value) AS calc_low
    FROM your_table_name
    GROUP BY high_value
) dh
WHERE t.high_value = dh.high_value;

方法2：无窗口函数兼容方案（适配MySQL 5.x等不支持窗口函数的旧版本数据库） #

核心思路：通过子查询直接取小于当前high_value的最大值，最小值自动返回0，无需额外处理：
查询验证语句：

SELECT 
    id,
    IFNULL((SELECT MAX(high_value) FROM your_table_name t2 WHERE t2.high_value < t1.high_value), 0) AS low_value,
    high_value
FROM your_table_name t1
ORDER BY id;

更新语句：

UPDATE your_table_name t1
SET low_value = IFNULL(
    (SELECT MAX(high_value) FROM (SELECT * FROM your_table_name) t2 WHERE t2.high_value < t1.high_value),
    0
);

注意：MySQL 5.x中更新同表查询需要嵌套一层子查询绕开同表更新限制。

两种方法均满足需求：相同high_value对应的low_value完全一致，最小high_value的low_value为0，其他值取排序后前一个最大的high_value。

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