含HAVING子句与子查询的Select语句报ORA-00979错误排查

原SQL的问题点 #

• HAVING子句误用：外层查询未进行GROUP BY分组操作，却直接使用HAVING过滤结果，HAVING仅用于分组后的结果过滤，无分组场景需改用WHERE子句。
• 表别名错误：内层子查询中GROUP BY lt.set_off_id和SUM(lt.amount)里的别名lt未定义，实际应使用已声明的表别名lti。
• 关联逻辑错误：lto.id IN (SELECT lti.set_off_id...)逻辑不匹配，子查询返回的是符合条件的set_off_id，应改为lto.set_off_id IN (...)，才能正确关联到对应的抵消组交易记录。

修正后的SQL #

SELECT ad.id 
FROM account_detail ad 
WHERE ad.account_status_code = 'ASDS'
  AND (
    SELECT COUNT(DISTINCT lto.txn_date) 
    FROM loan_transaction lto 
    WHERE lto.set_off_id IN (
      SELECT lti.set_off_id 
      FROM loan_transaction lti 
      WHERE lti.account_id = ad.id 
      GROUP BY lti.set_off_id 
      HAVING SUM(lti.amount) > 0
    )
  ) > 6;

补充说明 #

1. 外层查询将原HAVING条件移至WHERE子句，符合SQL语法规范。
2. 修正内层子查询的别名错误，确保分组和聚合函数引用正确的表别名。
3. 调整关联条件为lto.set_off_id IN (...)，正确关联到满足SUM(amount)>0的抵消组，统计这些组对应的唯一交易日期数量。
4. 该SQL可直接适配JPQL的分页需求，只需在外层查询添加分页相关的OFFSET/LIMIT（或通过JPA的setFirstResult/setMaxResults方法实现）即可。

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