基于条件替换SQL表行值：将System替换为最近关联Agent

我是SQL新手，需要编写SQL查询实现以下需求：将表中Agent Name为“System”的记录，替换为同一ACCT ID下最近时间戳的非System Agent Name（关联Approved Date的记录）。

当前表数据 #

我的初步逻辑 #

SELECT ACCT_ID, Agent_Name, Date, Approved_Date 
FROM Internal_Table

-- 逻辑思路：同一ACCT ID下，若Approved_Date不为空，Agent_Name为"System"的记录需要替换为该账户下最近的非System名称

期望查询结果 #

解决方案 #

方法一：使用CTE+窗口函数筛选最近代理 #

WITH RecentAgent AS (
    SELECT 
        ACCT_ID,
        Agent_Name,
        ROW_NUMBER() OVER (PARTITION BY ACCT_ID ORDER BY Date DESC) AS rn
    FROM Internal_Table
    WHERE Agent_Name != 'System'
)
SELECT 
    it.ACCT_ID,
    CASE 
        WHEN it.Agent_Name = 'System' THEN ra.Agent_Name
        ELSE it.Agent_Name
    END AS Agent_Name,
    it.Date,
    it.Approved_Date
FROM Internal_Table it
LEFT JOIN RecentAgent ra 
    ON it.ACCT_ID = ra.ACCT_ID AND ra.rn = 1;

逻辑说明：

1. 先通过CTE筛选所有非System的记录，按ACCT ID分组后用ROW_NUMBER()按日期倒序标记序号，序号为1的就是该账户下最近的代理名称。
2. 主查询关联原表和CTE，遇到Agent Name为System的记录时，替换为对应账户下序号1的代理名称，其余记录保留原名称。

方法二：使用FIRST_VALUE窗口函数简化写法 #

如果你的数据库支持FIRST_VALUE()，可以用更简洁的语句实现：

SELECT 
    ACCT_ID,
    CASE 
        WHEN Agent_Name = 'System' THEN 
            FIRST_VALUE(Agent_Name) OVER (
                PARTITION BY ACCT_ID 
                ORDER BY CASE WHEN Agent_Name != 'System' THEN Date ELSE '1900-01-01' END DESC
                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
            )
        ELSE Agent_Name
    END AS Agent_Name,
    Date,
    Approved_Date
FROM Internal_Table;

逻辑说明：
在每个ACCT ID分组内，优先按非System记录的日期倒序，用FIRST_VALUE()直接取第一个代理名称，替换System的记录。

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