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);
Just replace the corresponding data.
The meaning expressed by that SQL is probably:
注意
Note thatexcept
后面是这个员工掌握的所有skill
except
is followed by all theskills
that this employee has mastered. 🎜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.
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?
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
Remove the skills corresponding to each employee in the EmpSkill table
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 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