Oracle中使用PIVOT无法获取PROPERTY_CONTENT最新值的问题

原查询的核心问题是直接使用MAX(PROPERTY_CONTENT)做转列，这仅会取PROPERTY_CONTENT字符串的最大值，而非基于主键AUTO_ID获取最新插入的记录。由于AUTO_ID是表的主键，最大的AUTO_ID对应最新的记录，因此需要先筛选出每个属性分组下的最新行，再执行PIVOT操作。

修改后的查询语句 #

SELECT *
FROM (
    SELECT 
        S.FILE_CODE,
        SP.SUBSCRIBER_ID,
        NVL(TO_CHAR(NVL(RC.FIRST_NAME, '') || NVL(RC.LAST_NAME, '')), LC.LEGAL_NAME) AS CUSTOMER_NAME,
        SP.PROPERTY_ID,
        SP.PROPERTY_CONTENT,
        -- 按用户ID、属性ID、文件编码分组，取AUTO_ID最大的最新记录
        ROW_NUMBER() OVER (PARTITION BY SP.SUBSCRIBER_ID, SP.PROPERTY_ID, S.FILE_CODE ORDER BY SP.AUTO_ID DESC) AS rn
    FROM CUSTOMERS_TCI.A_SUBSCRIBER_PROPERTIES SP
        INNER JOIN CUSTOMERS_TCI.SUBSCRIBERS S ON S.SUBSCRIBER_ID = SP.SUBSCRIBER_ID
        INNER JOIN CUSTOMERS_TCI.CUSTOMERS_INFO CI ON CI.CUSTOMER_ID = S.CUSTOMER_ID
        LEFT JOIN CUSTOMERS_TCI.REAL_CUSTOMER RC ON RC.CUSTOMER_ID = CI.CUSTOMER_ID
        LEFT JOIN CUSTOMERS_TCI.LEGAL_CUSTOMER LC ON LC.CUSTOMER_ID = CI.CUSTOMER_ID
    WHERE SP.PROPERTY_ID IN (277, 289, 290, 291) 
      AND SP.PROVINCE_ID = 22
    -- 筛选每组的最新记录（Oracle 12c+支持QUALIFY，低版本可移到外层WHERE）
    QUALIFY rn = 1
)
PIVOT (
    MAX(PROPERTY_CONTENT)
    FOR PROPERTY_ID IN (
        277 AS GRAPH,
        289 AS GRAPH_REGISTRATION_DATE,
        290 AS GRAPH_YEAR,
        291 AS GRAPH_MONTH
    )
)
WHERE FILE_CODE = '741' -- 可替换为目标FILE_CODE
ORDER BY CUSTOMER_NAME;

关键逻辑说明 #

1. 窗口函数筛选最新记录：
   ROW_NUMBER() OVER (PARTITION BY SP.SUBSCRIBER_ID, SP.PROPERTY_ID, S.FILE_CODE ORDER BY SP.AUTO_ID DESC) 按用户ID、属性ID、文件编码分组，每组内按AUTO_ID降序排序，给最新的记录标记序号1。
2. 过滤最新行：
   使用QUALIFY rn = 1直接筛选出每组的最新记录（Oracle 12c以下版本可将该条件移到外层查询的WHERE子句中）。
3. PIVOT转列：
   此时转列的数据源已经是每个属性的最新记录，PIVOT后得到的结果符合预期。

针对提供的测试数据，当FILE_CODE='741'时，会返回AUTO_ID为8、7、6、5对应的记录，即GRAPH='20'、GRAPH_REGISTRATION_DATE='2/14/2024'、GRAPH_YEAR='2024'、GRAPH_MONTH='2'。

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