SELECT * FROM emp_skills AS A WHERE EXISTS ( SELECT * FROM skills C WHERE EXISTS ( SELECT * FROM emp_skills AS B WHERE (A.emp = B.emp) AND (B.skill = C.skill) ));
效果是,找出至少會一個skills 表上技能的員工。 因此靚儀會被去除。
Hint: 把每個人會的技能組當成一個集合。
情況二
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
SELECT * FROM emp_skills AS A WHERE NOTEXISTS ( SELECT * FROM skills C WHERE EXISTS ( SELECT * FROM emp_skills AS B WHERE (A.emp = B.emp) AND (B.skill = C.skill) ));
效果是,找出 skills 表上技能一個都不會的員工。 因此只有靚儀會被選出。
情況三
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
SELECT * FROM emp_skills AS A WHERE EXISTS ( SELECT * FROM skills C WHERE NOTEXISTS ( SELECT * FROM emp_skills AS B WHERE (A.emp = B.emp) AND (B.skill = C.skill) ));
效果是,找出沒有全會skills 表上技能的員工。 因此銘仁、靜儀、勝朋皆會被選出。
情況四
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
SELECT * FROM emp_skills AS A WHERE NOTEXISTS ( SELECT * FROM skills C WHERE NOTEXISTS ( SELECT * FROM emp_skills AS B WHERE (A.emp = B.emp) AND (B.skill = C.skill) ));
SELECT DISTINCT emp FROM emp_skills AS A WHERE NOTEXISTS ( SELECT * FROM skills C WHERE NOTEXISTS ( SELECT * FROM emp_skills AS B WHERE (A.emp = B.emp) AND (B.skill = C.skill) ));