使用聚合自联接更新查询

I am using a self-join to get year-to-date totals. This works:

我使用自我加入来获得年初至今的总数。这有效:

SELECT tc.EmployeeID, tc.TimeCardNum, tc.Tax, SUM(inr.Tax) AS YTDTax
FROM TimeCards tc
JOIN (
        SELECT EmployeeID, TimeCardNum, Tax
        FROM TimeCards  
) AS inr
ON inr.EmployeeID = tc.EmployeeID
AND inr.TimeCardNum <= tc.TimeCardNum
GROUP BY tc.EmployeeID, tc.TimeCardNum, tc.Tax
ORDER BY tc.EmployeeID, tc.TimeCardNum

which gives me a running total column that resets for each employee.

这给了我一个运行的总列,可以为每个员工重置。

Now I want to turn it into an UPDATE query to put that data into a new column. How is this done?

现在我想将其转换为UPDATE查询以将该数据放入新列。这是怎么做到的?

1 个解决方案

#1

Have you tried:

你有没有尝试过:

UPDATE a
SET a.YTDTax=b.YTDTax
FROM TimeCards a
INNER JOIN (
    SELECT tc.EmployeeID, tc.TimeCardNum, tc.Tax, SUM(inr.Tax) AS YTDTax
    FROM TimeCards tc
    JOIN (
            SELECT EmployeeID, TimeCardNum, Tax
            FROM TimeCards  
    ) AS inr
    ON inr.EmployeeID = tc.EmployeeID
    AND inr.TimeCardNum <= tc.TimeCardNum
    GROUP BY tc.EmployeeID, tc.TimeCardNum, tc.Tax
) b on a.EmployeeID=b.EmployeeID
AND a.TimeCardNum = b.TimeCardNum

Run in a transaction to ensure it produces the desired output.

在事务中运行以确保它生成所需的输出。

#1