如何让SQL查询代码更新原Test_Table表数据？

你的代码是SELECT查询语句，它仅用于从原表读取数据并返回计算后的结果集，不会对Test_Table原表产生任何修改。要更新原表的数据，你需要使用UPDATE语句，或根据需求选择替换原表、新增列后填充等方式。

先优化你的查询代码（清理冗余逻辑） #

你的CASE语句存在逻辑冗余：row_num由ROW_NUMBER()生成，值从1开始，因此WHEN row_num =1和WHEN row_num >=1的条件完全重复，可以简化为：

WITH RankedData AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY DMY DESC) AS row_num
    FROM TEST_TABLE
)
SELECT
    game_id,
    DMY AS date,
    player_id,
    AVG(stat) OVER (PARTITION BY player_id ORDER BY row_num ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING) AS stat
FROM RankedData
ORDER BY player_id, row_num

方案1：更新原表已存在的stat列 #

如果要把原表的stat列替换为计算后的平均值，可结合CTE使用UPDATE实现（以下以SQL Server为例，不同数据库语法略有差异）：

WITH RankedData AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY DMY DESC) AS row_num,
        -- 提前计算出要更新的stat值
        AVG(stat) OVER (PARTITION BY player_id ORDER BY row_num ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING) AS new_stat
    FROM TEST_TABLE
)
UPDATE TEST_TABLE
SET stat = rd.new_stat
FROM TEST_TABLE t
-- 通过唯一标识关联原表和CTE的行，避免错误更新
JOIN RankedData rd ON t.player_id = rd.player_id AND t.game_id = rd.game_id AND t.DMY = rd.DMY;

方案2：新增列并填充计算结果 #

如果原表没有存储该计算值的列，先新增列再执行更新：

-- 新增列（根据你的数据类型调整字段类型，比如DECIMAL(10,2)）
ALTER TABLE TEST_TABLE ADD calculated_stat DECIMAL(10,2);

WITH RankedData AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY DMY DESC) AS row_num,
        AVG(stat) OVER (PARTITION BY player_id ORDER BY row_num ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING) AS new_stat
    FROM TEST_TABLE
)
UPDATE TEST_TABLE
SET calculated_stat = rd.new_stat
FROM TEST_TABLE t
JOIN RankedData rd ON t.player_id = rd.player_id AND t.game_id = rd.game_id AND t.DMY = rd.DMY;

方案3：用查询结果替换原表（谨慎操作） #

如果希望直接用查询结果覆盖原表，建议先备份原表，再执行替换：

-- 先备份原表（可选但强烈建议）
SELECT * INTO TEST_TABLE_BACKUP FROM TEST_TABLE;

-- 用查询结果创建新表
CREATE TABLE TEST_TABLE_NEW AS
WITH RankedData AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY DMY DESC) AS row_num
    FROM TEST_TABLE
)
SELECT
    game_id,
    DMY AS date,
    player_id,
    AVG(stat) OVER (PARTITION BY player_id ORDER BY row_num ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING) AS stat
FROM RankedData
ORDER BY player_id, row_num;

-- 替换原表（此操作会删除原表，务必确认数据备份完成）
DROP TABLE TEST_TABLE;
ALTER TABLE TEST_TABLE_NEW RENAME TO TEST_TABLE;

内容的提问来源于stack exchange，提问作者Sean Shapiro