sql - 使用来自不同表的记录的列的总和更新列值

Question

我有两张表 payroll_est_act，actual_payroll_history

payroll_est_act

id| location_id     | type          | amt
-------------------------------------------
1 | 1               |  Actual       | 36000  
2 | 1               |  Estimated    | 20000   
3 | 2               |  Actual       | 50000  
4 | 2               |  Estimated    | 50000 
5 | 3               |  Actual       | 24000  
6 | 3               |  Estimated    | 18000 


actual_payroll_history

id  |  payroll_est_act_id   | amt       | date
-----------------------------------------------------
1   |   1                   |   4000    |   1 June 20
2   |   1                   |   4000    |   1 July 20
3   |   1                   |   4000    |   1 Aug 20
4   |   1                   |   4000    |   1 Sep 20
5   |   2                   |   12500   |   1 June 20
6   |   2                   |   12500   |   1 July 20
7   |   2                   |   12500   |   1 Aug 20
8   |   2                   |   12500   |   1 Sep 20
9   |   3                   |   4000    |   1 June 20

如果 payroll_est_act 中的 amt 值与 actual_payroll_history 中的 amt 总和不匹配，我想编写一条更新语句，以使用来自 actual_payroll_history 的相应 payroll_est_act_id 的金额总和来更新“实际”类型的 payroll_est_act 中的金额。

请帮忙。谢谢

标签： sqlsql-servertsqlsql-update

Answer 1

我会在这里使用可更新的 CTE：

WITH cte1 AS (
    SELECT payroll_est_act_id, SUM(amt) AS amt_actual
    FROM actual_payroll_history
    GROUP BY payroll_est_act_id
),
cte2 AS (
    SELECT p.amt, t.amt_actual
    FROM payroll_est_act p
    INNER JOIN cte1 t
        ON t.payroll_est_act_id = p.location_id
    WHERE p.type = 'Actual'
)

UPDATE cte2
SET amt = amt_actual
WHERE amt <> amt_acutal;