mysql - 排序/清理 MySQL 数据库

Question

我有一个看起来像这样的 MySQL 数据库，其中没有列中值的系统。将其复制到新数据库并对其进行排序的最简单方法是什么？

+----+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+
| ID |      ID1      |    Value1    |      ID2      |    Value2    |      ID3      |    Value3    |      ID4      |    Value4    |
+----+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+
|  1 | Picture       | picture1.png | Documentation | doc1.pdf     | Height        | 10           | Volume        | 150          |
|  2 | Documentation | doc2.pdf     | Picture       | picture2.png | Volume        | 150          | Height        | 10           |
|  3 | Volume        | 200          | Height        | 20           | Picture       | picture3.png | Documentation | doc3.pdf     |
|  4 | Height        | 25           | Volume        | 250          | Documentation | Doc4.pdf     | Picture       | picture4.png |
+----+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+

我希望它看起来像这样 ID1=图片、ID2=文档等等..？

+----+---------+--------------+---------------+----------------+--------+--------------+--------+--------+
| ID |   ID1   |      Value1  |        ID2    |         Value2 |  ID3   |     Value3   |  ID4   | Value4 |
+----+---------+--------------+---------------+----------------+--------+--------------+--------+--------+
|  1 | Picture | picture1.png | Documentation | doc1.pdf       | Volume |          100 | Height |     10 |
|  2 | Picture | picture2.png | Documentation | doc2.pdf       | Volume |          150 | Height |     15 |
|  3 | Picture | picture3.png | Documentation | doc3.pdf       | Volume |          200 | Height |     20 |
|  4 | Picture | picture4.png | Documentation | doc4.pdf       | Volume |          250 | Height |     25 |
+----+---------+--------------+---------------+----------------+--------+--------------+--------+--------+

Answer 1

对于您提供的数据，这将起作用:

CREATE TABLE Table2
    (`ID` int, 
     `ID1` varchar(13), `Value1` varchar(12), 
     `ID2` varchar(13), `Value2` varchar(12), 
     `ID3` varchar(13), `Value3` varchar(12), 
     `ID4` varchar(13), `Value4` varchar(12));
INSERT INTO Table2
    (SELECT id, 'Picture' as ID1,
     CASE WHEN id1='Picture' THEN value1 WHEN id2='Picture' THEN value2 WHEN id3='Picture' THEN value3 ELSE value4 END AS value1,
    'Documentation' AS ID2,
    CASE WHEN id1='Documentation' THEN value1 WHEN id2='Documentation' THEN value2 WHEN id3='Documentation' THEN value3 ELSE value4 END AS value2,
    'Volume' AS ID3,
    CASE WHEN id1='Volume' THEN value1 WHEN id2='Volume' THEN value2 WHEN id3='Volume' THEN value3 ELSE value4 END AS value3,
    'Height' AS ID4,
    CASE WHEN id1='Height' THEN value1 when id2='Height' THEN value2 when id3='Height' THEN value3 ELSE value4 END AS value4
    FROM Table1);

SELECT * FROM Table2

输出:

ID    ID1      Value1        ID2            Value2    ID3    Value3    ID4    Value4
1     Picture  picture1.png  Documentation  doc1.pdf  Volume 150       Height 10 
2     Picture  picture2.png  Documentation  doc2.pdf  Volume 150       Height 10 
3     Picture  picture3.png  Documentation  doc3.pdf  Volume 200       Height 20 
4     Picture  picture4.png  Documentation  Doc4.pdf  Volume 250       Height 25 

此时您可能应该考虑更改表的结构，因为该表中有很多冗余信息(ID1始终是“图片”，ID2始终是“文档”等)，因此您可以删除这些字段只有名为“图片”、“文档”等的字段。您可以使用以下查询来做到这一点:

CREATE TABLE Table3
    (`ID` int, 
     `Picture` varchar(12), 
     `Documentation` varchar(12), 
     `Volume` varchar(12), 
     `Height` varchar(12));
INSERT INTO Table3
    (SELECT id,
     CASE WHEN id1='Picture' THEN value1 WHEN id2='Picture' THEN value2 WHEN id3='Picture' THEN value3 ELSE value4 END AS Picture,
    CASE WHEN id1='Documentation' THEN value1 WHEN id2='Documentation' THEN value2 WHEN id3='Documentation' THEN value3 ELSE value4 END AS Documentation,
    CASE WHEN id1='Volume' THEN value1 WHEN id2='Volume' THEN value2 WHEN id3='Volume' THEN value3 ELSE value4 END AS Volume,
    CASE WHEN id1='Height' THEN value1 when id2='Height' THEN value2 when id3='Height' THEN value3 ELSE value4 END AS Height
    FROM Table1);

SELECT * FROM Table3

输出:

ID   Picture       Documentation  Volume  Height
1    picture1.png  doc1.pdf       150     10 
2    picture2.png  doc2.pdf       150     10 
3    picture3.png  doc3.pdf       200     20 
4    picture4.png  Doc4.pdf       250     25