如何基于employeeidno用WHILE LOOP实现SQL Server行级累加更新？

The main problem with your original code is that the @total (or @aremployee in your production query) variable is global—it's shared across all employees. That's why every row ends up with the same cumulative sum instead of individual totals per employeeid/employeeidno. To fix this, we need to track running totals separately for each employee, not with a single variable.

Step-by-Step Solution for Your Simplified Example #

Here's how to adjust your code to handle per-employee accumulation, stopping when the total exceeds your 25000 threshold:

DROP TABLE IF EXISTS #temtable1;
DROP TABLE IF EXISTS #temtable2;
DROP TABLE IF EXISTS #EmployeeRunningTotals; -- Temp table to track per-employee sums

-- Create and populate source tables
CREATE TABLE #temtable1 ( 
    referenceno int, 
    employeeid varchar(10), 
    ccode varchar(10), 
    amount float(10) 
);
INSERT INTO #temtable1 (referenceno,employeeid,ccode,amount) 
VALUES
('1001','2001','3001','11000.00'),
('1003','2002','3002','11000.00'),
('1005','2003','3003','11000.00'),
('1001','2001','3004','10000.00'),
('1003','2002','3005','10000.00'),
('1005','2003','3006','10000.00');

CREATE TABLE #temtable2 ( 
    referenceno int, 
    employeeid varchar(10), 
    total float(10) 
);
INSERT INTO #temtable2 (referenceno,employeeid,total) 
VALUES
('1001','2001',NULL),
('1002','2002',NULL),
('1003','2003',NULL);

-- Temp table to track running totals PER EMPLOYEE (instead of global variable)
CREATE TABLE #EmployeeRunningTotals (
    employeeid varchar(10) PRIMARY KEY,
    current_total float(10) DEFAULT 0
);
-- Initialize with all employees from your target table
INSERT INTO #EmployeeRunningTotals (employeeid)
SELECT employeeid FROM #temtable2;

DECLARE @cnt INT = 0;
WHILE @cnt <= 10
BEGIN
    SET @cnt = @cnt + 1;
    DECLARE @current_ccode varchar(10) = CONCAT('30', CASE WHEN @cnt < 10 THEN '0' ELSE '' END , @cnt);

    -- Update only if adding the batch amount doesn't exceed the 25000 threshold
    WITH CurrentBatch AS (
        SELECT 
            t1.employeeid,
            SUM(t1.amount) AS batch_amount
        FROM #temtable1 t1
        WHERE t1.ccode = @current_ccode
        GROUP BY t1.employeeid
    )
    UPDATE ert
    SET 
        ert.current_total = ert.current_total + cb.batch_amount,
        t2.total = ert.current_total + cb.batch_amount
    FROM #EmployeeRunningTotals ert
    JOIN CurrentBatch cb ON ert.employeeid = cb.employeeid
    JOIN #temtable2 t2 ON ert.employeeid = t2.employeeid
    WHERE ert.current_total + cb.batch_amount <= 25000; -- Stop when threshold is hit
END

SELECT * FROM #temtable2;

Key Changes Explained #

• Per-Employee Tracking: We use #EmployeeRunningTotals to store a separate running total for each employeeid, ensuring each employee's sum stays independent of others.
• Threshold Guard: In each loop, we only update the total if adding the current batch amount keeps us under 25000. Once an employee's total hits or exceeds the threshold, they won't get further updates in subsequent loops.
• Clean Batch Logic: We first calculate the sum for the current ccode per employee, then join it with the running totals table to update both the running total and the target table in one step.

Adapting This to Your Original Production Code #

For your accounting_tblpayroll_db update, apply the same pattern:

1. Create a temp table to track employeeidno and their current aremployee running totals.
2. Initialize it with all relevant employees from accounting_tblpayroll_db (matching your datefrom/dateto range).
3. In each WHILE loop:
   • Calculate the batch sum per employeeidno for the current accountcode (using your existing grouping logic).
   • Join with the running totals temp table, check if adding the batch sum stays under your custom threshold (the long calculation in your original WHERE clause).
   • Update both the running totals temp table and accounting_tblpayroll_db.aremployee only if the threshold isn't exceeded.

This way, each employee's aremployee is accumulated independently, exactly as you need.

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