SQL -从辅助词中选择第二表中没有的内容

Question

我有一张桌子"person"，一个联想表"person_vaccination“和一个表格”疫苗接种“。

我想找到没有接种疫苗的人，但到目前为止，我只有在有身份证的情况下才能让它工作。

SELECT vac.VACCINATION_Name
FROM VACCINATION vac
WHERE vac.VACCINATION_NUMBER NOT IN
(SELECT v.VACCINATION_NUMBER
FROM  PERSON per
Join PERSON_VACCINATION pv ON per.PERSON_NUMBER = pv.PERSON_NUMBER
JOIN VACCINATION v ON pv.VACCINATION_NUMBER = v.VACCINATION_NUMBER
WHERE per.PERSON_NUMBER = 6)

它很好，但我如何让所有的人错过他们的疫苗接种？(例: 555，疫苗1555，疫苗2666，疫苗1)

Answer 1

SQL Fiddle

Oracle 11g R2架构设置

CREATE TABLE VACCINATION ( VACCINATION_NUMBER, VACCINATION_NAME ) AS
          SELECT 1, 'Vac 1' FROM DUAL
UNION ALL SELECT 2, 'Vac 2' FROM DUAL
UNION ALL SELECT 3, 'Vac 3' FROM DUAL
UNION ALL SELECT 4, 'Vac 4' FROM DUAL;

CREATE TABLE PERSON_VACCINATION ( VACCINATION_NUMBER, PERSON_NUMBER ) AS
          SELECT 1, 1 FROM DUAL
UNION ALL SELECT 2, 1 FROM DUAL
UNION ALL SELECT 3, 1 FROM DUAL
UNION ALL SELECT 4, 1 FROM DUAL
UNION ALL SELECT 1, 2 FROM DUAL
UNION ALL SELECT 2, 2 FROM DUAL
UNION ALL SELECT 3, 2 FROM DUAL;

CREATE TABLE PERSON ( PERSON_NUMBER, PERSON_NAME ) AS
          SELECT 1, 'P1' FROM DUAL
UNION ALL SELECT 2, 'P2' FROM DUAL
UNION ALL SELECT 3, 'P3' FROM DUAL;

查询1

SELECT p.PERSON_NAME,
       v.VACCINATION_NAME
FROM   VACCINATION v
       CROSS JOIN
       PERSON p
WHERE  NOT EXISTS ( SELECT 1
                    FROM   PERSON_VACCINATION pv
                    WHERE  pv.VACCINATION_NUMBER = v.VACCINATION_NUMBER
                    AND    pv.PERSON_NUMBER = p.PERSON_NUMBER )
ORDER BY p.PERSON_NAME,
         p.PERSON_NUMBER,
         v.VACCINATION_NAME,
         v.VACCINATION_NUMBER

结果

| PERSON_NAME | VACCINATION_NAME |
|-------------|------------------|
|          P2 |            Vac 4 |
|          P3 |            Vac 1 |
|          P3 |            Vac 2 |
|          P3 |            Vac 3 |
|          P3 |            Vac 4 |

Answer 2

您应该使用左联接，而不是内部联接。

看看这个链接：left.asp

Answer 3

如果您的目标是根本没有接种疫苗的人，那么您可以在PERSON和PERSON_VACCINATION之间使用左外部连接，然后查找PERSON_VACCINATION列为NULL的所有条目。

SELECT PERSON_NUMBER
FROM PERSON P
LEFT OUTER JOIN
PERSON_VACCINATION PV
ON P.PERSON_NUMBER = PV.PERSON_NUMBER
WHERE PV.PERSON_NUMBER IS NULL

如果您不熟悉左外部联接，则会尝试亲自在PERSON_VACCINATION中为每一行查找匹配行。如果没有匹配的行，则将PERSON行留在结果集中，并显示PERSON_VACCINATION表中所有列的空值。

如果你正在寻找一份人的名单，而他们没有接种疫苗，那么@MT0的答案是正确的。您需要创建一个结果集，其中包含所有可能的人员组合和疫苗接种(交叉连接)，然后检查这些组合中哪些组合实际存在于PERSON_VACCINATION中。任何不存在的条目都是您缺少的疫苗。