Oracle两表关联Pivot查询报错及多状态字段处理咨询

1. 语句错误原因 #

你遇到的ORA-00904错误，根源是PIVOT子句的IN部分没有为列指定合法的别名。Oracle中，当你在PIVOT的IN里用字符串值（比如'Open'）时，默认生成的列名是带单引号的字符串，无法直接用.引用。正确的做法是给这个字符串值指定一个别名，或者直接使用大写的标识符（Oracle默认将未加双引号的标识符转为大写）。

修正后的基础语句示例：

SELECT
    IRD.INC_ID,
    IRD.resolution_time,
    p.OPEN AS open_duration
FROM
         INC_RES_DUR IRD
    JOIN (
        SELECT
            ISD.INC_ID,
            ISD.case_status,
            ISD.time_duration
        FROM
            INC_STATUS_DUR ISD
    ) PIVOT (
        SUM(time_duration)
        FOR case_status
        IN ( 'Open' AS OPEN ) -- 为'Open'指定别名OPEN
    )
    p ON IRD.INC_ID = p.INC_ID

2. 处理“In Progress”这类含空格的状态 #

对于包含空格、特殊字符的状态值，需要在PIVOT的IN子句中为其指定一个不含特殊字符的别名，方便后续引用；如果一定要保留原名称作为列名，就用双引号把别名括起来。

示例1：指定下划线替代空格的别名

SELECT
    IRD.INC_ID,
    IRD.resolution_time,
    p.OPEN AS open_duration,
    p.IN_PROGRESS AS in_progress_duration
FROM
         INC_RES_DUR IRD
    JOIN (
        SELECT
            ISD.INC_ID,
            ISD.case_status,
            ISD.time_duration
        FROM
            INC_STATUS_DUR ISD
    ) PIVOT (
        SUM(time_duration)
        FOR case_status
        IN ( 
            'Open' AS OPEN,
            'In Progress' AS IN_PROGRESS -- 给含空格的状态指定别名
        )
    )
    p ON IRD.INC_ID = p.INC_ID

示例2：保留原名称作为列名（需用双引号引用）

SELECT
    IRD.INC_ID,
    IRD.resolution_time,
    p."Open" AS open_duration,
    p."In Progress" AS in_progress_duration
FROM
         INC_RES_DUR IRD
    JOIN (
        SELECT
            ISD.INC_ID,
            ISD.case_status,
            ISD.time_duration
        FROM
            INC_STATUS_DUR ISD
    ) PIVOT (
        SUM(time_duration)
        FOR case_status
        IN ( 
            'Open' AS "Open",
            'In Progress' AS "In Progress" -- 用双引号保留原名称
        )
    )
    p ON IRD.INC_ID = p.INC_ID

3. 是否必须使用SUM聚合函数 #

Oracle的PIVOT语法必须搭配聚合函数，但不一定非要用SUM。因为你提到INC_STATUS_DUR中各状态时长已确定（即每个INC_ID+case_status的组合只有一条记录），此时用MAX(time_duration)或MIN(time_duration)会比SUM更合理——毕竟单条记录的求和结果和取最大/最小值一致，但语义上更贴合“获取唯一时长”的需求。

替换后的示例：

SELECT
    IRD.INC_ID,
    IRD.resolution_time,
    p.OPEN AS open_duration
FROM
         INC_RES_DUR IRD
    JOIN (
        SELECT
            ISD.INC_ID,
            ISD.case_status,
            ISD.time_duration
        FROM
            INC_STATUS_DUR ISD
    ) PIVOT (
        MAX(time_duration) -- 用MAX替代SUM
        FOR case_status
        IN ( 'Open' AS OPEN )
    )
    p ON IRD.INC_ID = p.INC_ID

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