搜索具有多个条件的产品 [英] Search for products with multiple criteria

问题描述

我在获取一些数据时遇到问题.
我拥有的表格(用于测试目的):

产品:

<前>product_id |产品名称-----------------------------1 |产品 12 |产品23 |产品 3

属性:

<前>product_id |属性_id |属性值-------------------------------------------------------1 |1 |知识点1 |2 |ipsum2 |1 |知识点2 |2 |多勒3 |1 |坐3 |2 |ipsum

我想找到 lorem 存储在 attribute_id 1 和 ipsum 存储在 attribute_id 2 中的产品.

如果我使用这个查询，

<前>选择attributes.attribute_id,attributes.attribute_value,products.product_id从产品内部连接属性 ON products.product_id = attributes.product_id在哪里(attributes.attribute_id = 1 AND attributes.attribute_value = 'lorem')或者(attributes.attribute_id = 2 AND attributes.attribute_value = 'ipsum')

我明白了:

<前>属性_id |属性值|产品编号----------------------------------------------------------1 |知识 |12 |ipsum |11 |知识 |22 |知识 |3

但我真的很想得到这个结果:

<前>attribute_id_1 |attribute_id_2 |属性值_1 |属性值_2 |产品编号----------------------------------------------------------1 |2 |知识 |ipsum |1

或者只是 product_id 1 作为结果.

我可以让它与下一个查询一起工作，但在生产中(有大量数据和更多连接)这很慢.

<前>选择products.product_id从产品内部连接属性 ON products.product_id = attributes.product_id在哪里attributes.attribute_value = 'ipsum'和products.product_id IN (选择products.product_id从产品内部连接属性 ON products.product_id = attributes.product_id在哪里attributes.attribute_value = 'lorem')

我希望你明白我在做什么.

有人可以帮我吗?
谢谢！

解决方案

您能否采用第一个(损坏的)查询，按 product_id 分组，然后添加一个 HAVING COUNT(*) =2 子句?

I'm having a problem with fetching some data.
The tables I have (for testing purposes):

Products:

product_id      | product_name
-----------------------------
1               | product 1
2               | product 2
3               | product 3

Attributes:

product_id      | attribute_id      | attribute_value
-------------------------------------------------------
1               | 1                 | lorem
1               | 2                 | ipsum
2               | 1                 | lorem
2               | 2                 | doler
3               | 1                 | sit
3               | 2                 | ipsum

I want to find the products that have lorem stored in attribute_id 1 AND ipsum stored in attribute_id 2.

If I use this query,

SELECT
   attributes.attribute_id,
   attributes.attribute_value,
   products.product_id
FROM
   products
   Inner Join attributes ON products.product_id = attributes.product_id
WHERE
   (attributes.attribute_id = 1 AND attributes.attribute_value = 'lorem')
OR
   (attributes.attribute_id = 2 AND attributes.attribute_value = 'ipsum')

I get:

attribute_id         | attribute_value     | product_id
----------------------------------------------------------
1                    | lorem               | 1
2                    | ipsum               | 1
1                    | lorem               | 2
2                    | lorem               | 3

But I really want to get this result:

attribute_id_1 | attribute_id_2 | attribute_value_1 | attribute_value_2 | product_id
----------------------------------------------------------
1              | 2              | lorem             | ipsum             | 1

Or just product_id 1 as a result.

I can get it working with the next query, but in production (with a lot of data and a few more joins) this is WAY to slow.

SELECT
   products.product_id
FROM
   products
   Inner Join attributes ON products.product_id = attributes.product_id
WHERE
   attributes.attribute_value = 'ipsum'
AND
   products.product_id IN (
      SELECT
         products.product_id
      FROM
         products
         Inner Join attributes ON products.product_id = attributes.product_id
      WHERE
         attributes.attribute_value =  'lorem'
)

I hope you understand what I'm trying to do.

Can someone please help me?
Thanks!

解决方案

Could you take the first (broken) query, group by product_id and then add a HAVING COUNT(*) = 2 clause?

这篇关于搜索具有多个条件的产品的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！