sql - 简单错误“ORA-00979：不是 GROUP BY 表达式”

Question

我正在尝试列出全职员工处理的全名和交易总数，并按交易总数的降序排列结果。

这就是我所拥有的

select 
    concat( e.efirst, e.elast ) ename, 
    count(*) total_transactions
from 
    transactions t 
    join employees e on t.employeeid = e.employeeid
where 
    e.etype = 'Fulltime' 
group by
    t.employeeid 
order by
    total_transactions desc;

标签： sql

Answer 1

当计算由外键分组的聚合时，您需要外部查询中JOIN的主体关系（在本例中），因为它与聚合是一个单独的关注点。employee

SELECT
    CONCAT( CONCAT( employee.efirst, ' ' ), employee.elast ) AS employee_name, 
    employee_id,
    total_transactions
FROM
    (
        SELECT
            employeeid AS employee_id,
            COUNT(*) AS total_transactions
        FROM
            transactions
        GROUP BY
            employeeid
    ) AS sq
    INNER JOIN employee ON sq.employee_id = employee.employeeid
WHERE
    employee.etype = 'Fulltime'
ORDER BY
    total_transactions desc;