sql - 如何使用聚合函数更新此列？

Question

我计算了每对 GameId 和 TeamId 唯一的 OREB 总和

使用这个查询，

查询图像。

select DGAM.GameID, HomeTeamID, SUM(OREB) as OREB_Home    
from dimGames as DGAM
join dimPerformance as DPERF on DGAM.GameID = DPERF.GAME_ID and DGAM.HomeTeamID = DPERF.TEAM_ID
group by DGAM.GameID, HomeTeamID

使用此查询的结果，我想更新此表中的空列

表名为 dimGames。

如何编写更新语句来完成此操作？

标签： sqlsql-servertsqlsql-update

Answer 1

调整这个答案，我想你想要：

with agg as (
    select DGAM.GameID, HomeTeamID, SUM(OREB) as OREB_Home
    from dimGames as DGAM
    join dimPerformance as DPERF on DGAM.GameID = DPERF.GAME_ID and DGAM.HomeTeamID = DPERF.TEAM_ID
    group by DGAM.GameID, HomeTeamID
)
UPDATE dimGames dg
    SET dg.OREB_home = agg.OREB_Home
FROM agg
WHERE dg.GameId = agg.GameId and dg.HomeTeamId = agg.HomeTeamId