SQL实现逐行累加：将Table A的ID列累加生成新列

Hey there! Calculating a running total (cumulative sum) for your Table A—where id_new is the sum of all ID values up to and including the current row—is a common task, and there are a few solid ways to tackle it depending on your SQL environment. Let's start with the most efficient and clean approach first.

1. Using Window Functions (Recommended) #

Most modern SQL databases (like MySQL 8+, PostgreSQL, SQL Server, Oracle) support window functions, which are the fastest and most readable way to compute a running total. Here's the query:

SELECT 
    ID,
    SUM(ID) OVER (ORDER BY ID) AS id_new
FROM TableA; -- Note: If your table is named "Table A" (with a space), wrap it in quotes like "Table A"

How it works: #

The OVER (ORDER BY ID) clause tells the database to calculate the sum incrementally, ordering rows by ID to ensure the cumulative sum follows the correct sequence. For your sample data (IDs 1,2,3,4), this will return exactly the results you need:

This method is optimal because it runs in a single pass over the data, making it much faster than procedural methods for large datasets.

2. Correlated Subquery (For Older Databases) #

If you're working with an older SQL dialect that doesn't support window functions (like MySQL 5.x), a correlated subquery will get the job done. Keep in mind this is less efficient for large tables since it runs a subquery for every row:

SELECT 
    t1.ID,
    (SELECT SUM(t2.ID) FROM TableA t2 WHERE t2.ID <= t1.ID) AS id_new
FROM TableA t1
ORDER BY t1.ID;

3. Cursor Method (Procedural Approach) #

As you mentioned, you can use a cursor to iterate through each row and build the running total manually. This is a procedural method and not ideal for large datasets, but it's useful if you need more control over the process. Here's an example for SQL Server:

DECLARE @RunningTotal INT = 0;
DECLARE @TempResults TABLE (ID INT, id_new INT);

-- Declare cursor to fetch IDs in order
DECLARE id_cursor CURSOR FOR
SELECT ID FROM TableA ORDER BY ID;

DECLARE @CurrentID INT;
OPEN id_cursor;

-- Iterate through each row
FETCH NEXT FROM id_cursor INTO @CurrentID;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @RunningTotal = @RunningTotal + @CurrentID;
    INSERT INTO @TempResults (ID, id_new) VALUES (@CurrentID, @RunningTotal);
    FETCH NEXT FROM id_cursor INTO @CurrentID;
END;

-- Clean up cursor
CLOSE id_cursor;
DEALLOCATE id_cursor;

-- Return the final results
SELECT * FROM @TempResults;

Key Notes: #

• Always prefer window functions when available—they're the most efficient and maintainable option.
• If your table has duplicate ID values, adjust the ORDER BY clause to include a unique column (like a timestamp) to ensure consistent ordering.

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