查找每个部门的员工人数 - SQL Oracle
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36407565/
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
Find the number of employees in each department - SQL Oracle
提问by user3395936
I have two tables Empand Deptand I am trying to display how many people work in each department along with their department name, but I can't get it to work. I have tried this select count(ename) as count from emp group by deptno;but the output I am getting is this :
我有两个表Emp，Dept我试图显示每个部门有多少人工作以及他们的部门名称，但我无法让它工作。我试过这个，select count(ename) as count from emp group by deptno;但我得到的输出是这样的：
COUNT
----------
6
5
3
But I also want to show the names for each of those departments. Here are my tables :
但我也想显示每个部门的名称。这是我的表：
Empt:
空：
CREATE TABLE EMP
(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2));
INSERT INTO EMP VALUES
(7369, 'SMITH', 'CLERK', 7902,
TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMP VALUES
(7499, 'ALLEN', 'SALESMAN', 7698,
TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO EMP VALUES
(7521, 'WARD', 'SALESMAN', 7698,
TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO EMP VALUES
(7566, 'JONES', 'MANAGER', 7839,
TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES
(7654, 'MARTIN', 'SALESMAN', 7698,
TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES
(7698, 'BLAKE', 'MANAGER', 7839,
TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES
(7782, 'CLARK', 'MANAGER', 7839,
TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES
(7788, 'SCOTT', 'ANALYST', 7566,
TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
(7839, 'KING', 'PRESIDENT', NULL,
TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES
(7844, 'TURNER', 'SALESMAN', 7698,
TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
INSERT INTO EMP VALUES
(7876, 'ADAMS', 'CLERK', 7788,
TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES
(7900, 'JAMES', 'CLERK', 7698,
TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
INSERT INTO EMP VALUES
(7902, 'FORD', 'ANALYST', 7566,
TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
(7934, 'MILLER', 'CLERK', 7782,
TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
Dept:
部门：
CREATE TABLE DEPT
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13) );
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
回答by Eray Balkanli
Please try:
请尝试：
select count(*) as count,dept.DNAME
from emp
inner join dept on emp.DEPTNO = dept.DEPTNO
group by dept.DNAME
回答by Ray
A request to list "Number of employees in each department" or "Display how many people work in each department" is the same as "For each department, list the number of employees", this must include departments with no employees. In the sample database, Operations has 0 employees. So a LEFT OUTER JOIN should be used.
请求列出“每个部门的员工人数”或“显示每个部门有多少人工作”与“对于每个部门，列出员工人数”相同，这必须包括没有员工的部门。在示例数据库中，Operations 有 0 名员工。所以应该使用 LEFT OUTER JOIN。
SELECT dept.name, COUNT(emp.empno) AS count
FROM dept
LEFT OUTER JOIN emp ON emp.deptno = dept.deptno
GROUP BY dept.name;
回答by Gaurav
select count(e.empno), d.deptno, d.dname
from emp e, dep d
where e.DEPTNO = d.DEPTNO
group by d.deptno, d.dname;
回答by user9526167
Try the query below:
试试下面的查询：
select count(*),d.dname from emp e , dept d where d.deptno = e.deptno
group by d.dname
回答by Walter_Ritzel
SELECT d.DEPTNO
, d.dname
, COUNT(e.ename) AS count
FROM emp e
INNER JOIN dept d ON e.DEPTNO = d.deptno
GROUP BY d.deptno
, d.dname;
回答by S Paul
select d.dname
,count(e.empno) as count
from dept d
left outer join emp e
on e.deptno=d.deptno
group by d.dname;
回答by padma
Try to do this:
尝试这样做：
SQL> select dept,count(*) "no of emp" from employee group by dept;
DEPT no of emp
-------------------- ----------
HR 2
Account 2
Admin 3
