中不工作
这个问题摘自《基础数据库系统第6版》(Elmasri,Navathe)第5章查询3b。作者回答这个问题的一种方式是使用存在量词:
SELECT Lname, Fname
FROM EMPLOYEE
WHERE NOT EXISTS ( SELECT *
FROM WORKS_ON B
WHERE ( B.Pno IN ( SELECT Pnumber
FROM PROJECT
WHERE Dnum=5 )
AND
NOT EXISTS ( SELECT *
FROM WORKS_ON C
WHERE C.Essn=Ssn
AND C.Pno=B.Pno )));
作者将解决方案解释为:
选择每个员工如下部门5控制的项目中不存在该员工不参与的项目
我无法理解第二个内部相关子查询如何与其他不相关子查询和外部查询相关联以给出正确的结果。
SELECT fname, lname FROM employee
WHERE NOT EXISTS
(SELECT pnumber FROM project WHERE dnum=5
AND pnumber NOT IN
(SELECT pno FROM works_on WHERE ssn=essn));
请注意Except在mysql
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE NOT EXISTS
(( SELECT PNUMBER
FROM PROJECT
WHERE DNUM = 5)
EXCEPT
( SELECT PNO
FROM WORKS ON
WHERE ESSN = SSN));
我必须修改查询,以便找到所有员工拥有Smith正在从事的所有项目。因此,将代码重写为:
SELECT fname, lname FROM employee
WHERE NOT EXISTS --single EXISTS looking for Emp OUTER QUERY
(SELECT pnumber FROM project WHERE dnum=5 -- ALL Dept 5 Projects
--add next line to exclude project 3 for dept 5 to return 2 employees (pNo 1&2)
--AND EXISTS (SELECT * FROM WORKS_ON WHERE Pno=Pnumber and ESSN='123456789')
AND pnumber NOT IN -- with ea dept proj matching pno assigned to SSN·
(SELECT pno FROM works_on WHERE ssn=essn)
);
添加注释的行返回分配给Smith的所有dept5项目(1,2)的所有员工,如果从项目中删除项目3,则输出相同。因此,混淆是NOT EXISTS,它表示返回所有记录,对于它不是真的,没有匹配部门中的每个项目
请尝试以下代码:
SELECT fname,
lname
FROM employee
WHERE EXISTS(SELECT pnumber
FROM project
WHERE dnum = 5
AND pnumber IN (SELECT pno
FROM works_on
WHERE essn = ssn));
SELECT FNAME,LNAME
FROM EMPLOYEE
WHERE EXISTS ( SELECT *
FROM DEPENDENT
WHERE SSN=ESSN)
AND EXISTS
( SELECT *
FROM DEPARTMENT
WHERE SSN=MGR_SSN);