时间戳

首页 > 解决方案 > 转换 MySQL 表

mysql - 转换 MySQL 表

问题描述

我有一个 MySQL 表，其布局如下：

---------------------------------------
| ID | TITLE       | VALUE  | PART NO |
---------------------------------------
| 1  | width (mm)  | 12     |    234  |
---------------------------------------
| 2  | colour      | red    |    234  |
--------------------------------------- 
| 3  | colour      | blue   |    235  |
---------------------------------------
| 4  | width (mm)  | 2      |    234  |
---------------------------------------  
| 5  | Part No.    | 235    |    235  |
---------------------------------------
| 6  | Part No.    | 234    |    234  |
---------------------------------------

我希望表格看起来像这样：

----------------------------------------
| id  | Width (mm) | colour | Part No. |
----------------------------------------
| 1   | 12         | red    |   234    |
----------------------------------------
| 2   | 2          | blue   |   235    |
----------------------------------------

我找到了另一个答案，但后来失去了链接——答案并没有解决我的问题，因为它只适用于数字。

我需要 SQL 代码来解释值中的标题和单词中的 ( 不仅仅是添加数字)。

我使用找到并丢失的答案创建了以下 SQL - 它有点工作，但我缺少大量数据，它返回“制造商”列和第一个“替代零件号”列，但没有别的。（这让我感到困惑 - 表中有 weight 和 Height 100% 并且这两个字段在查询中彼此不接近。）

SQL 代码

SELECT 
    part_number,
    part_number AS pn, 
        if( title = 'Alternative Partnumber', (SELECT table_name.value FROM `table_name` WHERE table_name.part_number = pn AND table_name.title = 'Alternative Partnumber'), '' ) AS `Alternative Partnumber`,
        if( title = 'Amp', (SELECT table_name.value FROM `table_name` WHERE table_name.part_number = pn AND table_name.title = 'Amp'), '' ) AS `Amp`,
        if( title = 'Amp Rating (Constant Torque)', (SELECT table_name.value FROM `table_name` WHERE table_name.part_number = pn AND table_name.title = "Amp Rating (Constant Torque)"), '' ) AS `Amp Rating (Constant Torque)`,
        if( title = 'Analogue Inputs', (SELECT table_name.value FROM `table_name` WHERE table_name.part_number = pn AND table_name.title = "Analogue Inputs"), '' ) AS "Analogue Inputs",
        if( title = 'Analogue output', (SELECT table_name.value FROM `table_name` WHERE table_name.part_number = pn AND table_name.title = "Analogue output"), '' ) AS "Analogue output",
        if( title = 'Manufacturer', (SELECT table_name.value FROM `table_name` WHERE table_name.part_number = pn AND table_name.title = 'Manufacturer'), '' ) AS `Manufacturer`, 
        if( title = 'Weight', (SELECT table_name.value FROM `table_name` WHERE table_name.part_number = pn AND table_name.title = 'Weight'), '' ) AS `Weight`,
        if( title = 'Width', (SELECT table_name.value FROM `table_name` WHERE table_name.part_number = pn AND table_name.title = 'Width'), '' ) AS `Width`,
        if( title = 'Width (mm)', (SELECT table_name.value FROM `table_name` WHERE table_name.part_number = pn AND table_name.title = 'Width (mm)'), '' ) AS `Width (mm)`
FROM 
    table_name 
GROUP BY 
    part_number;

我需要另一个 SQL 来执行 Group By 吗？我认为这就是它丢失数据的地方。

谢谢你，瑞安

标签： mysqlsqlgroup-bypivotentity-attribute-value

解决方案

您可以进行条件聚合以旋转您的 EAV 模型，如下所示：

select 
    min(id) id,
    max(case when title = 'width (mm)' then value end) width_mm,
    max(case when title = 'color' then value end) colour,
    part_number
from mytable
group by part_number

推荐阅读