Найти всех лиц, которые не содержат предоставленных ресурсов

#sql #postgresql

Вопрос:

Ниже приведены таблицы

 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’)?

С указанными выше вставленными данными он должен вернуть person Tim Jolt

Я использую PostgreSQL.

1. Не имеет ни одного из значений или не имеет их обоих?

2. Немного плохая выборка данных, она должна охватывать больше возможных комбинаций.

3. @jarlh Возвращенный человек не должен иметь ни одного из этих значений

4. Присоединиться. ГРУППИРУЙТЕ ПО, ИМЕЯ. Или ЗА ИСКЛЮЧЕНИЕМ.

Ответ №1:

Итак, вашим основным источником сущности является таблица Person. И вам нужно игнорировать всех людей, у которых есть данные ресурсы.

Таким образом, SQL будет таким, как показано ниже.

 select PersonID from Person where PersonID not in ( select PersonID from PR where ResourceID in ('ABC', 'DEF'))

1. Я хочу добиться этого без вложенного запроса

2. @akashdeepwadhwa, объясните, почему вы хотите это без подзапроса.

3. @akashdeepwadhwa select person.* from person natural left join pr group by person.personid having count(*) filter (where resurceid in ('ABC', 'DEF')) = 0; Но почему вы думаете, что это будет быстрее?

4. @akashdeepwadhwa Я не думаю, что вы можете сделать это без подзапроса.

5. @AkhileshMishra конечно, вы можете ПРИСОЕДИНИТЬСЯ к запросу вместо того, чтобы использовать его в качестве подзапроса , но это практически то же самое…

Ответ №2:

Вы можете написать свой запрос следующими 4 способами:

Использование НЕ В: то же самое ответил Пратик Сони

 select personid from person 
where personid not in ( select personid from PR where resourceid in ('ABC', 'HIJ'))

Использование NOT EXIST:

 select personid from person t1 
where  not exists (select 1 from PR where personid=t1.personid and resourceid in ('ABC', 'HIJ'))

Используя <> ВСЕ:

 SELECT personid FROM person WHERE
personid <> ALL(select personid from PR where resourceid in ('ABC', 'HIJ'))

Используя ЛЕВОЕ СОЕДИНЕНИЕ и ИМЕЕТ ЗНАЧЕНИЕ 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 результата. Поэтому проверьте план выполнения, используя все вышеперечисленные запросы с реальными данными, и соответственно решите, какой метод вам следует использовать.

ДЕМОНСТРАЦИЯ