Oracle GROUP BY报错Expression not in GROUP BY key 'isin'修复咨询

Oracle的GROUP BY语法规则要求：SELECT子句中所有未使用聚合函数的字段，必须全部包含在GROUP BY子句中。你当前SQL仅将stg.stocks_full_id设置为分组键，但SELECT列表中存在a.isin、a.state_number等大量非聚合字段未纳入分组逻辑，因此触发报错。

你的子查询a已经通过rank()窗口函数按isin、state_number分区取了最新的一行数据（过滤条件a.rn=1），且关联的trading_stocks、stocks_kinds表均与a.id一一对应，因此同一个stg.stocks_full_id对应的所有非聚合字段值都是唯一的，两种修正方式均可实现需求：

• 方案1：将SELECT列表中所有非聚合字段全部添加到GROUP BY子句中
• 方案2：用聚合函数包裹所有非聚合字段（同组值完全一致，聚合结果不会出现偏差，写法更简洁）

SELECT MAX(a.isin) as id,
       MAX(a.state_number) as number,
       MAX(a.update_time) as valid_from_date,
       '2999-12-31 00:00:00' as valid_to_date,
       MAX(a.operdate) as oper,
       MAX(a.inn) as inn_num,
       MAX(a.name_eng) as name,
       MAX(coalesce(ts.full_name_eng,a.name_eng)) as full_nm,
       MAX(stg.end_circ) as end_date,
       MIN(stg.begin_circ) as start_date,
       MAX(case when sk.name_eng IS NULL then sk.name_uk else sk.name_eng end) as subtype_nm
FROM (SELECT s.*, rank() over (PARTITION BY isin,state_number ORDER BY operdate desc) as rn
      FROM stocks s 
      WHERE isin IS NOT NULL and state_number IS NOT NULL) a
JOIN trading_stocks ts ON ts.emission_is=a.id
JOIN stocks_trading_grounds stg ON stg.stocks_full_id=a.id
JOIN stocks_kinds sk ON sk.id=a.kind_id
WHERE stg.end_circ >= '2021-01-01 00:00:00' and a.rn=1
GROUP BY stg.stocks_full_id

注：额外修正了原SQL中字符串常量的引号问题，Oracle中字符串需用单引号包裹，双引号会被识别为对象名导致报错。

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