sql中无聚合函数的行到列转换

5fjcxozz 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(383)

各位，下面是我的样品表。。
当前表格

PropertyAttributeValueID PropertyTypeID PropertyAttributeName PropertyAttributeValue
1000                     3216           Mileage               20.4
1000                     3216           Engine                DIESEL
1000                     3216           Manufacturer          HONDA
1000                     3216           Seat_Capacity         5
1001                     3216           Mileage               19.2
1001                     3216           Engine                PETROL
1001                     3216           Manufacturer          SUZUKI
1001                     3216           Seat_Capacity         4
1002                     3216           Mileage               18.0
1002                     3216           Engine                DIESEL
1002                     3216           Manufacturer          SUZUKI
1002                     3216           Seat_Capacity         4
1003                     3216           Mileage               16.3
1003                     3216           Engine                PETROL
1003                     3216           Manufacturer          HYUNDAI
1003                     3216           Seat_Capacity         5

我需要从上面的表创建这个表
所需表格

PropertyAttributeValueID Mileage Engine Manufacturer Seat_Capacity
1000                     20.4    DIESEL HONDA        5
1001                     19.2    PETROL SUZUKI       4
1002                     18.0    DIESEL SUZUKI       4
1003                     16.3    PETROL HYUNDAI      5

我想过使用pivot，但正如您所见，不需要聚合函数，如何创建它？

mysql pivot-table entity-attribute-value

来源：https://stackoverflow.com/questions/52829673/row-to-column-transformation-without-aggregate-function-in-sql

1条答案

按热度按时间

fumotvh31#

你可以使用内部连接

select a.PropertyAttributeValueID 
        , a.PropertyAttributeValue  as Mileage
        , b.PropertyAttributeValue  as Engine
        , c.PropertyAttributeValue  as Manufacturer       
        , d.PropertyAttributeValue  as Seat_Capacity          
from my_table a 
inner join my_table b on a.PropertyAttributeValueID = b.PropertyAttributeValueID 
        and a.PropertyAttributeName='Mileage'
            and b.PropertyAttributeName = 'Engine'
inner join my_table c on a.PropertyAttributeValueID = c.PropertyAttributeValueID 
        and c.PropertyAttributeName='Manufacturer'
inner join my_table d on a.PropertyAttributeValueID = d.PropertyAttributeValueID 
        and d.PropertyAttributeName='Seat_Capacity'

为了获得更好的性能，可以在表上添加一个复合索引

create index  my_idx on my_table ( PropertyAttributeName
                , PropertyAttributeValueID 
               , PropertyAttributeValue);

赞(0）回复(0）举报 2021-06-20

我来回答

sql中无聚合函数的行到列转换

1条答案

相关问题

热门标签

最新问答