使用EAV表联接/枢轴项目 [英] Join/Pivot items with EAV table

问题描述

我有一个如下表，其中包含产品说明 <上一页> ╔════╦══════════════╦══════╗ d ID║名称║价格║ ╠════╬══════════════╬══════╣ ║1║苹果║23║ ║2║衬衫║148║ ║3║电脑║101║ ║4║打印机║959║ ╚════牛皮══════════════牛皮══════╝

I have a table like below which would have the product description

╔════╦══════════════╦══════╗
║ Id ║  name        ║ price║
╠════╬══════════════╬══════╣
║  1 ║ Apple        ║ 23   ║
║  2 ║ shirt        ║  148 ║
║  3 ║ computer     ║  101 ║
║  4 ║ printer      ║  959 ║
╚════╩══════════════╩══════╝

 
和另一个表，该表包含ID链接的属性，如
and another table which is holding the attributes like linked by the ID
 
╔════╦══════════════╦══════╗
║ID║attr_name║值║
╠════╬══════════════╬══════╣
║1║颜色║红色║
║1║尺寸║xl║
║1║品牌║App║
║2║颜色║蓝色║
║2║大小║l║
║3║颜色║蓝色║
║3║大小║xxl║
║3║品牌║HP║
╚════牛皮══════════════牛皮══════╝

╔════╦══════════════╦══════╗
║ Id ║  attr_name   ║ Value║
╠════╬══════════════╬══════╣
║  1 ║ color        ║ red  ║
║  1 ║ size         ║  xl  ║
║  1 ║ brand        ║  App ║
║  2 ║ color        ║  blue║
║  2 ║ size         ║  l   ║
║  3 ║ color        ║  blue║
║  3 ║ size         ║  xxl ║
║  3 ║ brand        ║  HP  ║
╚════╩══════════════╩══════╝

 
如果我知道属性名称将仅是颜色大小和品牌，是否有任何可能的方法可以带来如下表格
Is there any possible way to bring a table like below if I know the attribute name is going to be only color size and brand
 
╔════╦══════════╦═══════╦═══════╦══════╦══╗
║ID║名称║颜色║品牌║尺寸║
╠════╬══════════╬═══════╬═══════╬══════╬══╣
║1║苹果║红色║应用║xl║
║2║衬衫║蓝色║l║║
║3║电脑║蓝色║HP║XXL║║
║4║打印机║║║║
╚════牛皮══════════牛皮═══════牛皮═══════牛皮═══════牛皮══════牛皮══╝

╔════╦══════════╦═══════╦═══════╦══════╦══╗
║ id ║   name   ║ color ║ brand ║ size ║  ║
╠════╬══════════╬═══════╬═══════╬══════╬══╣
║  1 ║ apple    ║ red   ║ app   ║ xl   ║  ║
║  2 ║ shirt    ║ blue  ║       ║ l    ║  ║
║  3 ║ computer ║ blue  ║ HP    ║ XXL  ║  ║
║  4 ║ printer  ║       ║       ║      ║  ║
╚════╩══════════╩═══════╩═══════╩══════╩══╝

 
推荐答案
您应该能够使用带有CASE表达式的聚合函数将行转换为列:
You should be able to use an aggregate function with a CASE expression to convert the rows into columns:
select d.id,
  d.name,
  max(case when a.attr_name = 'color' then a.value end) color,
  max(case when a.attr_name = 'brand' then a.value end) brand,
  max(case when a.attr_name = 'size' then a.value end) size
from product_description d
inner join product_attributes a
  on d.id = a.id
group by d.id, d.name;

请参见带演示的SQL提琴.
由于您使用的是Oracle 11g，因此可以使用PIVOT函数获取结果:
Since you are using Oracle 11g, then you can use the PIVOT function to get the result:
select id, name, Color, Brand, "Size"
from
(
  select d.id, d.name,
    a.attr_name, a.value
  from product_description d
  inner join product_attributes a
    on d.id = a.id
) src
pivot
(
  max(value)
  for attr_name in ('color' as Color,
                    'brand' as Brand,
                    'size' as "Size")
) p;

请参见带有演示的SQL提琴 

                        
这篇关于使用EAV表联接/枢轴项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！