mysql 实现行列转换

基础概念

MySQL中的行列转换通常是指将数据从一种格式转换为另一种格式，这在数据处理和分析中非常常见。具体来说，行列转换包括以下几种类型：

行转列：将多行数据转换为单列数据。
列转行：将单列数据转换为多行数据。
透视表：将数据从宽表转换为窄表，或者从窄表转换为宽表。

类型与应用场景

行转列

应用场景：当需要将多行数据合并为一列时，例如将多个订单的商品信息合并为一列。

示例：假设有一个订单表 orders，包含订单ID和商品ID：

CREATE TABLE orders (
    order_id INT,
    product_id INT
);

INSERT INTO orders (order_id, product_id) VALUES
(1, 101),
(1, 102),
(2, 103),
(2, 104);

可以使用 GROUP_CONCAT 函数将商品ID合并为一列：

SELECT order_id, GROUP_CONCAT(product_id) AS products
FROM orders
GROUP BY order_id;

列转行

应用场景：当需要将单列数据拆分为多行时，例如将一个包含多个值的字段拆分为多行。

示例：假设有一个商品表 products，包含商品ID和商品类别：

CREATE TABLE products (
    product_id INT,
    categories VARCHAR(255)
);

INSERT INTO products (product_id, categories) VALUES
(101, 'Electronics,Books'),
(102, 'Clothing');

可以使用 SUBSTRING_INDEX 和 FIND_IN_SET 函数将类别拆分为多行：

SELECT product_id, SUBSTRING_INDEX(SUBSTRING_INDEX(categories, ',', numbers.n), ',', -1) AS category
FROM products
JOIN (
    SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
) numbers
WHERE numbers.n <= LENGTH(categories) - LENGTH(REPLACE(categories, ',', '')) + 1;

透视表

应用场景：当需要将数据从宽表转换为窄表，或者从窄表转换为宽表时，例如将销售数据按月份和地区进行汇总。

示例：假设有一个销售表 sales，包含销售ID、日期、地区和销售额：

CREATE TABLE sales (
    sale_id INT,
    sale_date DATE,
    region VARCHAR(255),
    amount DECIMAL(10, 2)
);

INSERT INTO sales (sale_id, sale_date, region, amount) VALUES
(1, '2023-01-01', 'North', 100),
(2, '2023-01-02', 'South', 200),
(3, '2023-02-01', 'North', 150),
(4, '2023-02-02', 'South', 250);

可以使用 CASE 语句和 SUM 函数生成透视表：

SELECT 
    YEAR(sale_date) AS year,
    MONTH(sale_date) AS month,
    region,
    SUM(amount) AS total_amount
FROM sales
GROUP BY YEAR(sale_date), MONTH(sale_date), region;

常见问题及解决方法

问题：行转列时数据重复

原因：在行转列过程中，如果数据中有重复值，可能会导致结果中出现重复数据。

解决方法：使用 DISTINCT 关键字或 GROUP BY 子句来去除重复数据。

SELECT order_id, GROUP_CONCAT(DISTINCT product_id) AS products
FROM orders
GROUP BY order_id;

问题：列转行时数据不全

原因：在列转行过程中，如果数据中的值数量超过了预定义的行数，可能会导致部分数据丢失。

解决方法：动态生成行号，确保能够处理任意数量的值。

SELECT product_id, SUBSTRING_INDEX(SUBSTRING_INDEX(categories, ',', numbers.n), ',', -1) AS category
FROM products
JOIN (
    SELECT @rownum:=@rownum+1 AS n FROM (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) t, (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) t2, (SELECT @rownum:=0) r
) numbers
WHERE numbers.n <= LENGTH(categories) - LENGTH(REPLACE(categories, ',', '')) + 1;