What is the SQL query for finding the name of manager who supervises maximum number of employees?
person_id | manager_id | name |
| | |
-------------------------------
Query to f开发者_如何学JAVAind name of manager who supervises maximum number of employees?
Added: This is the only table. Yes self-referencing. DB is mysql. Recursive queries will also do.
This query returns the manager_id
and manager_name
of the manager with the maximal number of employees.
The trick is in the HAVING
clause, which allows aggregates and counts over multiple rows.
SELECT manager_id,name, count(*)
FROM table
GROUP BY manager_id, name
HAVING max(count(*));
You can read more in the short but informative w3schools.com HAVING clause
tutorial.
If the manager_id references a person id in the same table, Svinto's answer might be more suitable.
SELECT name
FROM table
WHERE person_id = (
SELECT manager_id
FROM table
GROUP BY manager_id
HAVING max(count(*)))
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
Result of query:
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
Result of the second query:
Foo
Bar
Here's my test data:
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');
Assuming manager_id have a reference to person_id and name of table: table_name
SELECT name FROM (
SELECT manager_id
FROM table_name
GROUP BY manager_id
ORDER BY COUNT(*) DESC
LIMIT 1
) t
INNER JOIN table_name ON t.manager_id = table_name.person_id
edit: Removed HAVING MAX COUNT, added ORDER BY COUNT DESC LIMIT 1 in subquery
精彩评论