生成实体-属性值-结构化数据报表的SQL

Question

我有3张桌子：

Productmaster：

ProductId    PName     PDescription

属性：

AttributeID  attName
----         ----
1            Brand
2            Category
3            Artist

ProductAttributeValues

paId   ProductId  AttributeID AttributeValues

一个product可以有多个attributes。

以下是所需的输出：

ProductId  ProductDesc  Brand    Category    Artist
---        ---          ---      ---         ---
1          sadasd       Brand1   Category1   Artist1
2          sadasds      Brand2   Category3   Artist4

如何获得此输出？

提前谢谢。

Answer 1

对于要引入的每个属性值，您应该使用左连接，例如

select p.ProductId,p.ProductDesc,
a1.AttributeValues as Brand,
a2.AttributeValues as Category, 
a3.AttributeValues as Artist,
from Product p
left join ProductAttributeValues a1 on(p.ProductId=a1.ProductId and a1.AttributeID=1)
left join ProductAttributeValues a2 on(p.ProductId=a2.ProductId and a2.AttributeID=2)
left join ProductAttributeValues a3 on(p.ProductId=a3.ProductId and a3.AttributeID=3)

为了把这个翻译成英语，“one me all products，并为每个产品提供一个品牌、类别和艺术家属性(如果存在)”

我假设产品的每个属性只有一个值或零值。

Answer 2

假设每个属性在每个产品中只出现一次：

SELECT 
  pm.ProductId as ProductId,
  pm.PDescription as ProductDesc,
  pav_Brand.AttributeValues as Brand,
  pav_Category.AttributeValues as Category,
  pav_Artist.AttributeValues as Artist
FROM
  ProductMaster pm
    inner join ProductAttributeValues pav_Brand 
      on pm.productId == pav_Brand.ProductId
    inner join Attributes a_Brand 
      on pav_Brand.AttributeId = a_Brand.AttributeId
        AND a_Brand.attName = 'Brand'
    inner join ProductAttributeValues pav_Category 
      on pm.productId == pav_Category.ProductId
    inner join Attributes a_Category 
      on pav_Category.AttributeId = a_Category.AttributeId
        AND a_Brand.attName = 'Category'
    inner join ProductAttributeValues pav_Artist 
      on pm.productId == pav_Artist.ProductId
    inner join Attributes a_Artist 
      on pav_Category.AttributeId = a_Artist.AttributeId
        AND a_Brand.attName = 'Artist'

如果数据并不总是可用，则可以使用左外部联接。

Answer 3

尝试此查询，可能需要一些调整

Select pm.ProductId, pm.PDescription, pav1.AttributeValues, pav2.AttributeValues, pav3.AttributeValues 
from Productmaster pm, ProductAttributeValues pav1, ProductAttributeValues pav2, ProductAttributeValues pav3 
where pm.ProductId = pav1.ProductId 
and pav1.AttributeID = 1 
and pm.ProductId = pav2.ProductId 
and pav2.AttributeID = 2 
and pm.ProductId = pav3.ProductId 
and pav3.AttributeID = 3