Выбор против подмножеств списка в MySQL
Я - вполне begginer, и у меня есть две таблицы: "продукт" и "атрибуты продукта".
Вот некоторые мнимые данные (фактический материал включает больше таблиц),
Таблица продуктов:
product_id | product_name
10 | aaa
11 | bbb
12 | ccc
Таблица атрибутов продукта:
attribute_id | product_id
21 | 10
23 | 10
24 | 10
21 | 11
24 | 11
21 | 12
25 | 12
Где каждый продукт имеет больше чем один возможный атрибут. У меня есть список идентификаторов атрибута как (21,10,25) и я должен выбрать все продукты, атрибуты которых являются подмножеством того списка.
Действительно ли возможно сделать это в одном запросе?
Когда я фильтрую для (21,24), желаемый вывод состоит в том, чтобы возвратить только продукт 11 (bbb)
Когда я фильтрую для (21,23,24), желаемый вывод состоит в том, чтобы возвратить продукты 10 и 11.
Когда я фильтрую для (21), желаемый вывод не состоит в том, чтобы возвратить ни один (потому что все продукты имеют по крайней мере еще один атрибут).
7 ответов
Если вы делаете вид, что ваш фильтр находится в таблице:
select *
from product p
where not exists (
select 1
from attributes a
where a.product_id = p.product_id
and not exists(
select 1
from filter f
where f.id_attribute = a.id_attribute))
Если он был в составленном запросе:
select *
from product p
where not exists (
select 1
from attributes a
where a.product_id = p.product_id
and attribute_id not in (<list>))
Это не в верхней части моего голова, значит, могут быть опечатки.
Пока MySQL не поддерживает ИСКЛЮЧАЯ комбинация запросов,
SELECT product_id
FROM attributes
WHERE product_id NOT IN (
SELECT product_id
FROM attributes
WHERE attribute_id NOT IN (21, 23, 24)
)
GROUP BY product_id
UNION
SELECT id
FROM products AS p
LEFT JOIN attributes AS a
ON p.id = a.product_id
WHERE a.product_id IS NULL
Если вы хотите иметь только продукты со всеми заданными атрибутами, добавьте предложение HAVING COUNT (*) = n к первому внешнему запросу, где 'n' - длина список атрибутов.
Предположим, что ваша таблица продуктов называется Product, а столбец ID в этой таблице называется просто Id:
SELECT * from Product p where p.Id IN
(Select id_product from ProductAttributes where id_attribute in (21, 23, 24))
Это должно возвращать только те идентификаторы, в которых все атрибуты для каждого идентификатора полностью содержатся в списке:
select attribute_match.id_product from
(select id_product, count(*) c from attributes
where id_attribute in (21, 10, 25)
group by id_product) attribute_match,
(select id_product, count(*) c_count from attributes
group by id_product) attribute_total
where attribute_match.id_product = attribute_total.id_product
and attribute_match.c = attribute_total.c
select
P.id,
P.name,
count(P.id) as matched_attr_count,
count(PA.a_id) as total_attr_count
from
product_attributes PA
left join product P on P.id = PA.p_id and PA.a_id in (21,23,24)
group by
PA.p_id
having
matched_attr_count = total_attr_count;
позвольте мне публиковать простые воображаемые данные (на самом деле это больше таблиц)
table products
product_id | product_name
10 | aaa
11 | bbb
table product_attribute
attribute_id | product_id <br>
21 | 10
23 | 10
24 | 10
21 | 11
24 | 11
я хочу, чтобы:
- когда я фильтрую для
(21,24), возвращался только продукт 11 (bbb) - , когда я фильтрую для
( 21,23,24)должны быть возвращены оба продукта - , когда я фильтрую для
(21), только чтобы не было возвращено ни одного (потому что ни один продукт не имеет только этот атрибут)
Основываясь на вашем понимании, я оптимизировал его еще больше и использовал только 1 COUNT оператор вроде этого:
SELECT * ,COUNT(p.product_id) AS c FROM product_attribute pa
LEFT JOIN products p ON pa.product_id = p.product_id AND pa.attribute_id NOT IN ($filter_list)
GROUP BY pa.product_id
HAVING c=0
Это работает? :)
Изменить:
Этот код не возвращает название продукта или другие поля, которые у него могут быть. Это правильный:
SELECT * ,COUNT(pa.product_id ) AS c FROM products p
LEFT JOIN product_attribute pa ON pa.product_id = p.product_id AND pa.attribute_id NOT IN ($filter)
GROUP BY p.product_id
HAVING c=0