mysql连接表查询2个值 [英] mysql join table query 2 values
问题描述
我正在尝试在数据库上运行查询以显示具有所选属性的产品
I'm trying to run a query on my database to show products which has the attribute chosen
表1 Products
ID | Product Name
---|-----------------
1 Red Car
2 Blue Car
3 Yellow Car
表2 Attributes
Product ID | Attribute ID
-----------|-----------------
1 3
2 3
3 3
1 4
2 4
例如,我只想显示具有属性3和4设置的产品,它应该只显示红色和蓝色的汽车.但是不是黄色的汽车,因为没有为产品ID 3设置属性集
For example i only want to display products which have attribute 3 and 4 set the it should only show the red and blue car. But not the yellow car as the product as not got a attribute set for product id 3
推荐答案
有很多方法可以解决此问题;最直接的方法可能是使用几个exists
子句,或者将attributes
表连接两次,但是您也可以使用group by
和having
子句来实现相同的结果:
There are many ways to solve this; the most straight-forward would probably be to use a couple of exists
clauses, or join the attributes
table twice, but you could also use group by
and having
clauses to accomplish the same result:
-- option 1: using multiple exists clauses
select p.id, p.productname
from Products p
where exists (select 1 from Attributes a where p.ID = a.ProductID and a.AttributeID = 3)
and exists (select 1 from Attributes a where p.ID = a.ProductID and a.AttributeID = 4);
-- option 2: using multiple joins
select p.id, p.productname
from Products p
join Attributes a3 on p.ID = a3.ProductID
join Attributes a4 on p.ID = a4.ProductID
where a3.AttributeID = 3
and a4.AttributeID = 4;
-- option 3: using aggregate and having
select p.id, p.productname
from Products p
join Attributes a on p.ID = a.ProductID
group by p.id, p.productname
having sum(case when a.AttributeID = 3 then 1 else 0 end) > 0
and sum(case when a.AttributeID = 4 then 1 else 0 end) > 0;
-- option 4: using having and count
select p.id, p.productname
from Products p
join Attributes a on p.ID = a.ProductID
where a.AttributeID in (3,4)
group by p.id, p.productname
having count(distinct a.attributeid) = 2;
哪种方法最适合您,可能取决于您需要什么结果以及对索引进行索引.
Which way is best for you would probably depend on what result you need and indexes et cetera.
这篇关于mysql连接表查询2个值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!