sql-server - 使用 SQL Server 中的一张表上的聚合函数更新查询

Question

我知道这个问题可能已经得到解答，但我遇到的许多示例都涉及使用 INNER JOIN 将数据从一个表传输到另一个表，而我的情况有点不同。

基本上我想做的就是将此 SELECT 语句转换为 Update 语句。

SELECT 
    SR, 100. * Cast(Round(AVG(Three_1 + Three_2 + Three_3 + Three_4 + Three_5 + Three_6 + Three_7 + Five_1 + Six_1 + Six_3) / 38,0) as int)
FROM 
    MainDB 
WHERE 
    Ctype = 'Call'
GROUP BY 
    SR

我能够成功计算一些列的百分比，然后我想用该信息更新列。我的第一次尝试是这样的

UPDATE QAMainDB 
SET TCX_score = 100. * avg(three_1 + Three_2 + Three_3 + Three_4 + Three_5 + Three_6 + Three_7 + Five_1 + Six_1 + Six_3) / 38

我收到错误:

An aggregate may not appear in the set list of an UPDATE statement.

在做了更多的尝试和错误之后，我尝试了这种方法来声明堆栈上前面示例中的变量。

Declare @Result Decimal = 0

SET @Result = 100. * Cast(round(avg(three_1 + Three_2 + Three_3 +  Three_4 + Three_5 + Three_6 + Three_7 + Five_1 + Six_1 + Six_3) / 38,0) as int)

UPDATE QAMainDB
SET TCX_score = @Result
WHERE Ctype = 'Call'

但是失败了，我想知道你们中是否有人可以帮助我，引导我走向正确的方向。我对 SQL 还很陌生，所以请耐心等待。

感谢您的宝贵时间。

Answer 1

您如何匹配列？我猜测 SR 是用于表之间匹配的列:

WITH m as (
      SELECT SR, 100. * Cast(Round(AVG(Three_1 + Three_2 + Three_3 + Three_4 + Three_5 + Three_6 + Three_7 + Five_1 + Six_1 + Six_3) / 38,0) as int) as val
      FROM MainDB 
      WHERE Ctype = 'Call'
      GROUP BY SR
     )
Update QAMainDB
    SET TCX_score = val
    FROM QAMainDB JOIN
         m
         ON m.SR = QAMainDB.SR;