mysql - 使用 MySQL 如何更好地选择所有人及其工作，排除从事特定工作的人？

Question

这里是当前有效的 fiddle 的链接，似乎有一种我没有看到的更好的方法来做到这一点:http://sqlfiddle.com/#!2/396f2/6

--- Sample schema
CREATE TABLE `jobs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  `salary` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `people` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `people_jobs` (
  `pid` int(11) NOT NULL DEFAULT '0',
  `jid` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`pid`,`jid`),
  KEY `jid` (`jid`),
  CONSTRAINT `people_jobs_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `people` (`id`),
  CONSTRAINT `people_jobs_ibfk_2` FOREIGN KEY (`jid`) REFERENCES `jobs` (`id`)
);

这是查询

SELECT p.name, j.title FROM people p LEFT JOIN
 people_jobs pj ON pj.pid = p.id LEFT JOIN
 jobs j ON j.id = pj.jid WHERE p.id NOT IN
(SELECT p.id FROM people p INNER JOIN
 people_jobs pj ON pj.pid = p.id INNER JOIN
 jobs j ON j.id = pj.jid WHERE j.title = 'Artist');

有没有办法通过 LEFT JOIN 来做到这一点？这就是我通常寻找表中不存在的东西的方式。但在这种情况下，我正在寻找不存在或与特定工作不匹配的东西。我查了很多资料，但找不到任何基于不在表中或匹配特定条件而排除的示例。

我想找回非艺术家的人员及其相关工作(如果他们没有工作，则为 null)。因此，如果某人有 5 份工作，但其中一项是“艺术家”，我想将该人完全排除在列表之外。

Answer 1

构建您想要的结果集(从 people 到 people_jobs 到 jobs)，然后再次左连接到 people_jobs，其中人员是相同的，但工作始终是“艺术家”。过滤掉生成的非空行，因为这些人拥有艺术家工作。

例如

SELECT p.name, j.title
FROM people p
LEFT JOIN (
    people_jobs pj
    JOIN jobs j ON j.id = pj.jid
) ON pj.pid = p.id
LEFT JOIN people_jobs artists ON artists.pid = p.id AND artists.jid = 5
WHERE artists.pid IS NULL

这假设您提前知道(或可以获取)艺术家的职位 ID，并且它是 5。如果没有，您可以执行另一个嵌套连接或子查询来按名称查找作业。