Oracle SQL查询最大版本数据时遭遇ORA-00934错误求助

原SQL可返回component_id=1222的所有QC Approved版本组件详情：

select a.version,b.section_id,a.component_id,
       a.comp_text, 
       case 
         when a.version=b.comp_version and a.component_id=b.component_id 
          then 'Yes' 
          else 'No' 
      END CurrentVersion 
     from
     (
      select version, component_id,comp_text 
      from cms_components 
      where component_id =1222 and status = 'QC Approved'
      union all
      select version, component_id,comp_text 
      from CMS_COMPONENT_HISTORY where component_id =1222 and status = 'QC Approved'
     ) a,
     DOC_SEC_COMPONENT_DETAILS b 
     where a.component_id = b.component_id 
     and b.section_id =9255 and b.DOC_ID =1747 
    order by a.version desc;

查询输出：

6.0 9255    1222    <p>main</p><p>Test1</p><p>Test level3</p><p>Test level4</p><p>Test level5</p><p>Test level6</p> No
5.0 9255    1222    <p>main</p><p>Test1</p><p>Test level3</p><p>Test level4</p><p>Test level5</p>   No
4.0 9255    1222    <p>main</p><p>Test1</p><p>Test level3</p><p>Test level4</p> No
3.0 9255    1222    <p>main</p><p>Test1</p><p>Test level3</p>   No
2.0 9255    1222    <p>main</p><p>Test1</p> No
1.0 9255    1222    <p>main</p> Yes

尝试修改SQL获取最大版本时触发错误：

ORA-00934: group function is not allowed here
00934. 00000 - "group function is not allowed here"
*Cause:
*Action: Error at Line: 6 Column: 17

错误原因 #

WHERE子句中不能直接使用聚合函数（如MAX()），聚合函数需在子查询或分组逻辑中计算完成后，再作为筛选条件使用。

解决方案 #

方案1：子查询获取最大版本后关联

先通过子查询计算目标组件的最大版本，再将其作为筛选条件：

select a.version,b.section_id,a.component_id,a.comp_text, 
       case 
         when a.version=b.comp_version and a.component_id=b.component_id 
          then 'Yes' 
          else 'No' 
      END CurrentVersion 
from(
   select version, component_id,comp_text 
   from cms_components 
   where component_id =1222 and status = 'QC Approved'
 union all 
   select version, component_id, comp_text 
   from CMS_COMPONENT_HISTORY 
   where component_id =1222 and status = 'QC Approved'
) a
join DOC_SEC_COMPONENT_DETAILS b 
  on a.component_id = b.component_id 
where b.section_id = 9255 
  and b.DOC_ID =1747 
  and a.version = (
      select max(version)
      from (
          select version from cms_components where component_id=1222 and status='QC Approved'
          union all
          select version from CMS_COMPONENT_HISTORY where component_id=1222 and status='QC Approved'
      ) t
  );

方案2：使用窗口函数ROW_NUMBER()排序取首行

通过窗口函数按版本降序排序并标记行号，筛选行号为1的记录：

select version, section_id, component_id, comp_text, CurrentVersion
from(
    select a.version,b.section_id,a.component_id,a.comp_text, 
           case 
             when a.version=b.comp_version and a.component_id=b.component_id 
              then 'Yes' 
              else 'No' 
          END CurrentVersion,
          row_number() over(order by a.version desc) rn
    from(
       select version, component_id,comp_text 
       from cms_components 
       where component_id =1222 and status = 'QC Approved'
     union all 
       select version, component_id, comp_text 
       from CMS_COMPONENT_HISTORY 
       where component_id =1222 and status = 'QC Approved'
    ) a
    join DOC_SEC_COMPONENT_DETAILS b 
      on a.component_id = b.component_id 
    where b.section_id = 9255 
      and b.DOC_ID =1747 
) t
where rn=1;

方案3：使用FETCH FIRST（Oracle 12c及以上版本支持）

利用Oracle原生语法直接取排序后的第一行：

select a.version,b.section_id,a.component_id,a.comp_text, 
       case 
         when a.version=b.comp_version and a.component_id=b.component_id 
          then 'Yes' 
          else 'No' 
      END CurrentVersion 
from(
   select version, component_id,comp_text 
   from cms_components 
   where component_id =1222 and status = 'QC Approved'
 union all 
   select version, component_id, comp_text 
   from CMS_COMPONENT_HISTORY 
   where component_id =1222 and status = 'QC Approved'
) a
join DOC_SEC_COMPONENT_DETAILS b 
  on a.component_id = b.component_id 
where b.section_id = 9255 
  and b.DOC_ID =1747 
order by a.version desc
fetch first 1 row only;

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