mysql 动态列转行

基础概念

MySQL中的动态列转行，通常是指将一个表中的多列数据转换为单行的多条记录。这种操作在数据处理和分析中非常常见，尤其是在需要将宽表转换为长格式以便进行进一步分析时。

类型

MySQL中实现动态列转行的方法主要有两种：

使用UNION ALL：适用于列数较少且固定的情况。
使用JSON函数：适用于列数较多或动态变化的情况。

应用场景

数据仓库：在构建数据仓库时，通常需要将业务系统的宽表转换为适合分析的长表。
报表生成：在生成报表时，可能需要将多列数据合并为一行以便展示。
数据分析：在进行数据分析时，长格式的数据更容易进行各种统计和可视化操作。

示例代码

假设我们有一个名为sales的表，结构如下：

CREATE TABLE sales (
    id INT PRIMARY KEY,
    product VARCHAR(50),
    january INT,
    february INT,
    march INT
);

我们可以使用以下SQL语句将january、february和march三列数据转换为长格式：

SELECT id, product, 'january' AS month, january AS amount FROM sales
UNION ALL
SELECT id, product, 'february' AS month, february AS amount FROM sales
UNION ALL
SELECT id, product, 'march' AS month, march AS amount FROM sales;

如果列数较多或动态变化，可以使用JSON函数来实现：

SELECT id, product, JSON_UNQUOTE(JSON_EXTRACT(JSON_OBJECT('january', january, 'february', february, 'march', march), CONCAT('$."', month, '"'))) AS amount
FROM (
    SELECT id, product, 'january' AS month, january AS amount FROM sales
    UNION ALL
    SELECT id, product, 'february' AS month, february AS amount FROM sales
    UNION ALL
    SELECT id, product, 'march' AS month, march AS amount FROM sales
) AS subquery;