用于查找负责监督最大员工人数的经理姓名的SQL查询是什么？

Question

person_id | manager_id | name |
          |            |      |
-------------------------------

查询以查找负责最大员工人数的经理的姓名？

补充： 这是唯一的表。是自我参照。DB是mysql。递归查询也可以。

Answer 1

我不清楚您想要什么，所以如果这不是您想要的，请澄清您的问题。

如果存在平局，此查询仅返回经理之一：

SELECT T2.name FROM (
    SELECT manager_id
    FROM table1
    WHERE manager_id IS NOT NULL
    GROUP BY manager_id
    ORDER BY count(*) DESC
    LIMIT 1
) AS T1
JOIN table1 AS T2
ON T1.manager_id = T2.person_id

查询结果：

Bar

这是一个查询，该查询在出现并列的情况下以并列的最大数量获取所有经理：

SELECT name FROM (
    SELECT manager_id, COUNT(*) AS C
    FROM person
    WHERE manager_id IS NOT NULL
    GROUP BY manager_id) AS Counts
JOIN (
    SELECT COUNT(*) AS C
    FROM person
    WHERE manager_id IS NOT NULL
    GROUP BY manager_id
    ORDER BY COUNT(*) DESC
    LIMIT 1
) AS MaxCount
ON Counts.C = MaxCount.C
JOIN person
ON Counts.manager_id = person.person_id

第二个查询的结果：

Foo
Bar

这是我的测试数据：

CREATE TABLE Table1 (person_id int NOT NULL, manager_id nvarchar(100) NULL, name nvarchar(100) NOT NULL);
INSERT INTO Table1 (person_id, manager_id, name) VALUES
(1, NULL, 'Foo'),
(2, '1', 'Bar'),
(3, '1', 'Baz'),
(4, '2', 'Qux'),
(5, '2', 'Quux'),
(6, '3', 'Corge');