如何使用T-SQL根据Upper_limit值计算对应Lower_limit下限值

需求说明 #

现有阈值表仅存储rank（排名）、Upper_limit（区间上限）两个字段，需要新增Lower_limit（区间下限）字段，规则为：

• rank=1的记录下限固定为0
• 其余排名的下限等于相邻上一排名的上限值
• 最终输出rank、Lower_limit、Upper_limit三个字段的结果集

通用实现方案（SQL Server 2012及以上版本，推荐使用） #

直接使用窗口函数LAG()取排序后的上一行上限值，空值替换为0即可，语句简洁执行效率高：

-- 可根据实际业务替换源表名rank_threshold
SELECT
    rank,
    Lower_limit = ISNULL(LAG(Upper_limit) OVER (ORDER BY rank), 0),
    Upper_limit
FROM rank_threshold
ORDER BY rank;

逻辑说明 #

• LAG(Upper_limit) OVER (ORDER BY rank)：按rank升序排序，直接读取当前行上一行的Upper_limit值
• ISNULL()函数处理rank=1的边界场景：rank=1没有上一行，LAG返回NULL，替换为规则要求的0
• 最终按rank排序输出，结果完全匹配预期

低版本兼容方案（SQL Server 2008R2及更早版本） #

如果使用的SQL Server版本不支持LAG窗口函数，可以通过左连接自关联实现同等效果：

SELECT
    t1.rank,
    Lower_limit = ISNULL(t2.Upper_limit, 0),
    t1.Upper_limit
FROM rank_threshold t1
LEFT JOIN rank_threshold t2 
    ON t1.rank = t2.rank + 1
ORDER BY t1.rank;

逻辑说明 #

通过左连接将表自身关联，关联条件为当前行的rank值等于关联行rank值+1，即可匹配到上一排名的记录；rank=1的行匹配不到关联记录，t2.Upper_limit为NULL，替换为0即可。

运行结果验证 #

两种语句执行后返回的结果完全一致：

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