SQL:按表分组

Question

假设我们使用 PostgreSQL 并有 2 个表，department 和 employee，后者属于前者并且与前者有 FK。

我们现在想做一个聚合选择，我们想把所有来自 department 的信息和一些来自 employee 的聚合值放在一起:

SELECT d.id, d.name, d.budget, count(*), avg(e.salary), max(e.age), sum(e.children)
FROM department d LEFT JOIN employee e ON e.dept = d.id
GROUP BY d.id, d.name, d.budget

我不喜欢我需要在 GROUP BY 中指定 department 的所有列 - 有没有办法“按整个表分组”？

还有一个更哲学的问题，假设我执行 GROUP BY d.id。假设 d.id 是 department 的主键，为什么我还需要按所有其他列进行分组？

Answer 1

如果 employee 是预聚合的，则不需要列出 select 列

select *
from
    department d
    left join (
        select
            dept as id,
            count(*) as count_employee,
            avg(salary) as avg_salary,
            max(age) as max_age,
            sum(children) as sum_children
        from employee
        group by dept
    ) e using (id)

using 子句避免了 joined on 列的重复性。