编写 SQL 查询以获取列中值的最大出现次数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6919880/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Writing SQL query for getting maximum occurrence of a value in a column
提问by anonymous
I have an emptable with the records below:
我有一个emp包含以下记录的表格：
INSERT into emp(EmpId,Emp name, Manager)
Values(1,A,M1)
values(2,B,M1)
values(3,C,M2)
values(4,D,M3)
How can I find the Managerhaving the maximum number of employees under him? In this case, output should be M1. Please help.
我怎样才能找到Manager他手下最多的员工？在这种情况下，输出应该是M1。请帮忙。
回答by Brian Hoover
select manager, count(*) as employees from emp
group by manager
order by count(*) desc
Take the first record. Depending on your SQL version, you can do this with a limit statement.
取得第一条记录。根据您的 SQL 版本，您可以使用限制语句执行此操作。
回答by Nick Rolando
SELECT
Manager,
count(Manager) AS 'Num of Emps'
FROM
emp
GROUP BY
Manager
ORDER BY
'Num of Emps' DESC
The first record will be the manager with the most employees. Also, based on the db provider, you can limit the result set to 1, so you only get the highest record. Here's an example using sql server:
第一个记录将是拥有最多员工的经理。此外，基于 db provider，您可以将结果集限制为 1，因此您只能获得最高记录。下面是一个使用 sql server 的例子：
SELECT
TOP 1 Manager,
count(Manager) AS 'Num of Emps'
FROM
emp
GROUP BY
Manager
ORDER BY
'Num of Emps' DESC
回答by Dan Meier
In SQL Server...
在 SQL Server 中...
SELECT TOP 1 Manager
FROM ( SELECT Manager,
COUNT(Manager) as "ManagerCount"
FROM emp
GROUP BY Manager
ORDER BY "ManagerCount" DESC )
Oracle is a bit different...
甲骨文有点不同......
SELECT Manager
FROM ( SELECT Manager,
COUNT(Manager) as "ManagerCount"
FROM emp
GROUP BY Manager
ORDER BY "ManagerCount" DESC )
WHERE ROWNUM <= 1
回答by Ashish Jha
In Postgresql, create schema Test:
在 Postgresql 中，创建模式测试：
create table Test.Employee (Emp_id numeric, manager_id numeric, Manager_name varchar(20));
insert into Test.Employee(emp_id, manager_id, manager_name ) values(1, 3, 'A'), (2, 3, 'A'), (3, 3, 'A'), (4, 3, 'A'), (5, 11, 'B'), (6, 12, 'C'), (7, 11, 'B');
select manager_name from (select count(manager_id) as mcount, manager_name from test.employee group by manager_name order by mcount DESC) AS TEMP limit 1
回答by ArifMustafa
Tested With SQL Server 2017
测试过 SQL Server 2017
Select TOP 1 City, Count(City) AS 'MAX_COUNT' FROM Customer Group By City Order By 'MAX_COUNT' DESC;
Hope this simple query will help many one.
希望这个简单的查询对很多人有帮助。
回答by Ashish sinha
SELECT
count(e.last_name) count,
d.last_name
FROM
employees e
LEFT OUTER JOIN employees d ON e.manager_id = d.employee_id
GROUP BY
d.last_name
ORDER BY
count DESC;
回答by Bohemian
If you want the row from the emp table, use this:
如果您想要 emp 表中的行，请使用以下命令：
select * from emp
where empid in (select manager from
(select manager, count(*)
from emp
group by 1
having count(*) = (select max(count) from (select manager, count(*) as count from emp group by 1) x)
) y );
This will also return multiple rows in case there is a tie for the most number of employees.
这也将返回多行，以防出现最多员工数的情况。
