Oracle SQL使用Pivot报错Ora-00904无效标识符问题求助

问题原因 #

• PIVOT 算子执行完成后，会自动消去两类字段：你用来做聚合计算的CO2E、用来做转置依据的分类字段SUBPART，这两个字段不会存在于PIVOT返回的结果集中，所以你在SELECT子句中写SUBPART和CO2E会触发无效标识符报错。
• 另外如果原表除了你提到的4列外还有其他字段，直接对全表做PIVOT会把其他字段也当作分组维度，导致最终结果行数不符合预期，建议先通过子查询筛选出你需要的4个字段再做转置。

修正后代码 #

Create Table CandK_emissions as
Select
  FACILITY_ID,
  REPORTING_YEAR,
  nvl(C, 0) as C_CO2E, -- nvl函数为可选配置，作用是没有对应SUBPART数据时返回0而非空值
  nvl(K, 0) as K_CO2E
From (
  -- 子查询只筛选需要的字段和分类，减少冗余数据处理，避免其他字段干扰分组逻辑
  Select FACILITY_ID, REPORTING_YEAR, SUBPART, CO2E
  From facilityReport
  Where SUBPART in ('C', 'K')
)
Pivot
(
  Sum(CO2E)
  For SUBPART in (
    'C' as C,
    'K' as K
  )
);

执行逻辑说明 #

• 内层子查询先筛选出仅需要的字段和SUBPART为C、K的记录，减少后续处理的数据量
• PIVOT算子自动按除了聚合字段、转置分类字段之外的字段（也就是FACILITY_ID、REPORTING_YEAR）分组，对C和K的CO2E分别求和生成对应的列
• 外层SELECT直接取分组字段和转置生成的C、K列即可，不需要再引用原来的SUBPART和CO2E字段

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