mysql - 在MySQL中按条款进行分组更新

我有一张如下所示的表格

 ID| EMP_NO| EMP_NAME| YEARMONTH| CURR_MTH_PAY_AMT| TOTAL_CURR_MTH_PAY_AMT| CURR_MTH_DED_AMT| TOTAL_CURR_MTH_DED_AMT
  1| 1      | 'Alan'  | 201712   | 100             | NULL                  |  50             | NULL
  2| 1      | 'Alan'  | 201712   | 200             | NULL                  |  70             | NULL
  3| 1      | 'Alan'  | 201712   | 300             | NULL                  |  80             | NULL
  4| 1      | 'Alan'  | 201801   | 1000            | NULL                  | 500             | NULL
  5| 1      | 'Alan'  | 201801   | 2000            | NULL                  | 700             | NULL
  6| 1      | 'Alan'  | 201801   | 3000            | NULL                  | 800             | NULL
  7| 1      | 'Alan'  | 201801   | 4000            | NULL                  | 700             | NULL
  8| 2      | 'Bob'   | 201712   | 400             | NULL                  | 50              | NULL
  9| 2      | 'Bob'   | 201712   | 500             | NULL                  | 60              | NULL
 10| 2      | 'Bob'   | 201712   | 600             | NULL                  | 70              | NULL
 11| 2      | 'Bob'   | 201802   | 700             | NULL                  | 70              | NULL
 12| 2      | 'Bob'   | 201802   | 800             | NULL                  | 80              | NULL
 13| 2      | 'Bob'   | 201802   | 900             | NULL                  | 90              | NULL
 14| 2      | 'Bob'   | 201802   | 900             | NULL                  | 90              | NULL

我正在尝试为每个员工更新Employee_Detail和TOTAL_CURR_MTH_PAY_AMT as SUM(CURR_MTH_PAY_AMT)，以便我的表如下所示。

ID| EMP_NO| EMP_NAME| YEARMONTH| CURR_MTH_PAY_AMT| TOTAL_CURR_MTH_PAY_AMT| CURR_MTH_DED_AMT| TOTAL_CURR_MTH_DED_AMT
  1| 1      | 'Alan'  | 201712   | 100             | 600                  |  50             | 200
  2| 1      | 'Alan'  | 201712   | 200             | 600                  |  70             | 200
  3| 1      | 'Alan'  | 201712   | 300             | 600                  |  80             | 200
  4| 1      | 'Alan'  | 201801   | 1000            |10000                 | 500             | 2700
  5| 1      | 'Alan'  | 201801   | 2000            |10000                 | 700             | 2700
  6| 1      | 'Alan'  | 201801   | 3000            |10000                 | 800             | 2700
  7| 1      | 'Alan'  | 201801   | 4000            |10000                 | 700             | 2700
  8| 2      | 'Bob'   | 201712   | 400             | 1500                 | 50              | 180
  9| 2      | 'Bob'   | 201712   | 500             | 1500                 | 60              | 180
 10| 2      | 'Bob'   | 201712   | 600             | 1500                 | 70              | 180
 11| 2      | 'Bob'   | 201802   | 700             | 3300                 | 70              | 330
 12| 2      | 'Bob'   | 201802   | 800             | 3300                 | 80              | 330
 13| 2      | 'Bob'   | 201802   | 900             | 3300                 | 90              | 330
 14| 2      | 'Bob'   | 201802   | 900             | 3300                 | 90              | 330

我试过在Mysql上使用这个查询

update Employee_Detail set TOTAL_CURR_MTH_PAY_AMT = SUM(CURR_MTH_PAY_AMT) , TOTAL_CURR_MTH_DED_AMT = SUM(CURR_MTH_DED_AMT)
group by YYYYMM, EMP_NO;

但它给了我这个错误
TOTAL_CURR_MTH_DED_AMT as SUM(CURR_MTH_DED_AMT)
我无法将group by子句与update一起使用。任何帮助都将不胜感激。

最佳答案

您不能将group by用于update，而是可以尝试：

update Employee_Detail
join (
    select EMP_NO, YEARMONTH, sum(CURR_MTH_PAY_AMT) TOTAL_CURR_MTH_PAY_AMT, SUM(CURR_MTH_DED_AMT) TOTAL_CURR_MTH_DED_AMT
    from Employee_Detail
    group by EMP_NO, YEARMONTH
) t on Employee_Detail.EMP_NO = t.EMP_NO AND Employee_Detail.YEARMONTH = T.YEARMONTH
set Employee_Detail.TOTAL_CURR_MTH_PAY_AMT = t.TOTAL_CURR_MTH_PAY_AMT,
Employee_Detail.TOTAL_CURR_MTH_DED_AMT = t.TOTAL_CURR_MTH_DED_AMT

子查询将获取每个组的totalCURR_MTH_PAY_AMT和totalCURR_MTH_DED_AMT，然后更新每个记录。
SQLFiddle DEMO这里。

关于mysql - 在MySQL中按条款进行分组更新，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/47785146/