在MySQL中针对列表的子集进行选择 [英] Selecting against subsets of a list in MySQL
问题描述
我是一个初学者,我有两个表:产品"和产品属性".
I'm quite a begginer and I have two tables: "product" and "product attributes".
这里有一些虚构的数据(实际的东西包含更多的表)
Here's some imaginary data (the actual stuff involves more tables )
产品表:
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)
这样的属性ID的列表,我需要选择所有属性是该列表的子集的产品.
Where each product has more than one possible attribute. I have a list of attribute ids like (21,10,25)
and I need to select all products whose attributes are a subset of that list.
是否可以在一个查询中执行此操作?
Is it possible to do this in one query?
当我针对(21,24)进行过滤时,期望的输出是仅返回乘积11(bbb)
When I filter for (21,24) desired output is to return only product 11 (bbb)
当我针对(21,23,24)进行过滤时,期望的输出将返回乘积10和11.
When I filter for (21,23,24) desired output is to return products 10 and 11.
当我对(21)进行过滤时,期望的输出将不返回任何值(因为所有产品都至少具有另一个属性).
When I filter for (21) desired output is to return none (because all products have at least one other attribute).
推荐答案
如果您假装过滤器在表中:
If you pretend that your filter is in a table:
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>))
这不在我的头上,所以可能有错别字.
This is off the top of my head, so may have typos.
这篇关于在MySQL中针对列表的子集进行选择的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!