sql - 不是 Group by 表达式错误 [连接表]

Question

当我试图获得与不同主题名称和员工姓名分组的最大值列时，分组不工作。

第一个大括号内的选择查询工作正常，我从中获得了所需的输出。但是，我无法为每个员工获得最大价值，因此受到影响。

select  staff_name, subject_name, max(value) from 
(select Staff.staff_name, Subject.subject_name, Mark.value as value 
from ((staff inner join subject on Staff.staff_id=Subject.staff_id)
inner join Mark ON Mark.subject_id=Subject.subject_id) 
group by subject_name);

错误是：

ORA-00979: not a GROUP BY expression

标签： sqldatabaseoracleoracle11g

Answer 1

似乎您的嵌套 () 顺序错误，并且您错过了 group by 中未聚合的列

select  t.staff_name, t.subject_name, max(t.value) 
from (
    select Staff.staff_name, Subject.subject_name, Mark.value as value 
    from staff 
    inner join subject on Staff.staff_id=Subject.staff_id
    inner join Mark ON Mark.subject_id=Subject.subject_id
) t
group by t.staff_name, t.subject_name;