使用JOIN - SQL Server在同一个表中使用平均值更新列

I have following table

我有下表

[Table_01]
ID  |   Name    |   Profit  |   AvgProfit
------------------------------------------
1   |   Donald  |   1001    |   
2   |   Hillary |   101     |   
3   |   Barack  |   11      |   
4   |   Bernie  |   1       |   
1   |   Donald  |   1002    |   
2   |   Hillary |   102     |   
3   |   Barack  |   12      |   
4   |   Bernie  |   2       |   
1   |   Donald  |   1003    |   
2   |   Hillary |   103     |   
3   |   Barack  |   13      |   
4   |   Bernie  |   3       |   

I need to add average Profit value for each person.

我需要为每个人添加平均利润值。

Result after UPDATE should be:

UPDATE后的结果应为：

[Table_01]
ID  |   Name    |   Profit  |   AvgProfit
------------------------------------------
1   |   Donald  |   1001    |   1002
2   |   Hillary |   101     |   102
3   |   Barack  |   11      |   12
4   |   Bernie  |   1       |   1
1   |   Donald  |   1002    |   1002
2   |   Hillary |   102     |   102
3   |   Barack  |   12      |   12
4   |   Bernie  |   2       |   1
1   |   Donald  |   1003    |   1002
2   |   Hillary |   103     |   102
3   |   Barack  |   13      |   12
4   |   Bernie  |   3       |   1

I do get average values using:

我使用以下方法获得平均值：

SELECT z1.*, AVG(Profit) OVER(PARTITION BY Name) AVERAGE
    FROM Table_01 z1
    order by Name

For update I've tried multiple JOIN clauses with no success. I get various errors: using AGGREGATE in JOIN and invalid object names.

为了更新，我尝试了多个JOIN子句但没有成功。我得到了各种错误：在JOIN中使用AGGREGATE和无效的对象名称。

This one works in Oracle, i suppose.

我认为这个适用于Oracle。

UPDATE Table_01 z1 
SET z1.AvgSecs = (SELECT AVG(Profit) 
                 FROM Table_01
                 WHERE Name = z1.Name)

What is the syntax used in Microsoft SQL Server 2014?

Microsoft SQL Server 2014中使用的语法是什么？

QUESTION 2

I have another table which i need to update

我有另一个表，我需要更新

[Table_02]
Name    |   AvgProfit                   Name    |   AvgProfit
---------------------   UPDATE -->      ---------------------
Donald  |                               Donald  |   1002
Hillary |                               Hillary |   102
Barack  |                               Barack  |   12
Bernie  |                               Bernie  |   1

I wonder if this works:

我想知道这是否有效：

UPDATE t2
SET t2.AvgProfit = t1.AvgProfit
FROM Table_02 t2
    join Table_01 t1 
    ON   t1.Name = t2.Name 

"Name" is a Unique value

“名称”是唯一值

Great thanks for all.

非常感谢大家。

2 个解决方案

#1

You can use a CTE for the first update:

您可以使用CTE进行第一次更新：

;WITH CTE AS
(
    SELECT Id, AVG(Profit) OVER (PARTITION BY Name) As AvgProfit
    FROM Table_01
)

UPDATE T1
SET AvgProfit = CTE.AvgProfit
FROM Table_01 T1 
INNER JOIN CTE ON(T1.Id = CTE.Id)

As for your second question - The answer is yes.

至于你的第二个问题 - 答案是肯定的。

#2

Question 1 :

问题1：

 UPDATE Table_01 SET AvgProfit = _AvgProfit FROM
 (SELECT ID , AVG(Profit) _AvgProfit FROM Table_01 GROUP BY ID )
 A WHERE A.ID = Table_01.ID

Question 2 :

问题2 ：

UPDATE Table_02 SET Table_02.AvgProfit = t1.AvgProfit 
FROM Table_01 t1 WHERE t1.Name = Table_02.Name