MySQL:查询一对多表? [英] mySQL: Querying one-to-many -table?

问题描述

在下面的数据库设计中，采用一对多方法查询具有特定属性的产品的合适方法是什么?

What would be the appropriate way to query products with a specific property in the following database design with a one-to-many approach?

我想我应该做以下事情: SELECT (*) FROM productProperties WHERE property = 'weight' AND value = '10'

I guess that I should be doing something like the following: SELECT (*) FROM productProperties WHERE property = 'weight' AND value = '10'

但是，如果我需要重量都等于10& amp;的产品该怎么办? color =蓝色(在同一查询中?)

数据库设计示例:

表格:产品

------------------------
id    | name     |  price
------------------------
0     | myName   |  100
1     | myName2  |  200

表:productProperties

------------------------------------------------
product  | property     |  Value
------------------------------------------------
0        | weight       |  10
1        | weight       |  20
1        | color        |  blue

推荐答案

如果我需要具有以下特征的产品怎么办 重量都等于10&颜色=蓝色 相同的查询?

What if I need to products that has both weight = 10 & color = blue in the same query?

一个选项:

select product, name
  from products inner join productProperties
    on (products.id = productProperties.product)
 where (property = 'weight' and value = '10')
    or (property = 'color' and value = 'blue')
 group by product, name
having count(1) = 2

带有子查询的另一个选项:

Another option with subqueries:

select id, name
  from products p
 where exists (
         select 1
           from productProperties pp1
          where p.id = pp1.product 
            and pp1.property = 'weight'
            and value = '10'
       )
   and exists (
         select 1
           from productProperties pp2
          where p.id = pp2.product 
            and pp2.property = 'color'
            and value = 'blue'
       )

这篇关于MySQL:查询一对多表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！