对employees表和department表进行分组后,再连接;与连接分组,再连接返回的结果不同.
要求:查询每个部门有多少人,包含没有部门的人,同时列出部门ID,部门name,最后的结果如下
DEPARTMENT_ID DEPARTMENT_NAME COUNTS
------------- ------------------------------ ----------
100 Finance 6
30 Purchasing 6
1
20 Marketing 2
70 Public Relations 1
90 Executive 3
110 Accounting 2
50 Shipping 45
40 Human Resources 1
80 Sales 34
60 IT 5
测试1
(1)建立实体子表,拆开测试,为正常返回值
select count(*) counts,department_id from employees group by department_id;
COUNTS DEPARTMENT_ID
---------- -------------
6 100
6 30
1
2 20
1 70
3 90
2 110
45 50
1 40
34 80
5 60
11 rows selected.
create table D2 as select * from (select count(*) counts,department_id from employees group by department_id);
Table created.
17-MAR-12 HR @ hdb>desc D2;
Name Null? Type
-------------------------------------------------------------------------
COUNTS NUMBER
DEPARTMENT_ID NUMBER(4)
select D2.department_id,D.department_name,D2.counts from D2 left join departments D on D2.department_id = D.department_id;
DEPARTMENT_ID DEPARTMENT_NAME COUNTS
------------- ------------------------------ ----------
100 Finance 6
30 Purchasing 6
1
20 Marketing 2
70 Public Relations 1
90 Executive 3
110 Accounting 2
50 Shipping 45
40 Human Resources 1
80 Sales 34
60 IT 5
11 rows selected.
(2),D2表为虚拟表时,含有NULL的行丢失.
select D2.department_id,D.department_name,D2.counts from (select count(*) counts,
department_id
from employees
group by department_id) D2left join departments D
on D2.department_id = D.department_id;
DEPARTMENT_ID DEPARTMENT_NAME COUNTS
------------- ------------------------------ ----------
80 Sales 34
50 Shipping 45
20 Marketing 2
90 Executive 3
70 Public Relations 1
60 IT 5
110 Accounting 2
30 Purchasing 6
40 Human Resources 1
100 Finance 6
10 rows selected.
测试2
当在统计发生在连接的时候,再进行连接,结果正常.
select t1.department_id,
d1.department_name,
t1.counts
from (select e.department_id,count(*) counts from employees e left join departments d on e.department_id = d.department_id group by e.department_id) t1
left join departments d1
on t1.department_id = d1.department_id;
DEPARTMENT_ID DEPARTMENT_NAME COUNTS
------------- ------------------------------ ----------
100 Finance 6
30 Purchasing 6
1
20 Marketing 2
70 Public Relations 1
90 Executive 3
110 Accounting 2
50 Shipping 45
40 Human Resources 1
80 Sales 34
60 IT 5
11 rows selected.
其中的子查询t1如下
select e.department_id,count(e.employee_id) countsfrom employees eleft join departments d on e.department_id = d.department_id group by e.department_id;
DEPARTMENT_ID COUNTS
---------------------- ----------------------
100 6
30 6
1
20 2
70 1
90 3
110 2
50 45
40 1
80 34
60 5
11 rows selected
此时下面的查询和t1的结果一样,但无法和departments连接成功,请问这个是为什么呢?
select e.department_id,count(e.employee_id) countsfrom employees egroup by e.department_id;
DEPARTMENT_ID COUNTS
---------------------- ----------------------
100 6
30 6
1
20 2
70 1
90 3
110 2
50 45
40 1
80 34
60 5
11 rows selected