SQL如何更新同一表中组上方列的总和 [英] SQL How to Update SUM of column over group in same table

问题描述

我有一个类似于以下内容的(SQL Server)表:

SalesSummary

年份|团队|人人员销售|团队销售/年

2013              吉姆                          $ 10              

2013    1      安娜         $ 0                            ??

2013    2      约翰           $ 8                            ??

2013    3     托德                        $ 4                            ??

2013    3     艾伦            $ 1                            ??

2014    3     艾伦            $ 22                ??

我试图对该示例SalesSummary表进行汇总，然后将正确的值插入团队销售"列中.在此示例中，我希望第一列和第二列中的金额为10美元，第三列中的金额为8美元，第四列/5位的金额为5美元，第六列的插槽为22美元.原谅我对SQL的无知，但是我决定告诉我一个糟糕的解决方案，如下所示:

UPDATE SalesSummary SET TeamSales = sum.TeamSales
FROM (SELECT Team, Year, SUM(PersonSales) OVER (Partition By Team, Year) as TeamSales)
      FROM SalesSummary
      GROUP BY Team, Year, PersonSales
     ) AS sum, SalesSummary as SS
WHERE sum.Team = ss.Team AND sum.Year = ss.Year

我希望有人能够证明如何最好地执行这种类型的更新.我感谢任何帮助，技巧或示例.抱歉，这很明显.

解决方案

假设您正在使用SQL Server，我想您需要这样的东西:

WITH toupdate AS
     (SELECT team, year, 
             Sum(personsales) OVER (partition BY team, year) AS newTeamSales 
      FROM salessummary
     ) 
UPDATE toupdate 
   SET teamsales = newteamsales; 

您的原始查询存在多个问题和可疑的结构.首先，聚合子查询不可更新.其次，您正在进行聚合，并且使用窗口函数(尽管允许)是不寻常的.第三，您正在按PersonSales进行汇总并采用sum().再次允许，但不寻常.

I have a (SQL Server) table similar to the following:

SalesSummary

Year | Team | Person | Person Sales | Team Sales/Yr

2013      1          Jim             $10                  ??

2013      1         Anna            $0                   ??

2013      2         John             $8                   ??

2013      3        Todd              $4                   ??

2013      3        Alan               $1                   ??

2014      3        Alan              $22                  ??

I'm trying to sum over this example SalesSummary table and insert the proper values into the Team Sales column. In this example, I would want $10 in the 1st and 2nd columns, $8 in the 3rd, $5 in the 4th/5th and $22 in the 6th column slot. Forgive my ignorance of SQL, but I settled on what I'm told is a poor solution as follows:

UPDATE SalesSummary SET TeamSales = sum.TeamSales
FROM (SELECT Team, Year, SUM(PersonSales) OVER (Partition By Team, Year) as TeamSales)
      FROM SalesSummary
      GROUP BY Team, Year, PersonSales
     ) AS sum, SalesSummary as SS
WHERE sum.Team = ss.Team AND sum.Year = ss.Year

I was hoping someone might be able to show be how best to perform this type of update. I appreciate any help, tips, or examples. Apologies if this is obvious.

解决方案

Assuming you are using SQL Server, I think you want something like this:

WITH toupdate AS
     (SELECT team, year, 
             Sum(personsales) OVER (partition BY team, year) AS newTeamSales 
      FROM salessummary
     ) 
UPDATE toupdate 
   SET teamsales = newteamsales; 

Your original query has several issues and suspicious constructs. First, an aggregation subquery is not updatable. Second, you are doing an aggregation and using a window function with, although allowed, is unusual. Third, you are aggregating by PersonSales and taking the sum(). Once again, allowed, but unusual.

这篇关于SQL如何更新同一表中组上方列的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！