如何获取计数最大值的详细信息？

tzdcorbm 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(406)

我有一张table：

|eid   |ManagerID|Phone#|(other details..|
|------|---------|------|----------------|
|1001  |1004     |12345 |.........       |
|1002  |1004     |1233  |.........       |
|1003  |1006     |133   |.........       |
|1004  |         |444   |.........       |
|1005  |1004     |555   |.........       |
|1006  |         |666   |.........       |

等等
我试图显示管理最多员工的经理的所有字段。
我有

SELECT *
FROM EMPLOYEE 
GROUP BY Manager
HAVING COUNT (Manager)=(
SELECT MAX(theCount) AS theCount
FROM (
SELECT Manager, COUNT(Manager) theCount
FROM EMPLOYEE
GROUP BY Manager));

但不断出现错误：

Incorrect syntax near ')'.

我不知道为什么。
我知道这一部分的作用是给出一个包含经理人数的表格：

SELECT Manager, COUNT(Manager) 
AS theCount 
FROM EMPLOYEE 
GROUP BY Manager

sql sql-server count Max

来源：https://stackoverflow.com/questions/62977740/how-do-i-get-the-details-of-the-max-of-a-count

2条答案

按热度按时间

qcbq4gxm1#

WITH managers AS (
SELECT
  ManagerID,
  count(eid) as directs_count
FROM employee
GROUP BY ManagerID
)
SELECT *
FROM employee
WHERE
  eid in (SELECT TOP 1 ManagerID FROM managers ORDER BY directs_count DESC)

赞(0）回复(0）举报 2021-07-24

wwtsj6pe2#

你可以试试下面的-
演示

select * from
(
select managerid,count(*) as cnt,row_number() over(order by count(*) desc)
as rn
from
employee where managerid is not null
group by managerid
)A where rn=1

输出：

managerid   cnt rn
1004         3  1

赞(0）回复(0）举报 2021-07-24

我来回答

如何获取计数最大值的详细信息？

2条答案

相关问题

热门标签

最新问答