时间戳

首页 > 解决方案 > 查找所有不包含所提供资源的人员

sql - 查找所有不包含所提供资源的人员

问题描述

下面是表格

CREATE TABLE Person(
    PersonID INT PRIMARY KEY,
    FirstName VARCHAR(10),
    LastName VARCHAR(10));

CREATE TABLE Resources(
    ResourceID CHAR(3) PRIMARY KEY
);

CREATE TABLE PR (
    PersonID INT,
    ResourceID CHAR(3),
CONSTRAINT pkpr PRIMARY KEY (PersonID, ResourceID),
CONSTRAINT fkPersonID FOREIGN KEY (PersonID) REFERENCES Person(PersonID),
CONSTRAINT fkResourceID FOREIGN KEY (ResourceID) REFERENCES Resources(ResourceID));


INSERT INTO Person(PersonID, FirstName, LastName) VALUES (1, 'Bill', 'Smith'),(2, 'John','Jones'), (3, 'Tim', 'Jolt');
INSERT INTO Resources (ResourceID) VALUES ('ABC'),('DEF'),('HIJ');
INSERT INTO PR (PersonID, ResourceID) VALUES (1,'ABC'),(1,'DEF'),(2,'ABC'), (2,'HIJ'), (1,'HIJ'), (3, 'DEF');

如何找到所有没有资源的人（'ABC'，'HIJ'）？

使用上面插入的数据，它应该返回人 Tim Jolt

我正在使用 PostgreSql。

标签： sqlpostgresql

解决方案

您可以通过以下 4 种方式编写查询：

  1. 使用NOT IN：Pratik Soni回答的相同
select personid from person 
where personid not in ( select personid from PR where resourceid in ('ABC', 'HIJ'))
  1. 使用不存在：
select personid from person t1 
where  not exists (select 1 from PR where personid=t1.personid and resourceid in ('ABC', 'HIJ'))
  1. 使用<> ALL：
SELECT personid FROM person WHERE
personid <> ALL(select personid from PR where resourceid in ('ABC', 'HIJ'))
  1. 使用LEFT JOIN 和 IS NULL
SELECT p.personid
FROM person p
LEFT JOIN PR r ON p.personid = r.personid AND r.resourceid in ('ABC', 'HIJ')
where r.personid is null

所有 4 种方法都有各自的优缺点。没有人可以在没有看到Explain Analyze结果的情况下预测表现。因此，使用上述所有查询和真实数据检查执行计划，并据此决定应该采用哪种方法。

演示

推荐阅读