Составить SQL-запрос для получения строк, значения которых идентичны переданной в запрос совокупности значений

Question

Заголовок вопроса, возможно, не совсем точный и правильный, но у меня не получилось его сформулировать нормально.

Есть таблица из двух столбцов (например, заказы (orders) и типы товаров (products)):

Orders	Products
o1	p1
o1	p2
o2	p1
o2	p2
o2	p3
o2	p4
o3	p1
o3	p2
o3	p3

Заказы могут содержать разные типы товаров, которые, в свою очередь, могут присутствовать в разных заказах.

Нужно написать запрос, возвращающий только те заказы, типы товаров которых перечислены в запросе (в виде кортежа, например). Но есть пара условий, как в следующем примере.

Если я передаю в запрос значения (p1, p2, p3), то мне должны вернуться строки только с заказом o3, потому что заказ o1 не сожержит товар типа p3, а в заказе o2 есть лишний товар типа p4. То есть результат - три строки с заказом o3. Либо можно не возвращать строки с товарами, а просто вернуть значение o3.

Вопрос: как написать такой запрос, чтобы он был максимально оптимальным?

Answer 1

SELECT orders
FROM table
WHERE products IN ('p1', 'p2', 'p3')
GROUP BY orders
HAVING COUNT(DISTINCT products) = 3;

Если (orders, products) определено как UNIQUE, то DISTINCT можно убрать за ненадобностью.

Если нужны отдельные записи - использовать это как CTE либо подзапрос для отбора из второй копии таблицы.

Или можно воспользоваться оконной функцией:

WITH cte AS ( SELECT *, COUNT(products) OVER (PARTITION BY orders) cnt
              FROM table
              WHERE products IN ('p1', 'p2', 'p3') )
SELECT *
FROM cte
WHERE cnt = 3;

Это работает только когда (orders, products) определено как UNIQUE. ЕМНИП оконный COUNT(DISTINCT) не поддерживается.

Ваш запрос отфильтрует столбец products по наличию p1, p2 и p3 и группировку применит к результату фильтрации. Под условие в having попадут o2 и o3. Условие задачи нарушается.

Как-то я просмотрел, что нужно не только наличие из списка, но и отсутствие не из списка. Тогда

SELECT orders
FROM table
GROUP BY orders
HAVING COUNT(DISTINCT products) = 3
   AND NOT SUM(products NOT IN ('p1', 'p2', 'p3'));

Answer 2

Вроде бы нашел вариант, как можно решить поставленную задачу. Сначала нужно посчитать количество продуктов для всех заказов и оставить только те заказы, в которых находится интересующее количество продуктов. Затем фильтруем результат по наличию нужных продуктов в заказе.

WITH h_table AS (
    SELECT DISTINCT 
        orders
    FROM table
    GROUP BY orders
    HAVING COUNT(products) = 3
)
SELECT DISTINCT * 
FROM h_table
JOIN table ON table.orders = h_table.orders
WHERE products IN ('p1', 'p2', 'p3')

БЛОГ НА HUSL

Составить SQL-запрос для получения строк, значения которых идентичны переданной в запрос совокупности значений

Ответы (2 шт):