我有一个名为Employee
的表,其中包含列:
- EMPID INT(主键(
- EMPNAME VARCHAR2(100(
样本数据如下:
empid|empname|
-----|-------|
1|Mary |
2|Bob |
我有另一个带有列的表Employee_Skills
:
- EMPID INT
- 技能变量2(100(
其样本可表示如下:
empid|skill |
-----|------|
1|SKILL |
1|Java |
1|C++ |
1|Python|
2|C++ |
2|Python|
在第二个表中,这两列一起构成主键。并且EMPID
具有指向Employee(EMPID)
的引用约束。
我想让所有的员工都具备玛丽的所有技能。
例如,
select t2.skill
from Employee t1
join Employee_Skills t2 on (t1.EMPID = t2.EMPID)
where t1.EMPNAME = 'Mary'
此查询返回以下行:
SKILL
------------
Java
C++
Python
... (a few more)
现在,我想选择所有具备上述技能的员工(可以拥有更多技能,但至少应该拥有Mary拥有的技能(。
我做了几次尝试,但没有什么能满足我的需要。
有一次尝试没有成功:
select t1.EMPID, t1.EMPNAME
from Employees t1
join Employee_Skills t2 on (t1.EMPID = t2.EMPID)
where t2.SKILL = ALL (select t4.skill
from Employee t3
join Employee_Skills t4 on (t3.EMPID = t4.EMPID)
where t3.EMPNAME = 'Mary');
您可以通过联接和聚合来解决这个关系划分问题:
select e2.empid
from employee e1
inner join employee_skills es1 on es1.empid = e1.empid
inner join employee_skills es2 on es2.skill = es1.skill and es2.empid <> es1.empid
inner join employee e2 on e2.empid = es2.empid
where e1.empname = 'Mary'
group by e2.empid
having count(*) = (
select count(*)
from employee e3
inner join employee_skills es3 on es3.empid = e3.empid
where e3.empname = 'Mary'
)
有很多方法可以解决这个问题,其中最直观的方法可能是:
select emp.empid, emp.empname
from Employee_Skills esk
inner join employee emp
on emp.empid = esk.empid
where emp.empname <> 'Mary'
and exists (select null
from employee_skills esk_
inner join employee emp_
on emp_.empid = esk_.empid
where emp_.empname = 'Mary'
and esk_.skill = esk.skill);
附言:我把玛丽排除在返回的名单之外。
我重新创建了您的数据如下:
create table employee(EMPID INT , EMPNAME VARCHAR(100));
create table Employee_Skills(EMPID INT, SKILL VARCHAR(100));
insert into employee values (1, 'Mary');
insert into employee values (2, 'Bob');
insert into employee_skills values (2, 'SKILL');
insert into employee_skills values (2, 'Java');
insert into employee_skills values (1, 'C++');
insert into employee_skills values (1, 'Python');
insert into employee_skills values (2, 'C++');
insert into employee_skills values (2, 'Python');
这已经奏效了。为每个人分享。基本上,你需要玛丽的技能。对当前员工的技能做一分钟评估。如果您得到一个空白,则在结果中显示该员工。
Select t4.empid, t4.empname from employee t4
Where not exists
(Select t1.skill from employee_skills t1 join employee t2 on (t1.empid = t2.empid) where t2.empname = 'Mary'
Minus
Select t3.skill from employee_skills t3 where t3.empid = t4.empid)
and t4.empname <> 'Mary';