SQL Server 2014中用SELECT计算Transparencia累加列的问题

Got it, let's figure out how to fix that cumulative calculation issue you're hitting. The problem with relying solely on LAG() here is that it only pulls the previous row's value—once you get past the second row, there's no way to carry that accumulated total forward without a way to chain the calculations. Luckily, we can solve this with a recursive CTE (Common Table Expression) using just a SELECT statement, no stored procedures needed.

The Core Approach: Recursive CTE #

Recursive CTEs let us build results incrementally: we start with the first row (our anchor), then repeatedly calculate each subsequent row using the result from the prior iteration. This perfectly matches your rule where each row's Transparencia depends on the previous row's Transparencia and Dif.

First, make sure your table has a column that defines a clear row order (like an id, timestamp, or sequence number)—without this, the cumulative calculation will be unpredictable.

Example Code (Works for SQL Server, PostgreSQL, MySQL 8+, etc.)

-- First, assign row numbers to ensure consistent ordering
WITH numbered_data AS (
    SELECT
        *,
        ROW_NUMBER() OVER (ORDER BY your_sort_column) AS row_num
    FROM your_table
),
-- Recursive CTE to build the cumulative values
recursive_calc AS (
    -- Anchor member: Get the first row with its initial Transparencia value
    SELECT
        row_num,
        Dif,
        Transparencia
    FROM numbered_data
    WHERE row_num = 1

    UNION ALL

    -- Recursive member: Calculate each subsequent row using the prior row's values
    SELECT
        nd.row_num,
        nd.Dif,
        rc.Transparencia + rc.Dif AS Transparencia
    FROM numbered_data nd
    JOIN recursive_calc rc ON nd.row_num = rc.row_num + 1
)
-- Final output: Join back to get all original columns (if needed)
SELECT
    nd.your_sort_column,
    nd.Dif,
    rc.Transparencia
FROM numbered_data nd
JOIN recursive_calc rc ON nd.row_num = rc.row_num
ORDER BY nd.row_num;

Why This Works #

• Anchor Member: We start with the first row, which has the initial Transparencia value you already have.
• Recursive Member: For each subsequent row, we join to the previous iteration's result (where row_num is exactly one less) and compute the new Transparencia as the sum of the prior row's Transparencia and Dif. This chains the calculation forward through every row.

Key Notes #

• Replace your_table and your_sort_column with your actual table name and the column that defines row order (e.g., id, transaction_date).
• If your database uses TOP 1 instead of LIMIT (like SQL Server), you can adjust the anchor member to use SELECT TOP 1 ... instead of filtering by row_num = 1.

This method stays strictly within a SELECT statement and handles the full cumulative calculation correctly, unlike the single LAG() call which can only reach back one row.

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