gpt4 book ai didi

mySQL:查询一对多表?

转载 作者:行者123 更新时间:2023-11-29 05:43:56 24 4
gpt4 key购买 nike

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

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

但是,如果我需要在同一查询中同时具有 weight = 10 和 color = blue 的产品怎么办?

数据库设计示例:

表:产品

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

表:productProperties

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

最佳答案

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

子查询的另一种选择:

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:查询一对多表?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/4328126/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com