时间戳

首页 > 解决方案 > SQL - 输出 2 个表的所有可能关系？

sql - SQL - 输出 2 个表的所有可能关系？

问题描述

我为标题的歧义道歉。如果有人在阅读本文后有更好的建议，我会优雅地更改它。

我有 2 张桌子：

部门表（部门）：

|IDDEPT |  DEPTNAME  |
--------+------------+
| 10    | ACCOUNTING |
| 20    | RESEARCH   |    
| 30    | OPERATIONS |
--------+------------+

EMP表（员工）：

| IDEMP |  EMPNAME  |  MGR  | IDDEPT |
--------+-----------+-------+--------+
| 7001  |   SMITH   | 7007  | 10     |
| 7002  |   ALLEN   | 7006  | 10     |
| 7003  |   WARD    | 7006  | 20     |
| 7004  |   JONES   | 7006  | 20     |
| 7005  |   MARTIN  | 7007  | 10     |
| 7006  |   BLAKE   | 7008  | 20     |
| 7007  |   FORD    | 7008  | 10     |
| 7008  |   KING    | NULL  | 10     |
--------+-----------+-------+--------+

MGR 列是员工的经理标识符。KING 为 NULL，因为他是公司的董事。EMP 表上的 IDDEPT 是员工所属的部门。

我要做的是，对于每个经理（在这种情况下为 KING、FORD 和 BLAKE），显示他们在每个部门（10、20 和 30）监督的员工数量。

对于这个给定的场景，我试图获得的输出（严格来说不是这个顺序）如下：

| IDEMP |  EMPNAME  | IDDEPT |  N_EMPLOYEES_SUPERVISED  |
--------+-----------+--------+--------------------------+
| 7006  |   BLAKE   | 10     | 1                        |
| 7006  |   BLAKE   | 20     | 2                        |
| 7006  |   BLAKE   | 30     | 0                        |
| 7007  |   FORD    | 10     | 2                        |
| 7007  |   FORD    | 20     | 0                        |
| 7007  |   FORD    | 30     | 0                        |
| 7008  |   KING    | 10     | 1                        |
| 7008  |   KING    | 20     | 1                        |
| 7008  |   KING    | 30     | 0                        |
--------+-----------+--------+--------------------------+

但是，我无法输出计数为 0 的行。

标签： sqloraclecountleft-joininner-join

解决方案

您可以cross join将员工表与部门列表一起使用，然后将原始表与left join. 要将结果限制为经理员工，我们可以使用exists：

select m.idemp, m.empname, d.iddept, 
    count(e.idemp) as n_employees_supervised
from emp m
cross join dept d 
left join emp e on e.mgr = m.idemp and e.iddept = d.iddept
where exists (select 1 from emp e1 where e1.mgr = m.idemp)
group by m.idemp, m.empname, d.iddept

避免子查询的另一种方法exists是条件聚合：

select m.idemp, m.empname, d.iddept, 
    sum(case when e.iddept = d.iddept then 1 else 0 end) as n_employees_supervised
from emp m
cross join dept d 
inner join emp e on e.mgr = m.idemp
group by m.idemp, m.empname, d.iddept

推荐阅读