SQL Server中如何仅展示数据集中最高的PTACHI_PK

嘿，针对你这个需求，用SQL Server的**窗口函数ROW_NUMBER()**就能轻松搞定，核心思路就是给每组相同的业务数据按PTACHI_PK降序排序，然后只留排第一的那条（也就是PK值最大的记录）。

修改后的完整SQL #

WITH RankedRecords AS (
    SELECT 
        PTACHI_PK,
        ACT.TAX_PAYER_ID AS 'AP_OWENER_ID',
        IDM.ID_PK AS 'NCPTS_OWNER_ID',
        '' as REL_PK,
        ADDR.ADDRESS_PK,
        FIRST_NAME,
        MIDDLE_NAME,
        LAST_NAME,
        BUSINESS_NAME,
        ADDR1,
        ADDR2,
        ADDR3,
        IN_CARE_OF,
        CITY,
        RIGHT(STATE,2) as 'STATE',
        POSTAL_CODE AS 'ZIP',
        POSTAL_CODE_EXT AS 'ZIP4',
        HIS.FIELD_CHANGED,
        HIS.FIELD_OLD_VALUE,
        HIS.FIELD_NEW_VALUE,
        CONVERT(VARCHAR, MODIFIED_TS, 22) AS 'DATE_CHANGED',
        -- 按业务唯一标识分组，PTACHI_PK降序标记行号
        ROW_NUMBER() OVER (
            PARTITION BY ACT.TAX_PAYER_ID, IDM.ID_PK, ADDR.ADDRESS_PK
            ORDER BY PTACHI_PK DESC
        ) AS RecordRank
    FROM OWNIDM_ID_MASTER IDM
    JOIN OWNREL_OWNER_RELATIONSHIP REL ON IDM.ID_PK = REL.IDM_ID_PK
    JOIN OWNACT_ACCOUNT ACT ON REL.ACT_ACCOUNT_PK = ACT.ACCOUNT_PK
    JOIN COMADD_COMMON_ADDRESS ADDR ON REL.ADD_ADDRESS_PK = ADDR.ADDRESS_PK
    JOIN COMNAM_COMMON_NAME NAME ON REL.NAM_NAME_PK = NAME.NAME_PK
    JOIN PTACHI_CHANGE_HISTORY HIS ON IDM.ID_PK = HIS.PARENT_PK
    WHERE 
        ACT.TAX_PAYER_ID IS NOT NULL 
        AND ACT.TAX_PAYER_ID <> '' 
        AND ISDEFAULT_ADDRESS = 'Y' 
        AND ADDR.SOURCE_TYPE <> 'ADSRDMV' 
        AND HIS.PARENT_TYPE = 'COMADD_COMMON_ADDRESS' 
        AND HIS.FIELD_CHANGED = 'OWNER_ADDRESS_UPDATED' 
        AND MODIFIED_TS >= DATEADD(day, DATEDIFF(day,0,GETDATE())-1,0) 
        AND MODIFIED_TS < DATEADD(day, DATEDIFF(day,0,GETDATE()),0) 
        AND ID_PK = 432082
)
SELECT 
    PTACHI_PK,
    AP_OWENER_ID,
    NCPTS_OWNER_ID,
    REL_PK,
    ADDRESS_PK,
    FIRST_NAME,
    MIDDLE_NAME,
    LAST_NAME,
    BUSINESS_NAME,
    ADDR1,
    ADDR2,
    ADDR3,
    IN_CARE_OF,
    CITY,
    STATE,
    ZIP,
    ZIP4,
    FIELD_CHANGED,
    FIELD_OLD_VALUE,
    FIELD_NEW_VALUE,
    DATE_CHANGED
FROM RankedRecords
WHERE RecordRank = 1
ORDER BY PTACHI_PK DESC;

几个关键要点 #

• 分组依据调整：我用ACT.TAX_PAYER_ID, IDM.ID_PK, ADDR.ADDRESS_PK作为分组的唯一标识，你可以根据实际业务情况修改——只要这些字段组合起来能确定一组"相同的业务数据"就行。
• 去掉原GROUP BY：原来的GROUP BY里包含了PTACHI_PK，这会导致每条不同PK的记录都被单独分组，所以用CTE+窗口函数替代后，就不需要保留原GROUP BY了。
• ROW_NUMBER()的作用：它会给每组内的记录按PTACHI_PK从大到小分配行号，RecordRank=1就对应每组里PK最大的那条记录，筛选这个条件就能得到你想要的结果。

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