如何进行此eav查询以得出水平结果 [英] How make this eav query to make horizontal result

问题描述

情况:

表格:

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查询以得出水平结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！