SQL Получить строки, в которых есть все строки из другой таблицы

Я столкнулся с некоторыми проблемами при получении данных с помощью SQL в одном конкретном сценарии:

Предположим, у меня есть следующие таблицы:

  • A (идентификатор, attr_a, attr_b);
  • B (идентификатор, attr_d, attr_e);
  • C (id_a, id_b);

Как видите, таблица C имеет FK ссылку на идентификатор из таблицы A и ссылку на идентификатор из таблицы C.

Мне нужно получить A строк таблицы, которые для каждой A строки относятся к каждой B строке.

В реальном сценарии, предполагая, что A означает пользователей, B означает привилегии, а C - сущность "многие ко многим", которая связывает пользователей с привилегиями, я бы хотел получить только пользователей, у которых есть ВСЕ привилегии.


postgresql sql relational-division
person Rogger Fernandes    schedule 29.08.2016    source источник
comment
использовать внутреннее соединение для a и b?   -  person Baahubali    schedule 29.08.2016
comment
@ user1490835 a и b не имеют отношения, поэтому я не могу использовать внутреннее соединение, также мне нужны только строки A, у которых есть все идентификаторы B, поэтому соединение не будет работать, оно должно быть более ограниченным   -  person Rogger Fernandes    schedule 29.08.2016
comment
a and b dont have a relationship ... да, через таблицу C.   -  person Tim Biegeleisen    schedule 29.08.2016
comment
@TimBiegeleisen, конечно.   -  person Rogger Fernandes    schedule 29.08.2016

Ответы (5)


arrow_upward
2
arrow_downward 

select A.*
from A
join C on id_a = id
group by id
having count(id) = (select count(*) from B)

Нет необходимости использовать подзапрос, потому что A.id является первичным ключом (или, по крайней мере, уникальным) в качестве столбца, на который ссылается C.id_a.

person klin    schedule 29.08.2016

arrow_upward
1
arrow_downward

Следующий запрос должен вернуть все A записи, где для данного идентификатора они соответствуют каждой записи в таблице B. Обратите внимание, что подзапрос необходим, если вы хотите вернуть каждую полную запись в A.

SELECT t1.*
FROM A t1
INNER JOIN
(
    SELECT A.id
    FROM A
    INNER JOIN C
        ON A.id = C.id_a
    GROUP BY A.id
    HAVING COUNT(*) = (SELECT COUNT(*) FROM B)
) t2
    ON t1.id = t2.id
person Tim Biegeleisen    schedule 29.08.2016

arrow_upward
1
arrow_downward

Предполагая, что ссылочная целостность обеспечивается ограничениями FK, всеми ключевыми столбцами NOT NULL и ограничением UNIQUE или PK для (id_a, id_b) в C.

Если вам нужны только идентификаторы, работайте только с таблицей C. Не тратьте время на присоединение к A:

SELECT id_a
FROM   C
GROUP  BY 1
HAVING count(*) = (SELECT count(*) FROM B);

Если вам нужны столбцы или целые строки из A, присоединитесь к нему после агрегирования и удаления неподходящих строк. Должен быть самым быстрым.

SELECT A.*
FROM  (
   SELECT id_a AS id
   FROM   C
   GROUP  BY 1
   HAVING count(*) = (SELECT count(*) FROM B)
   ) c
JOIN   A USING (id);
person Erwin Brandstetter    schedule 29.08.2016

arrow_upward
0
arrow_downward

Считать не нужно, нужно только проверить наличие (несуществующих) строк:

SELECT *
FROM A
WHERE NOT EXISTS (
        SELECT * FROM B
        WHERE NOT EXISTS (
                SELECT * FROM C
                WHERE C.id_a = A.id AND C.id_b = B.id
                )
        );
person joop    schedule 29.08.2016

arrow_upward
0
arrow_downward

Похоже, вам действительно нужна таблица C, только если вам нужен список пользователей, у которых есть все привилегии. Один из способов сделать это - использовать CONCAT, который рассматривает соединение между C.id_a и C.id_b как одну строку:

SELECT C.id_a
 FROM C
 GROUP BY C.id_a
 HAVING COUNT(DISTINCT CONCAT(C.id_a, C.id_b)) =
 (SELECT COUNT(DISTINCT C.id_b)
  FROM C)

Проверено здесь: http://sqlfiddle.com/#!9/f92a54/3

person kjmerf    schedule 29.08.2016