MySQL实现数据一行转多行（mysql 一行转多行）

MySQL实现数据一行转多行

MySQL是一种广泛使用的关系型数据库管理系统，其强大的数据操作能力在企业和个人实践中得到了广泛的应用。我们常常遇到的数据存储格式往往是一行包含多个字段的情况，如下所示：

id  name    score1  score2  score3
1   Tom     80      76      90
2   Jack    78      85      87
3   Alice   90      92      88

现在，我们需要将这种数据格式转换成多行包含单个字段的格式，如下所示：

id  name    score
1   Tom     80
1   Tom     76
1   Tom     90
2   Jack    78
2   Jack    85
2   Jack    87
3   Alice   90
3   Alice   92
3   Alice   88

在MySQL中实现这一操作可以通过以下几个步骤完成：

1. 创建一个包含数据的表

在这里，我们创建一个名为`score`的表，包含`id`、`name`、`score1`、`score2`、`score3`五个字段，用于存储上述数据格式。

CREATE TABLE score (
    id INT PRIMARY KEY,
    name VARCHAR(10),
    score1 INT,
    score2 INT,
    score3 INT
);
INSERT INTO score VALUES (1,'Tom',80,76,90);
INSERT INTO score VALUES (2,'Jack',78,85,87);
INSERT INTO score VALUES (3,'Alice',90,92,88);

2. 使用`UNION ALL`多次查询实现数据转换

在创建好包含数据的表之后，我们可以使用`UNION ALL`语句将数据一行转多行。具体实现方式如下：

SELECT id, name, score1 AS score FROM score
UNION ALL
SELECT id, name, score2 AS score FROM score
UNION ALL
SELECT id, name, score3 AS score FROM score
ORDER BY id ASC;

其中，我们分别查询包含`score1`、`score2`、`score3`的三个子表，并使用`UNION ALL`合并三个子表的查询结果，在`ORDER BY`语句中依据`id`进行排序。

查询结果如下：

id  name    score
1   Tom     80
1   Tom     76
1   Tom     90
2   Jack    78
2   Jack    85
2   Jack    87
3   Alice   90
3   Alice   92
3   Alice   88

3. 优化查询语句

对于含有大量数据的表，在使用`UNION ALL`语句进行多次查询时，可能会对数据库造成较大的压力。因此，我们可以尝试优化查询语句，减少查询次数。具体实现方式如下：

SELECT id, name, score FROM (
    SELECT id, name, score1 AS score FROM score
    UNION ALL
    SELECT id, name, score2 AS score FROM score
    UNION ALL
    SELECT id, name, score3 AS score FROM score
) AS sub
ORDER BY id ASC;

在查询语句中，我们将`UNION ALL`语句合并至一个子查询中，然后在该子查询的结果上进行排序。

最终查询结果与之前相同，但执行效率更高：

id  name    score
1   Tom     80
1   Tom     76
1   Tom     90
2   Jack    78
2   Jack    85
2   Jack    87
3   Alice   90
3   Alice   92
3   Alice   88

综上所述，MySQL实现数据一行转多行可以通过使用`UNION ALL`语句对多个子查询结果进行合并，或将多次查询合并至一个子查询中实现。这种技术应用于数据转换等多种场景，可以为开发人员提供高效的数据处理方式，实现数据库操作的灵活性和可拓展性。