本文介绍了如何进行此eav查询以得出水平结果的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
情况:
表格:
product:
product_id|name |
------------------------
1 |iphone 4 |
2 |gallaxy 2 |
3 |blackbery 6 |
product_attribute:
id|product_id|attribute_id
--------------------------------------------------
1 |1 |2
2 |1 |6
. . .
attribute:
------------------------------
attribute_id|name |value|
1 |width |300
2 |width |320
3 |width |310
4 |height|390
5 |height|370
6 |height|380
应该得到结果:
product_id|height|width
1 |380 |320
......................
修改:高度和宽度属性仅是产品属性的一部分-产品需要像magento一样由用户在后端添加动态功能,因为我选择了eav db设计.如果可能的话,请写查询,以防我们不知道产品具有哪个名称.
height and width attributes its only part of product attributes - product need to have dynamic ability to be added by the user in backend as it done in magento, because that i choose eav db design.Please write queries if it possible in case we dont know which names product have.
谢谢
推荐答案
有几种方法可以实现此目的.对于每个属性值,类似的事情应该可以多次在表上重新加入:
There are several ways to implement this. Something like this should work joining back on the table multiple times for each attribute value:
SELECT p.product_id,
a.value height,
a2.value width
FROM Product p
JOIN Product_Attribute pa ON p.product_id = pa.product_id
JOIN Attribute a ON pa.attribute_id = a.attribute_id AND a.name = 'height'
JOIN Product_Attribute pa2 ON p.product_id = pa2.product_id
JOIN Attribute a2 ON pa2.attribute_id = a2.attribute_id AND a2.name = 'width'
这是小提琴.
这是我个人更喜欢使用MAX和GROUP BY的另一种方法:
Here is an alternative approach using MAX and GROUP BY that I personally prefer:
SELECT p.product_id,
MAX(Case WHEN a.name = 'height' THEN a.value END) height,
MAX(Case WHEN a.name = 'width' THEN a.value END) width
FROM Product p
JOIN Product_Attribute pa ON p.product_id = pa.product_id
JOIN Attribute a ON pa.attribute_id = a.attribute_id
GROUP BY p.product_id
祝你好运.
这篇关于如何进行此eav查询以得出水平结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!