select d.dname,d.loc,count(*),avg(e.sal)
2 from dept d left outer join emp e
3 on d.deptno = e.deptno
4 group by d.dname,d.loc;DNAME LOC COUNT(*) AVG(E.SAL)
-------------- ------------- ---------- ----------
RESEARCH DALLAS 5 2175
SALES CHICAGO 5 1690
ACCOUNTING NEW YORK 2 3150
OPERATIONS BOSTON 1
OPERATIONS部门里面没有员工,但为什么员工数是1?
2 from dept d left outer join emp e
3 on d.deptno = e.deptno
4 group by d.dname,d.loc;DNAME LOC COUNT(*) AVG(E.SAL)
-------------- ------------- ---------- ----------
RESEARCH DALLAS 5 2175
SALES CHICAGO 5 1690
ACCOUNTING NEW YORK 2 3150
OPERATIONS BOSTON 1
OPERATIONS部门里面没有员工,但为什么员工数是1?
2 from dept d inner join emp e
3 on d.deptno = e.deptno
4 group by d.dname,d.loc;
你这个没有OPERATIONS部门啊……
from dept d left outer join emp e
on d.deptno = e.deptno
group by d.dname,d.loc;
是只分组中 记录的条数 不一定是员工数吧
要是生成的结果 员工的id,姓名这些列是null
而其它的列有不为null的 那么 count(*) 这一列是算进来的
但是你count(员工的id) 这一列是0
你说count(*)还是员工数?
你用count(*) 肯定是会有记录啊用count(员工ID)吧