sql - 使用 group by 更新同一个表中的两列？

Question

我在一个表中有两个空列。我想根据条件填充这些列。第一列应使用击球手的总 HR 进行更新。第二列应更新为最高击球率。我试过了：

UPDATE Spring_2021_BaseBall.dbo.People 
SET [Total_HR] = (SELECT SUM(HR)
                  FROM Spring_2021_BaseBall.dbo.Batting
                  GROUP BY playerid),
     [High_BA] = (SELECT playerID, 
                  FROM Spring_2021_BaseBall.dbo.Batting
                  GROUP BY playerid) B                       
WHERE A.playerid = B.playerid

有什么想法吗？

标签： sqlgroup-bysql-update

Answer 1

使用聚合将它们组合在一起：

UPDATE p
    SET Total_HR = b.Total_HR,
        High_BA =  b.High_BA
    FROM Spring_2021_BaseBall.dbo.People p JOIN
         (SELECT SUM(HR) as SUM_hr, MAX(BA) as High_BA
          FROM Spring_2021_BaseBall.dbo.Batting
          GROUP BY playerid 
         ) b
         ON b.playerid = p.playerid ;