用于查找负责监督最大雇员人数的经理姓名的SQL查询是什么? [英] What is the SQL query for finding the name of manager who supervises maximum number of employees?

问题描述

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

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

Query to find name of manager who supervises maximum number of employees?

已添加:这是唯一的表格.是自我参照. DB是mysql.递归查询也可以.

Added: This is the only table. Yes self-referencing. DB is mysql. Recursive queries will also do.

推荐答案

我尚不清楚您想要什么，因此，如果不是您想要的，请澄清您的问题.

It's not entirely clear to me what you want, so if this isn't what you want please clarify your question.

如果存在平局，此查询仅返回其中一位经理:

This query returns just one of the managers if there is a tie:

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

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

Here's a query that fetches all managers with the tied maximum count in the case that there is a tie:

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');

这篇关于用于查找负责监督最大雇员人数的经理姓名的SQL查询是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！