在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屋！