oracle 在员工表中显示经理姓名和报告他的员工人数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24626771/
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
display manager name and count of employees reporting him in employees table
提问by rajeshrepalle
I want to display manager_name and count of employees reporting him in employees table.I want to sort the data based on count IE maximum employees reporting to a manager should come first.
我想在员工表中显示 manager_name 和报告他的员工数。我想根据计数对数据进行排序 IE 向经理报告的最大员工数应该排在第一位。
I tried to write self join but i could not get the out put .
我尝试编写 self join ，但无法输出。
EMPLOYEE_ID FIRST_NAME MANAGER_ID SALARY HIRE_DATE
EMPLOYEE_ID FIRST_NAME MANAGER_ID SALARY HIRE_DATE
198 Donald 124 2600 21-JUN-99
199 Douglas 124 2600 13-JAN-00
200 Jennifer 101 4400 17-SEP-87
201 Michael 100 13000 17-FEB-96
202 Pat 201 6000 17-AUG-97
203 Susan 101 6500 07-JUN-94
204 Hermann 101 10000 07-JUN-94
205 Shelley 101 12000 07-JUN-94
206 William 205 8300 07-JUN-94
100 Steven 24000 17-JUN-87
101 Neena 100 17000 21-SEP-89
the table name is employees and i want to see names also
表名是员工，我也想看到名字
回答by Vijay R.
You can use the aggregate function COUNTand ORDER BYclause You didn't mention the table name assuming the table name as EMPLOYEES, below query would help you.
您可以使用聚合函数COUNT和ORDER BY子句您没有提到假设表名为 EMPLOYEES 的表名，下面的查询会对您有所帮助。
SELECT MANAGER_ID, COUNT(EMPLOYEE_ID) as EMP_COUNT
FROM EMPLOYEES
GROUP BY MANAGER_ID
ORDER BY EMP_COUNT DESC;
Here EMP_COUNT is the column alias name.If you don't want any column alias you can simply use the query below.
这里 EMP_COUNT 是列别名。如果您不想要任何列别名，您可以简单地使用下面的查询。
SELECT MANAGER_ID, COUNT(EMPLOYEE_ID)
FROM EMPLOYEES
GROUP BY MANAGER_ID
ORDER BY COUNT(EMPLOYEE_ID) DESC;
If you want to sort by ascending order instead of DESC you can use ASC.
如果要按升序而不是 DESC 排序，可以使用 ASC。
回答by Rajeev Singh
We can get this output using an analytical function:
我们可以使用分析函数得到这个输出：
SELECT E.EMPID,E.EMPNAME as "Manager Name",M.EMPNAME AS "Employee Name",count(*) over(partition by e.empid) reportee_count
from empmgid m,empmgid e where M.MAGID=e.EMPID order by reportee_count desc;
回答by Rohit
Please employ the following SQL-Query:
请使用以下 SQL 查询：
SELECT
e.empno,
e.ename,
e1.empcnt
FROM
emp e,
(
SELECT
mgr,
COUNT(*) empcnt
FROM
emp
GROUP BY
mgr
) e1
WHERE
e.empno = e1.mgr;
回答by Ranaj Parida
-- Restricting which manager is having two employees working under them
-----------------------------------------------------------------------
SELECT E1.* FROM
(
SELECT E1.EMPNO,E1.ENAME AS EMPLOYE,
M1.ENAME AS MANAGERS,
COUNT(*)
OVER
(
PARTITION BY E1.EMPNO
) EMPCNT
FROM EMP E1,EMP M1
WHERE M1.MGR=E1.EMPNO
) E1
WHERE EMPCNT = 2;
