如何从一个列中分组的MySQL表中选择具有所需值的行

I need to filter products where certain attributes are stored in a joined table that match all the required properties, i.e. users need to be able to gradually narrow down their search by adding requirements.

我需要过滤某些属性存储在连接表中且匹配所有必需属性的产品，即用户需要能够通过添加需求逐步缩小搜索范围。

The problem really just concerns the properties table I think, rather than the join, given the following (simplified) table of product properties:

考虑到以下（简化）产品属性表，问题实际上只涉及我认为的属性表，而不是连接：

id  product_id  property  value
---------------------------------
1   1           color     red
2   1           size      small
3   2           color     red
4   2           size      large

how would I get all the product_ids where value is both 'red' and 'small' ?

如何获得所有product_ids，其中值为“红色”和“小”？

A similar question was asked before but not answered very fully. A solution involves a COUNT and HAVING to get the rows where there are as many rows in each group as required values e.g.

之前曾问过类似的问题，但未得到充分回答。解决方案涉及COUNT和HAVING以获得每组中具有尽可能多的行的行，例如所需的值。

SELECT product_id, count(*) AS group_count FROM properties where
value = 'red' OR value = 'small'
GROUP BY product_id
HAVING group_count = 2

This works but I'm concerned about performance, it seems like there would be a better way.

这有效，但我关注性能，似乎会有更好的方法。

Eventually this would need to be joined with, or at least used to filter the products table:

最终，这需要与产品表连接，或者至少用于过滤产品表：

id  name     
-------------
1   Product 1
2   Product 2

I forgot to mention that I have 2 of these properties tables joined to products that I need to filter on, one with regular attributes of a product, another with available configurable options (a bit like variants). The scenario is to allow users to filter products like: "show products where gender = 'male', brand = 'nike' and size == 'small'" where gender and brand are 'properties' and size is in options (configurable when adding to cart)

我忘了提到我有两个属性表连接到我需要过滤的产品，一个具有产品的常规属性，另一个具有可用的可配置选项（有点像变体）。该方案是允许用户过滤以下产品：“显示性别='男性'，品牌='耐克'和尺寸=='小'的产品，其中性别和品牌是'属性'，尺寸在选项中（可配置时）加入购物车）

The solution of using a group with a count works with the 2 joined tables still but it gets messy, the required group count is the number of required options on the first table multiplied by the number on the second.

使用带有计数的组的解决方案仍然可以处理2个连接的表，但它会变得混乱，所需的组计数是第一个表上所需选项的数量乘以第二个表上的数字。

I could just fetch the ids from properties (and the other table) then just do a select where id IN(ids), matching a set of ids for both property tables, I don't like the idea of doing this with a really long list of ids though.

我可以从属性（和另一个表）中获取id然后只做一个选择id IN（ids），匹配两个属性表的一组id，我不喜欢这样做的想法很长尽管如此。

4 个解决方案

#1

Not sure this is faster, but joins from subqueries generated from your filter criteria would work:

不确定这是否更快，但是根据过滤条件生成的子查询的连接可以正常工作：

Select p.name, p.id from product p, 
(select product_id from properties where value='red') colors,
(select product_id from properties where value='small') sizes
where p.id=colors.product_id and p.id=sizes.product_id

#2

SELECT DISTINCT p1.product_id, pn.name 
FROM properties p1, properties p2,
     productNames pn
WHERE p1.product_id = p2.product_id
AND p1.property = 'size' and value = 'small'
AND p2.property = 'color' and value = 'red'
AND pn.id = p1.product_id

#3

You could join the table to itself:

您可以将表连接到自己：

SELECT
prop1.product_id
FROM
properties prop1
JOIN properties prop2
    ON prop1.product_id = prop2.product_id
WHERE
prop1.property = 'color' and prop1.value = 'red'
and prop2.property = 'size' and prop2.value = 'small'

#4

Yet another encounter with one of the pitfalls of the attribute-value data model.

又一次遇到了属性 - 值数据模型的一个缺陷。

Assuming that you want products where the "color" matches "red" and the "size" matches "small" (you don't say in your question that the property actually matters, just the value), a big part of the question is, how are you representing the list of required matches? Will they be passed as a delimited string, stored in a temporary table, the SQL built dynamically, something else?

假设你想要的产品“颜色”匹配“红色”而“尺寸”匹配“小”（你在问题中没有说财产真正重要，只是价值），问题的很大一部分是，您如何表示所需的匹配列表？它们是作为分隔字符串传递的，存储在临时表中，动态构建的SQL还有其他什么？

If you can get them into a table (temporary or otherwise) then the following queries should work. Because of the subqueries, performance is going to be very dependent on the amount of data that you are working with and how it is indexed. Also, if you end up with duplicate properties in your table for the same product then it could throw things off, so you may need to account for that.

如果您可以将它们放入表中（临时或其他），则以下查询应该有效。由于子查询，性能将非常依赖于您正在使用的数据量以及索引的索引方式。此外，如果您在同一产品的表中最终得到重复的属性，那么它可能会抛弃，所以您可能需要考虑到这一点。

SELECT
    P.*
FROM
    Products P
WHERE
    NOT EXISTS
    (
        SELECT
            *
        FROM
            Product_Search_Template PST
        LEFT OUTER JOIN Properties P2 ON
            P2.property = PST.property AND
            P2.value = PST.value AND
            P2.product_id = P.product_id
        WHERE
            P2.id IS NULL
    )

。

SELECT
    P.*
FROM
(
    SELECT
        PROP1.product_id,
        COUNT(*) AS match_count
    FROM
        Properties PROP1
    INNER JOIN Product_Search_Template PST ON
        PST.property = PROP1.property AND
        PST.value = PROP1.value
    GROUP BY
        PROP1.product_id
) SQ
INNER JOIN Products P ON
    P.product_id = SQ.product_id
WHERE
    SQ.match_count = (SELECT COUNT(*) FROM Product_Search_Template)

#1