SQL中基于条件用上一行与当前行值计算ExpectedOutcome列

要实现你描述的累积计算+范围限制逻辑，核心是逐行递推计算并约束结果范围，以下是不同数据库环境下的可行方案：

1. 通用递归CTE方案（支持SQL Server、PostgreSQL、MySQL 8.0+） #

递归CTE可以清晰实现逐行依赖的计算逻辑，适合大多数现代数据库：

WITH RecursiveSessions AS (
    -- 锚点：取第一行（按No.Session+id排序，可根据实际调整排序规则）
    SELECT 
        id,
        `No.Session`,
        Score,
        GREATEST(0, LEAST(Score + 60, 100)) AS ExpectedOutcome
    FROM your_table
    WHERE `No.Session` = 1
    ORDER BY `No.Session`, id
    LIMIT 1

    UNION ALL

    -- 递归：逐行计算后续值
    SELECT 
        t.id,
        t.`No.Session`,
        t.Score,
        GREATEST(0, LEAST(rs.ExpectedOutcome + t.Score, 100)) AS ExpectedOutcome
    FROM your_table t
    INNER JOIN RecursiveSessions rs 
        ON t.id = (SELECT MIN(id) FROM your_table WHERE id > rs.id) -- 按id顺序取下一行
        -- 若Session是连续递增的，可替换为：t.`No.Session` = rs.`No.Session` + 1
)
SELECT id, `No.Session`, Score, ExpectedOutcome 
FROM RecursiveSessions 
ORDER BY id;

2. MySQL 8.0+ 变量逐行计算方案 #

如果偏好非递归写法，可利用用户变量实现：

SELECT 
    id,
    `No.Session`,
    Score,
    @expected := GREATEST(0, LEAST(
        CASE 
            WHEN `No.Session` = 1 AND @row = 1 THEN Score + 60
            ELSE @expected + Score
        END,
    100)) AS ExpectedOutcome
FROM your_table,
(SELECT @expected := 0, @row := 1) AS init_vars
ORDER BY `No.Session`, id; -- 必须指定排序，保证计算顺序正确

关键注意事项 #

• 排序规则：必须明确“第一行”“上一行”的排序依据（如id或No.Session），否则计算结果会混乱。
• 范围约束：GREATEST(0, LEAST(计算值, 100))是通用的范围限制写法，确保结果始终在0-100之间。
• 数据库适配：不同数据库语法有细微差异，比如PostgreSQL递归CTE不需要LIMIT，可直接用ROW_NUMBER()筛选第一行，按需调整即可。

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