我有以下表格

表:产品

+------------+--------------------+
| product_id | name               |
+------------+--------------------+
| 1          | samsung galaxy s8  |
| 2          | apple iphone 7     |
+------------+--------------------+

表:属性

+--------------+--------------------+
| attribute_id | name               |
+--------------+--------------------+
| 1            | brand              |
| 2            | color              |
+--------------+--------------------+

表:attribute_values

+--------------------+--------------+------------+---------------------+
| attribute_value_id | attribute_id | product_id | value               |
+--------------------+--------------+------------+---------------------+
| 1                  | 1            | 1          | samsung             |
| 2                  | 2            | 1          | blue                |
| 3                  | 1            | 2          | apple               |
| 4                  | 2            | 2          | red                 |
+--------------------+--------------+------------+---------------------+

我有以下查询:

查询 1(有效!)

    SELECT
                p.product_id                AS product_id,
                p.name                      AS product_name,
                v.value                     AS attribute_value,
                a.attribute_id              AS attribute_id,
                a.name                      AS attribute_name,
                c.name                      AS attributes_category_name
    FROM
                products p
    LEFT JOIN
                attribute_values v USING (product_id)
    LEFT JOIN
                attributes a USING (attribute_id)
    WHERE
                p.product_id IN (
                    SELECT 
                       p.product_id 
                    FROM 
                       products p 
                    LEFT JOIN 
                      attribute_values v USING (product_id) 
                    LEFT JOIN  
                      attributes a USING (attribute_id) 
                    WHERE  
                      (a.name = 'brand' AND (v.value = 'samsung'))
                )

查询 2(不起作用!)

    SELECT
                p.product_id                AS product_id,
                p.name                      AS product_name,
                v.value                     AS attribute_value,
                a.attribute_id              AS attribute_id,
                a.name                      AS attribute_name,
                c.name                      AS attributes_category_name
    FROM
                products p
    LEFT JOIN
                attribute_values v USING (product_id)
    LEFT JOIN
                attributes a USING (attribute_id)
    WHERE
                p.product_id IN ( 
                    SELECT   
                      p.product_id  
                    FROM   
                      products p  
                    LEFT JOIN   
                      attribute_values v USING (product_id)  
                    LEFT JOIN   
                      attributes a USING (attribute_id)  
                    WHERE   
                      (a.name = 'brand' AND (v.value = 'samsung'))   
                      AND (a.name = 'color' AND (v.value = 'blue'))  
                   )

如您所见，这两个查询之间的区别在于 WHERE 子句。

Query 1:
--------
p.product_id IN (
    SELECT  
        p.product_id 
    FROM  
        products p 
    LEFT JOIN  
        attribute_values v USING (product_id) 
    LEFT JOIN  
        attributes a USING (attribute_id) 
    WHERE  
        (a.name = 'brand' AND (v.value = 'samsung')) 
   )

Query 2:
--------
p.product_id IN (
    SELECT 
        p.product_id 
    FROM  
        products p 
    LEFT JOIN  
        attribute_values v USING (product_id) 
    LEFT JOIN  
        attributes a USING (attribute_id) 
    WHERE  
        (a.name = 'brand' AND (v.value = 'samsung'))  
        AND (a.name = 'color' AND (v.value = 'blue')) 
   )

我在查询 1 中搜索品牌 > 三星，在查询 2 中搜索品牌 > 三星和颜色 > 蓝色。

有谁知道为什么我的第二个查询不起作用？

最佳答案

在 second 查询中查看您的 WHERE 条件:

 WHERE  
  (a.name = 'brand' AND (v.value = 'samsung'))  
  AND (a.name = 'color' AND (v.value = 'blue'))

这个条件最终类似于:

WHERE a.name = 'brand' AND v.value = 'samsung'
  AND a.name = 'color' AND v.value = 'blue'

对于任何记录，这将永远为TRUE。

关于mysql - 在 MySQL 查询中搜索多个属性(带有工作示例)，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/45190878/