sql - 根据另一个表中的数据更新列值

Question

表格1

[Associate ID] [Reporting Date] [Time Quantity]
103554         2020-08-31           8
103554         2020-09-01           8
103554         2020-09-02           8

表 2

[Associate ID] [1] [2] [3]-------[31] [Month] [Year] 
103554                                  8      2020
103554                                  9      2020

我希望 SQL 查询根据 [Reporting Date] 列更新表 2。[1] 至 [31] 是表 2 中的月份中的日期。

标签： sqlsql-server

Answer 1

可怕的数据模型。但是，您可以使用聚合然后join在结果中执行此操作：

update table2
    set col_01 = t1.t1_01,
        col_02 = t1.t1_02,
        . . .
    from (select associate_id, year(reporting_date) as yyyy, month(reporting_date) as mm,
                 sum(case when day(reporting_date) = 1 then time_quantity end) as tq_01,
                 sum(case when day(reporting_date) = 2 then time_quantity end) as tq_02,
                 . . .
          from table1
          group by associate_id, year(reporting_date) as yyyy, month(reporting_date)
         ) t1
         on t1.associate_id = table2.associate_id and
            t1.yyyy = table2.year and
            t1.mm = table2.month;