sql - 如何获取列值以及聚合值 (MAX)

Question

我使用 postgresql 作为我的存储引擎。

我有一个员工到经理的自外键引用。

这是我的 employee 表的样子:

employee_id   firstname  lastname   manager_id
11;           "Chuck";   "Norris";  NULL
1;            "Sergey";  "Brin";    11
3;            "Larry";   "Page";    11
5;            "Bill";    "Gates";   4
4;            "Father";  "Bill";    NULL

现在我想要一个查询来显示在他/她手下拥有最多员工的员工的(manager_id 和计数)。

那是上表，我预计

    id   max_count
    11        2

这个查询有效；

select MAX(y.count) as max_count FROM
(select m.manager_id as id, count(m.manager_id) as count from employee m GROUP BY (id) ) y;

max_count
    2

但这不起作用——我只包含了 id 列。

select y.id, MAX(y.count) as max_count FROM
(select m.manager_id as id, count(m.manager_id) as count from employee m GROUP BY (id) ) y;

我收到以下错误:

ERROR:  column "y.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select y.id, MAX(y.count) as max_count FROM
               ^

********** Error **********

ERROR: column "y.id" must appear in the GROUP BY clause or be used in an aggregate function
SQL state: 42803

Character: 8

为什么这不起作用？

Answer 1

SELECT 列表中的任何非聚合字段都必须包含在 GROUP BY 中。您可以通过使用 LIMIT 或使用窗口/分析函数来获得您想要的:

select y.id, y.count as max_count 
FROM  (select m.manager_id as id
            , count(m.manager_id) as count 
       from employee m 
       GROUP BY id 
      ) y
order by count DESC
limit 1;

正如评论中指出的，更直接:

select m.manager_id as id
     , count(m.manager_id) as count 
from employee m 
GROUP BY id 
order by count(m.manager_id) DESC
limit 1;