使用MAX函数查询学生最新评论时遇ORA-00937/ORA-00934错误求助

错误原因 #

1. ORA-00937：你使用了MAX(TABLE3_ACTIVITY_DATE)聚合函数，但SELECT列表里还包含大量非聚合列（比如学生ID、姓名、评论内容等），这些非聚合列要么全部放到GROUP BY子句中，要么用窗口函数处理——你没按规则操作，所以触发报错。
2. ORA-00934：GROUP BY子句只能放列名或普通表达式，不能嵌套MAX()这类聚合函数，你把聚合函数写进GROUP BY里，属于语法错误。

正确解法 #

你的需求是获取每个学生的最新评论，这里提供两种常用的实现方式：

方法1：用窗口函数ROW_NUMBER()（逻辑清晰，推荐使用）

给每个学生的评论按TABLE3_ACTIVITY_DATE倒序排号，直接取排号为1的那条（即最新评论）：

SELECT 
    "Student ID",
    "Last Name",
    "First Name",
    "Delinquency Code",
    "Comment",
    "Comment date",
    "Latest_TABLE3_ACTIVITY_DATE",
    "Commented by",
    "TABLE2_ACTIVITY_DATE"
FROM (
    SELECT 
        STUDENTTABLE1.STUDENTTABLE1_ID AS "Student ID",
        STUDENTTABLE1.STUDENTTABLE1_LAST_NAME AS "Last Name",
        STUDENTTABLE1.STUDENTTABLE1_FIRST_NAME AS "First Name",
        TABLE2.TABLE2_DELI_CODE AS "Delinquency Code",
        DBMS_LOB.SUBSTR(TABLE3_COMMENT) AS "Comment",
        TABLE3.TABLE3_ACTIVITY_DATE AS "Comment date",
        MAX(TABLE3.TABLE3_ACTIVITY_DATE) OVER (PARTITION BY STUDENTTABLE1.STUDENTTABLE1_ID) AS "Latest_TABLE3_ACTIVITY_DATE",
        TABLE3.TABLE3_CREATED_BY AS "Commented by",
        TABLE2.TABLE2_ACTIVITY_DATE,
        -- 按学生分组，评论日期倒序排序，最新的评论排第1
        ROW_NUMBER() OVER (PARTITION BY STUDENTTABLE1.STUDENTTABLE1_ID ORDER BY TABLE3.TABLE3_ACTIVITY_DATE DESC) AS rn
    FROM TABLE2
    INNER JOIN STUDENTTABLE1
        ON STUDENTTABLE1.STUDENTTABLE1_PIDM = TABLE2.TABLE2_PIDM
    INNER JOIN TABLE3
        ON TABLE3.TABLE3_PIDM = STUDENTTABLE1.STUDENTTABLE1_PIDM
    WHERE STUDENTTABLE1.STUDENTTABLE1_CHANGE_IND IS NULL
        AND TABLE3.TABLE3_ORIG_CODE = 'SectionA'
        AND STUDENTTABLE1.STUDENTTABLE1_ID IN ('101', '213', '141', '123', '103')
) t
WHERE rn = 1;

方法2：先子查询获取最新日期，再关联原表

先查出每个学生的最新评论时间，再用这个时间匹配对应的评论记录：

SELECT 
    STUDENTTABLE1.STUDENTTABLE1_ID AS "Student ID",
    STUDENTTABLE1.STUDENTTABLE1_LAST_NAME AS "Last Name",
    STUDENTTABLE1.STUDENTTABLE1_FIRST_NAME AS "First Name",
    TABLE2.TABLE2_DELI_CODE AS "Delinquency Code",
    DBMS_LOB.SUBSTR(TABLE3_COMMENT) AS "Comment",
    TABLE3.TABLE3_ACTIVITY_DATE AS "Comment date",
    latest.latest_date AS "Latest_TABLE3_ACTIVITY_DATE",
    TABLE3.TABLE3_CREATED_BY AS "Commented by",
    TABLE2.TABLE2_ACTIVITY_DATE
FROM TABLE2
INNER JOIN STUDENTTABLE1
    ON STUDENTTABLE1.STUDENTTABLE1_PIDM = TABLE2.TABLE2_PIDM
INNER JOIN TABLE3
    ON TABLE3.TABLE3_PIDM = STUDENTTABLE1.STUDENTTABLE1_PIDM
-- 子查询获取每个学生的最新评论日期
INNER JOIN (
    SELECT 
        TABLE3_PIDM,
        MAX(TABLE3_ACTIVITY_DATE) AS latest_date
    FROM TABLE3
    WHERE TABLE3_ORIG_CODE = 'SectionA'
    GROUP BY TABLE3_PIDM
) latest
    ON latest.TABLE3_PIDM = STUDENTTABLE1.STUDENTTABLE1_PIDM
    AND latest.latest_date = TABLE3.TABLE3_ACTIVITY_DATE
WHERE STUDENTTABLE1.STUDENTTABLE1_CHANGE_IND IS NULL
    AND STUDENTTABLE1.STUDENTTABLE1_ID IN ('101', '213', '141', '123', '103');

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