mysql - 一个sql查询的题目，望路过大神看下

Question

1.上图两个表，要求找出掌握Skills中全部3个技能的员工，即神崎和相田，要求用mysql的语句实现

2.刚学sql，不是很会，想了好久

3.下面是书里面用sql server写的，我也不是很理解，另外mysql中没有except

SELECT DISTINCT emp
  FROM EmpSkills  ES1
 WHERE NOT EXISTS
        (SELECT skill
           FROM Skills
         EXCEPT
         SELECT skill
           FROM EmpSkills ES2
          WHERE ES1.emp = ES2.emp);

Answer 1

SELECT DISTINCT emp FROM EmpSkills s WHERE NOT exists(SELECT skill FROM skills WHERE skill NOT IN
  (SELECT skill FROM EmpSkills Es2 WHERE Es2.emp = s.emp));

Just replace the corresponding data.

Answer 2

The meaning expressed by that SQL is probably:

If the skills an employee has mastered are removed from the Skills list and the list becomes empty, then select this employee. skill，从Skills列表里刨除后列表为空了，那么选出这个员工。

注意except后面是这个员工掌握的所有skill

Note that except is followed by all the skills that this employee has mastered. 🎜

Answer 3

1. Whether it is SQL books or online information, people like to write long and complex SQL statements. This is actually wrong. Because SQL statements that are too long and too complex are not conducive to structural design, code writing, debugging, maintenance, project handover, etc.

2. The correct idea should be to ensure that each statement is as simple as possible and as short as possible. Use more variables, table variables, and temporary tables to reduce complexity, and use more functions, stored procedures, and views to increase reusability and reduce coupling and code redundancy.

Answer 4

Personal question:
Is the title description inaccurate?
It is required to find employees who can master 3 skills in Skills. If there is an employee who not only masters these 3 skills but also masters other skills
Such employees will be screened out by the sql statements in the book
Or should the question be modified to master and Employees who only master all the skills in the skills table?

Answer 5

The core of the problem is, how to express in SQL that all members of set A are in set B.
Using the logic of negation of negation: if there is no member of set A, it is not in set B

Set A: all records of the Skills table
Set B: EmpSkills table is the record corresponding to each employee

The process of logical transformation of SQL:
1. Find all employees of EmpSkill

1. Remove the skills corresponding to each employee in the EmpSkill table

2. According to the "negation of negation" condition mentioned above, determine whether the employee has all the skills in the Skills table

---

Another idea is to first construct a matrix of employees and skills, and then check whether it is in the EmpSkill table. If it exists, it means that the skills are incomplete. As an exclusion condition, the SQL is probably like this:

select distinct emp
from EmpSkills
where emp not in (
    select distinct t1.emp 
    from (select distinct emp
    from EmpSkills) t1 inner join Skills t2
    where not exists (select 1 from EmpSkills t3 where t3.emp = t1.emp and t2.skill)
)

Answer 6

SELECT a.emp,SUM((CASE WHEN a.skill='Oracle' THEN 1 ELSE 0 END) + (CASE WHEN a.skill='UNIX' THEN 1 ELSE 0 END) + (CASE WHEN a.skill='Java' THEN 1 ELSE 0 END)) AS count FROM empskills a JOIN skills b ON a.skill = b.skill GROUP BY a.emp HAVING count = 3