一道面试题

Question

数据库mysql插入两张表 dept和emp若干条记录如下insert into dept(dept_name) values('技术部');
insert into dept(dept_name) values('人力资源部');
insert into dept(dept_name) values('销售部');
insert into dept(dept_name) values('售后服务部');
insert into dept(dept_name) values('市场策划部');
insert into dept(dept_name) values('后勤部');
insert into emp(emp_name,dept_id) values('tom', 1);
insert into emp(emp_name,dept_id) values('jetty', 1);
insert into emp(emp_name,dept_id) values('max', 1);
insert into emp(emp_name,dept_id) values('freeman', 1);
insert into emp(emp_name,dept_id) values('henry', 1);
insert into emp(emp_name,dept_id) values('cctv', 2);
insert into emp(emp_name,dept_id) values('gov', 2);
insert into emp(emp_name,dept_id) values('ba', 2);
insert into emp(emp_name,dept_id) values('sun', 2);
insert into emp(emp_name,dept_id) values('xml', 3);
insert into emp(emp_name,dept_id) values('ono', 3);
insert into emp(emp_name,dept_id) values('tamad', 4);
insert into emp(emp_name,dept_id) values('andy', 4);
insert into emp(emp_name,dept_id) values('too', 5);其中 后勤部 没有员工的记录问题是 查询每个部门的员工总数
我查过了没后勤部是1，但是后勤部没有员工
我的sql
select dept.dept_name, count(dept.id,0) emp_count
from emp right outer join dept on emp.dept_id=dept.id
group by dept.id我知道是count惹的祸。有没有其它的思路???
多谢了诸位

Answer 1

count(dept.id,0)改为sum(nvl2(emp.dept_id,1,0))

Answer 2

select dept.dept_name, count(*) emp_count 
from emp right outer join dept on emp.dept_id=dept.id 
group by dept.dept_name

Answer 3

我是mysql的库，好像没有nvl

Answer 4

问题依旧，就是那个该死的count

Answer 5

SELECT d.dept_name, ed.emp_count
  FROM
 (SELECT e.dept_id, count(*) emp_count FROM emp e GROUP BY e.dept_id) ed right OUTER JOIN dept d
  ON d.dept_id = ed.dept_id

Answer 6

最后销售部还是 null 而不是0，看来这个问题应该没有解答了！

Answer 7

select dept.dept_name, count(*) emp_count 
from emp left outer join dept on emp.dept_id=dept.id 
group by dept.dept_name;
试试

Answer 8

因为你是右外连接 在dept  表里面有一条记录 所以改成左外连接 应该就ok

Answer 9

mysql> select * from dept;
+----+------------+
| id | dept_name  |
+----+------------+
|  1 | 技术部     |
|  2 | 人力资源部 |
|  3 | 销售部     |
|  4 | 售后服务部 |
|  5 | 市场策划部 |
|  6 | 后勤部     |
+----+------------+
6 rows in set (0.02 sec)mysql> select * from emp;
+----------+---------+
| emp_name | dept_id |
+----------+---------+
| tom      |       1 |
| jetty    |       1 |
| max      |       1 |
| freeman  |       1 |
| henry    |       1 |
| cctv     |       2 |
| gov      |       2 |
| ba       |       2 |
| sun      |       2 |
| xml      |       3 |
| ono      |       3 |
| tamad    |       4 |
| andy     |       4 |
| too      |       5 |
+----------+---------+
14 rows in set (0.00 sec)mysql> select dept.dept_name,count(1) from dept
    -> left join emp on dept.id=emp.dept_id
    -> group by dept_name;
+------------+----------+
| dept_name  | count(1) |
+------------+----------+
| 人力资源部 |        4 |
| 售后服务部 |        2 |
| 市场策划部 |        1 |
| 销售部     |        2 |
| 后勤部     |        1 |
| 技术部     |        5 |
+------------+----------+
6 rows in set (0.00 sec)这条不是后勤部嘛
insert into emp(emp_name,dept_id) values('too', 5); 

Answer 10

mysql> select * from dept;
+----+------------+
| id | dept_name  |
+----+------------+
|  1 | 技术部     |
|  2 | 人力资源部 |
|  3 | 销售部     |
|  4 | 售后服务部 |
|  5 | 市场策划部 |
|  6 | 后勤部     |
+----+------------+
6 rows in set (0.00 sec)mysql> select * from emp;
+----------+---------+
| emp_name | dept_id |
+----------+---------+
| tom      |       1 |
| jetty    |       1 |
| max      |       1 |
| freeman  |       1 |
| henry    |       1 |
| cctv     |       2 |
| gov      |       2 |
| ba       |       2 |
| sun      |       2 |
| xml      |       3 |
| ono      |       3 |
| tamad    |       4 |
| andy     |       4 |
| too      |       5 |
+----------+---------+
14 rows in set (0.00 sec)mysql> select dept.dept_name,sum(if(ifnull(dept_id,0),1,0)) from dept
    -> left join emp on dept.id=emp.dept_id
    -> group by dept_name
    -> order by id;
+------------+--------------------------------+
| dept_name  | sum(if(ifnull(dept_id,0),1,0)) |
+------------+--------------------------------+
| 技术部     |                              5 |
| 人力资源部 |                              4 |
| 销售部     |                              2 |
| 售后服务部 |                              2 |
| 市场策划部 |                              1 |
| 后勤部     |                              0 |
+------------+--------------------------------+
6 rows in set (0.00 sec)

Answer 11

 oracle 用这个可以：
select dept.dept_name,count(dept.dept_name) from dept, emp where dept.id(+)=emp.dept_id group by dept.dept_name

Answer 12

楼主 你用外连接count是dept的dept_id，所以有一条记录；如果换成emp的字段count就没问题了
select d.dept_name,nvl(count(e.dept_id),0)
from emp e,dept d
where e.dept_id(+)=d.dept_id
group by 
         d.dept_name

Answer 13

/**
**测试
*/
create table deptTemp
(
 deptno integer primary key,
 dname varchar2(50)
)create table empTemp
(
  empno integer primary key,
  ename varchar2(50),
  deptno integer
)insert into deptTemp values(1,'技术部'); 
insert into deptTemp values(2,'人力资源部'); 
insert into deptTemp values(3,'销售部'); 
insert into deptTemp values(4,'售后服务部'); 
insert into deptTemp values(5,'市场策划部'); 
insert into deptTemp values(6,'后勤部'); insert into empTemp(empno,ename,deptno) values(1,'tom', 1); 
insert into empTemp(empno,ename,deptno) values(2,'jetty', 1); 
insert into empTemp(empno,ename,deptno) values(3,'max', 1); 
insert into empTemp(empno,ename,deptno) values(4,'freeman', 1); 
insert into empTemp(empno,ename,deptno) values(5,'henry', 1); 
insert into empTemp(empno,ename,deptno) values(6,'cctv', 2); 
insert into empTemp(empno,ename,deptno) values(7,'gov', 2); 
insert into empTemp(empno,ename,deptno) values(8,'ba', 2); 
insert into empTemp(empno,ename,deptno) values(9,'sun', 2); 
insert into empTemp(empno,ename,deptno) values(10,'xml', 3); 
insert into empTemp(empno,ename,deptno) values(11,'ono', 3); 
insert into empTemp(empno,ename,deptno) values(12,'tamad', 4); 
insert into empTemp(empno,ename,deptno) values(13,'andy', 4); 
insert into empTemp(empno,ename,deptno) values(14,'too', 5); select d.dname 部门,count(e.ename) 人员数量
from empTemp e,deptTemp d where e.deptno(+) = d.deptno group by d.dname order by 人员数量;测试结果：后勤部 0
市场策划部 1
销售部 2
售后服务部 2
人力资源部 4
技术部 5