sql - 如何计算和更新 SQL Server 表中的小计

Question

我有一个以下格式的 SQL Server 表：Type列是指示行是小计还是行级值。

如果 type 为 1，则为行级成本，如果 type 为 2，则为小计，如果 type 为 3，则为总计。

Category   Subcategory      Option1   Option2   Option3   Option4    Type
----------------------------------------------------------------------------
Insurance  Insurance Cost   10        20        30        40         1
Insurance  Insurance Tax    10        20        30        40         1
Insurance  Subtotal         0         0         0         0          2
Finance    Finance Cost     10        20        30        40         1
Finance    Finance Tax      10        20        30        40         1
Finance    Subtotal         0         0         0         0          2
GrandTotal GrandTotal       0         0         0         0          3
----------------------------------------------------------------------------

我想用相应类别的行级小计更新行，用行级总计更新行

Category   Subcategory      Option1   Option2   Option3   Option4    Type
----------------------------------------------------------------------------
Insurance  Insurance Cost   10        20        30        40         1
Insurance  Insurance Tax    10        20        30        40         1
Insurance  Subtotal         20        40        60        80         2
Finance    Finance Cost     10        20        30        40         1
Finance    Finance Tax      10        20        30        40         1
Finance    Subtotal         20        40        60        80         2
GrandTotal GrandTotal       40        80        120       160        3
----------------------------------------------------------------------------

如何计算和更新这些行？

等待您的回复。

谢谢。

标签： sqlsql-server

Answer 1

您正在尝试进行更新，所以这有点棘手。我认为这可以解决问题：

update t
    set option1 = (case t.subcategory 
                        when 'Subtotal' then subtotal.option1
                        when 'GrandTotal' then grandtotal.option1
                        else option1
                   end),
        option2 = (case t.subcategory 
                        when 'Subtotal' then subtotal.option2
                        when 'GrandTotal' then grandtotal.option2
                        else option2
                   end),
        option3 = (case t.subcategory 
                        when 'Subtotal' then subtotal.option3
                        when 'GrandTotal' then grandtotal.option3
                        else option3
                   end),
        option4 = (case t.subcategory 
                        when 'Subtotal' then subtotal.option4
                        when 'GrandTotal' then grandtotal.option4
                        else option4
                   end)
    from t cross apply
         (select category, sum(option1) as option1, sum(option2) as option2,
                 sum(option3) as option3)
          from t t2
          where t2.category = t.category
         ) subtotal cross join
         (select category, sum(option1) as option1, sum(option2) as option2,
                 sum(option3) as option3)
          from t t2
         ) grandtotal
    where t.subcategory in ('Subtotal', 'GrandTotal');