SQL技术问询：如何获取每个ParcelKey分组的最新年度数据行？

原数据表 #

CaseKey    CaseStatus    ParcelKey    Year       Status
-------    ----------    ---------    -------    ------
8866       Open          11901        2024/25    STOP
8866       Open          11901        2023/24    FILE
8866       Open          11901        2022/23    FILE
8866       Open          11901        2021/22    FILE
8866       Open          11901        2020/21    FILE
8866       Open          11901        2019/20    SETT
8866       Open          11901        2018/19    SETT
8866       Open          11901        2017/18    SETT
8866       Open          11902        2024/25    STOP
8866       Open          11902        2023/24    FILE
8866       Open          11902        2022/23    FILE
8866       Open          11902        2021/22    FILE
8866       Open          11902        2020/21    FILE
8866       Open          11902        2019/20    FILE
8866       Open          11902        2018/19    FILE
8866       Open          11902        2017/18    FILE
8866       Open          16451        2024/25    FILE
8866       Open          16451        2023/24    FILE
8866       Open          16452        2024/25    FILE
8866       Open          16452        2023/24    FILE 

期望查询结果 #

CaseKey    CaseStatus    ParcelKey    Year       Status
-------    ----------    ---------    -------    ------
8866       Open          11901        2024/25    STOP
8866       Open          11902        2024/25    STOP
8866       Open          16451        2024/25    FILE
8866       Open          16452        2024/25    FILE

尝试的脚本及错误结果 #

尝试的脚本：

create table #TopRecord
(
    CaseKey int,
    CaseStatus Varchar(30),
    ParcelKey int,
    TaxYear Varchar(7),
    YearStatus Varchar(30)
)
INSERT INTO #TopRecord (CaseKey, CaseStatus, ParcelKey, YearStatus, TaxYear)
SELECT CaseKey, CaseStatus, ParcelKey, YearStatus, TaxYear=MAX(TaxYear)
FROM #tmp
GROUP BY CaseKey, CaseStatus, ParcelKey, YearStatus
ORDER BY CaseKey, CaseStatus, ParcelKey

select * from #TopRecord order by ParcelKey, TaxYear DESC

得到的错误结果：

CaseKey    CaseStatus    ParcelKey    Year       Status
-------    ----------    ---------    -------    ------
8866       Open          11901        2024/25    STOP
8866       Open          11901        2023/24    FILE
8866       Open          11901        2019/20    SETT
8866       Open          11902        2024/25    STOP
8866       Open          11902        2023/24    FILE
8866       Open          16451        2024/25    FILE
8866       Open          16452        2024/25    FILE

问题原因 #

你的GROUP BY子句中包含了YearStatus字段，分组逻辑变成了CaseKey + CaseStatus + ParcelKey + YearStatus。同一个ParcelKey下如果有不同的YearStatus值，会被拆分成不同的组，每组各自取最大的TaxYear，所以ParcelKey=11901有STOP、FILE、SETT三种YearStatus，生成3条记录；ParcelKey=11902有STOP、FILE两种YearStatus，生成2条记录，这和「每个ParcelKey只取最新年度行」的需求不符。

解决方案 #

使用窗口函数ROW_NUMBER()是获取每组最新记录的标准方法，脚本如下：

SELECT CaseKey, CaseStatus, ParcelKey, TaxYear, YearStatus
FROM (
    SELECT 
        CaseKey, 
        CaseStatus, 
        ParcelKey, 
        TaxYear, 
        YearStatus,
        -- 按ParcelKey分组，每组内按TaxYear降序编号
        ROW_NUMBER() OVER (PARTITION BY ParcelKey ORDER BY TaxYear DESC) AS rn
    FROM #tmp
) t
WHERE rn = 1 -- 只取每组编号为1的最新记录
ORDER BY ParcelKey

脚本说明 #

• PARTITION BY ParcelKey：将数据按ParcelKey拆分成独立的组
• ORDER BY TaxYear DESC：让每组内最新的年份排在最前面
• ROW_NUMBER()：为每组内的行从1开始编号，最新年份的行编号为1
• 外层查询筛选rn=1，即可得到每个ParcelKey对应的最新年度数据行

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