MySQL实现新增列：取当前值的下一个最近较大值（最大值设为inf）

针对你的需求，我们可以通过两种实用的SQL写法来实现——关联子查询或者窗口函数+CTE，下面分别详细说明：

方法一：关联子查询（通用SQL，适配大多数数据库） #

这种写法逻辑直观，几乎所有支持子查询的数据库（MySQL、PostgreSQL、SQL Server等）都能运行：

SELECT
    id,
    value,
    COALESCE(
        (SELECT MIN(value) FROM a WHERE value > t.value),
        'inf'
    ) AS shiftedValue
FROM a t;

逻辑拆解： #

• 子查询(SELECT MIN(value) FROM a WHERE value > t.value)的作用是：找到比当前行value大的所有值里最小的那个，也就是最接近当前值的目标值。
• COALESCE()函数专门处理最大值的特殊情况：当当前行是所有value中的最大值时，子查询会返回NULL，这时候我们把它替换成'inf'。

不同数据库的inf写法调整： #

• MySQL：直接用'inf'即可，也可以用CAST('inf' AS DECIMAL)保证和value的数值类型一致
• PostgreSQL：使用'infinity'
• SQL Server：可以用CAST('inf' AS FLOAT)，如果不需要严格的inf符号，也可以用一个足够大的数值替代（比如999999999）

方法二：窗口函数+CTE（适合支持窗口函数的数据库） #

如果你的数据库支持窗口函数（比如PostgreSQL 9.4+、MySQL 8.0+、SQL Server 2012+），这种写法效率更高：

WITH sorted_unique_values AS (
    -- 先提取去重后的value并按升序排列
    SELECT DISTINCT value 
    FROM a 
    ORDER BY value
)
SELECT
    a.id,
    a.value,
    COALESCE(
        LEAD(suv.value) OVER (ORDER BY suv.value),
        'inf'
    ) AS shiftedValue
FROM a
JOIN sorted_unique_values suv ON a.value = suv.value;

逻辑拆解： #

1. 首先通过CTEsorted_unique_values得到去重并按升序排列的value列表
2. 使用LEAD()窗口函数，获取每个value在排序后列表中的下一个值——也就是比它大的最接近值
3. 同样用COALESCE()把最大值对应的NULL替换成'inf'

示例输出 #

针对你提供的测试数据，两种方法都会得到如下结果：

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