select * from employee where depid=(select depid from employee where empid=1001);
select * from employee where depid=(select edepid from employee where empid=1001);
select * from Employee where depid in( select depid from Employee where EMPID='1001')
select * from Employee a where depid in( select depid from Employee where EMPID='1001') and not exist( select * from employee where empid='10001' and depid not in (select depid from employee b where b.empid=a.empid))
select a.* from Employee a,(select * from employee where EmpID = '1001') b where a.depid = b.depid;
sun1976:小弟有一事请教。 你的语句中 and not exist( select * from employee where empid='10001' and depid not in (select depid from employee b where b.empid=a.empid)) 看不太懂,这句话是什么意思?
from employee
where depid=(select edepid
from employee
where empid=1001);
select depid from Employee where EMPID='1001')
select depid from Employee where EMPID='1001')
and not exist( select * from employee where empid='10001' and depid not in (select depid from employee b where b.empid=a.empid))
a.depid = b.depid;
你的语句中
and not exist( select * from employee where empid='10001' and depid not in (select depid from employee b where b.empid=a.empid))
看不太懂,这句话是什么意思?
我的意思是:如果一个员工可以对应多个部门,得到的员工的部门必须是包含在‘1001’员工的部门中的,而且‘1001’员工的所有部门也必须都包含于查询得到的员工的部门中,这样才能保证查询得到的员工和1001有完全相同的部门
另外应该加上员工号<>'1001'