如何用SQL获取同一列上一行数据并实现Excel式递推计算？

Got it, let's unpack this. Your Excel logic is actually calculating a running maximum—each row's C value is the largest number from the start up to that row. Let's break down how to replicate this in SQL, since SQL works differently than Excel's strict row-by-row order.

First, a quick sanity check: Looking at your sample data, the C column follows the max of all previous B values (not A, which looks like a row number). I'll assume you meant to base the calculation on column B (since the numbers match), but I'll note how to adjust if it's actually column A.

For Modern Databases (PostgreSQL 9.4+, MySQL 8.0+, SQL Server 2012+, Oracle 12c+) #

These systems support window functions, which are perfect for this kind of running calculation. You'll need to use a MAX() window function with a range that includes all rows from the start up to the current one.

SELECT
    AS row_number,
    B,
    -- Calculate running max of B, ordered by the row number (Excel's row order)
    MAX(B) OVER (
        ORDER BY A
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS C
FROM
    your_table_name;

What this does: #

• MAX(B) OVER (...): This window function computes the maximum value of B within a "window" of rows.
• ORDER BY A: Ensures we follow the same row order as Excel (since SQL tables don't have a default inherent order). Use your actual row-ordering column here (could be a timestamp, unique ID, etc.).
• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: Defines the window as every row from the first one up to the current row—exactly matching your Excel formula's recursive logic.

If you did mean to base the calculation on column A instead of B, just replace MAX(B) with MAX(A).

For Older Databases (e.g., MySQL 5.x) #

If your database doesn't support window functions, you can use user-defined variables to mimic the recursive Excel calculation:

-- Initialize a variable to track the running max
SET @running_max = NULL;

SELECT
    A,
    B,
    -- Update the variable with the max of its current value and B, then return it as C
    @running_max := GREATEST(@running_max, B) AS C
FROM
    your_table_name
-- Critical: Order by the row number to match Excel's sequence
ORDER BY A;

Key Note: #

No matter which method you use, always specify an explicit ORDER BY clause that matches the row order you used in Excel. Without this, SQL will return rows in an arbitrary order, and your running max will be incorrect.

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