mysql - 如何从B表的所有各行中仅选择A的值都只有X的条目？

如何只选择所有实习生的Practice_result.Mark等于5的mentors.l_name，mentors.f_name？目前，我可以使用以下方式为所有“导师”选择所有“实习生”的所有分数：SELECT mentors.l_name, mentors.f_name, interns.l_name, Practice_result.MarkFROM mentorsLEFT OUTER JOIN interns_specialtyON mentors.mentor_id = interns_specialty.mentor_idLEFT OUTER JOIN internsON interns.intern_id = interns_specialty.intern_idLEFT OUTER JOIN Practice_resultON Practice_result.intern_id = interns.intern_id;结果如下：+---------+----------+---------+------+| l_name | f_name | l_name | Mark |+---------+----------+---------+------+| Mentor1 | Mentor1 | Intern1 | 5 || Mentor2 | Mentor2 | Intern2 | 4 || Mentor1 | Mentor1 | Intern3 | 5 || Mentor3 | Mentor3 | Intern4 | NULL || Mentor2 | Mentor2 | Intern5 | 3 || Mentor3 | Mentor3 | Intern6 | 5 || Mentor4 | Mentor4 | Intern7 | 4 || Mentor4 | Mentor4 | Intern8 | 5 |+---------+----------+---------+------+或者，我可以选择所有带有Practice_result.Mark ='5'的ROWS，而不会排除那些也拥有使用Practice_result.Mark '5'实习生的导师。而且我需要排除那些 5的对象，因此在这种情况下只返回Mentor1，因为只有他的“实习生”只有5s。我尝试将ORDER BY与LIMIT 1结合使用，但仍然不知道如何使它适用于较小的一组相同mentor.l_name，但适用于所有实习生。表格：导师：+-----------+----------+---------+| mentor_id | f_name | l_name |+-----------+----------+---------+| 1 | Mentor2 | Mentor2 || 2 | Mentor1 | Mentor1 || 3 | Mentor3 | Mentor3 || 4 | Mentor4 | Mentor4 |+-----------+----------+---------+实习生：+-----------+----------+---------+| intern_id | f_name | l_name |+-----------+----------+---------+| 1 | Name1 | Intern1 || 2 | Name2 | Intern2 || 3 | Name3 | Intern3 || 4 | Name4 | Intern4 || 5 | Name5 | Intern5 || 6 | Name6 | Intern6 || 7 | Name7 | Intern7 || 8 | Name8 | Intern8 || 9 | Name9 | Intern9 || 10 | Name10 | Intern10|| 11 | Name11 | Intern11|+-----------+----------+---------+interns_specialty： +-----------+--------------+-----------+ | intern_id | specialty_id | mentor_id | +-----------+--------------+-----------+ | 1 | 1 | 2 | | 2 | 1 | 1 | | 3 | 4 | 2 | | 4 | 2 | 3 | | 5 | 3 | 1 | | 6 | 3 | 3 | | 7 | 4 | 4 | | 8 | 4 | 4 | +-----------+--------------+-----------+练习结果：+-----------+------+| intern_id | Mark |+-----------+------+| 1 | 5 || 2 | 4 || 3 | 5 || 5 | 3 || 6 | 5 || 7 | 4 || 8 | 5 |+-----------+------+ 最佳答案 对于我来说，在不看到实际表的情况下编写这样的请求有点困难，但是我建议尝试这样的操作：SELECT mentors.l_name, mentors.f_name, interns.l_name, PR1.MarkFROM mentorsLEFT OUTER JOIN interns_specialtyON mentors.mentor_id = interns_specialty.mentor_idLEFT OUTER JOIN internsON interns.intern_id = interns_specialty.intern_idLEFT OUTER JOIN Practice_result as PR1ON PR1.intern_id = interns.intern_idLEFT JOIN Practice_result as PR2ON PR2.intern_id = interns.intern_id and PR2.Mark <> '5'WHEREPR2.intern_id is null;关于mysql - 如何从B表的所有各行中仅选择A的值都只有X的条目？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/49877923/