SQL技术问题：如何获取每个AccountId对应的最大生效日期？

问题原因 #

你的查询里GROUP BY同时包含了AccountId和RTRIM(codeStatus.Description)，这意味着只要同一个AccountId下存在不同的Status描述，哪怕它们的最大生效日期相同，也会被分成不同的组，最终返回多行结果。这就是为什么你会得到两条记录，而非预期的一条。

解决方案 #

方法一：使用窗口函数（推荐）

通过窗口函数可以精准定位每个AccountId下生效日期最新的记录，代码如下：

WITH RankedPolicies AS (
    SELECT 
        plc.effectivedate,
        plc.AccountId,
        RTRIM(codeStatus.Description) as Status,
        -- 按AccountId分组，生效日期倒序排名，最新记录排第1
        ROW_NUMBER() OVER (PARTITION BY plc.AccountId ORDER BY plc.effectivedate DESC) as rn
    FROM PlcPolicy plc
    JOIN dbo.CodePlcStatus codeStatus WITH (NOLOCK) ON codeStatus.StatusId = plc.StatusId  
    WHERE plc.AccountName LIKE 'Hertz%'
)
SELECT effectivedate as MaxEffectiveDate, AccountId, Status
FROM RankedPolicies
WHERE rn = 1; -- 只保留每个AccountId下的最新记录

如果同一个AccountId下有多个记录共享最大生效日期，且你需要返回所有这类记录，可将ROW_NUMBER()替换为RANK()。

方法二：先取最大日期再关联

先通过子查询获取每个AccountId的最大生效日期，再关联回原表和状态表拿到对应状态：

SELECT 
    plc.effectivedate as MaxEffectiveDate,
    plc.AccountId,
    RTRIM(codeStatus.Description) as Status
FROM PlcPolicy plc
JOIN dbo.CodePlcStatus codeStatus WITH (NOLOCK) ON codeStatus.StatusId = plc.StatusId
-- 关联子查询得到的每个AccountId的最大生效日期
JOIN (
    SELECT AccountId, MAX(effectivedate) as MaxDate
    FROM PlcPolicy
    WHERE AccountName LIKE 'Hertz%'
    GROUP BY AccountId
) maxDates ON plc.AccountId = maxDates.AccountId AND plc.effectivedate = maxDates.MaxDate
WHERE plc.AccountName LIKE 'Hertz%';

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