MySQL中实现行列转换的操作示例_Mysql

在 mysql 中进行行列转换（即，将某些列转换为行或将某些行转换为列）通常涉及使用条件逻辑和聚合函数。虽然 mysql 没有像 oracle/sql server 中的 pivot 和 unpivot 那样的直接功能，但你可以通过结合 case 语句、union 或 union all、以及 group by 等来实现这些转换。

1、行转列的操作

在 mysql 中，并没有内置的 pivot 函数，如 oracle/sql server 中那样。但是，你可以使用条件聚合或 case 语句来模拟 pivot 操作。

以下是一个简单的示例，说明如何在 mysql 中模拟 pivot。

假设你有一个名为 t_sales 的表，它记录了销售数据，结构如下：

create table t_sales (  
    id int primary key auto_increment,
    col_year int,  
    product varchar(255),  
    amount decimal(10, 2)  
);  
  
insert into t_sales (col_year, product, amount) values  
(2020, 'a001', 100),  
(2020, 'b001', 120),  
(2021, 'a001', 150),  
(2021, 'b001', 150),
(2022, 'a001', 260),  
(2022, 'b001', 240),
(2023, 'b001', 330),
(2024, 'a001', 440);

(root@localhost:mysql.sock)[superdb 10:49:26]>select * from t_sales;
+----+----------+---------+--------+
| id | col_year | product | amount |
+----+----------+---------+--------+
|  1 |     2020 | a001    | 100.00 |
|  2 |     2020 | b001    | 120.00 |
|  3 |     2021 | a001    | 150.00 |
|  4 |     2021 | b001    | 150.00 |
|  5 |     2022 | a001    | 260.00 |
|  6 |     2022 | b001    | 240.00 |
|  7 |     2023 | b001    | 330.00 |
|  8 |     2024 | a001    | 440.00 |
+----+----------+---------+--------+
8 rows in set (0.00 sec)

现在，假设你想要将产品列 (product) 转换为列标题，并为每个年份和产品显示销售额。在 oracle/sql server 中，你可以使用 pivot 来实现这一点。但在 mysql 中，你可以这样做：

select   
    col_year,  
    sum(case when product = 'a001' then amount else 0 end) as 'a_product',  
    sum(case when product = 'b001' then amount else 0 end) as 'b_product'  
from t_sales
group by col_year;

(root@localhost:mysql.sock)[superdb 10:50:29]>select   
    ->     col_year,  
    ->     sum(case when product = 'a001' then amount else 0 end) as 'a_product',  
    ->     sum(case when product = 'b001' then amount else 0 end) as 'b_product'  
    -> from t_sales
    -> group by col_year;
    
-- 这将返回以下结果
+----------+-----------+-----------+
| col_year | a_product | b_product |
+----------+-----------+-----------+
|     2020 |    100.00 |    120.00 |
|     2021 |    150.00 |    150.00 |
|     2022 |    260.00 |    240.00 |
|     2023 |      0.00 |    330.00 |
|     2024 |    440.00 |      0.00 |
+----------+-----------+-----------+
5 rows in set (0.00 sec)

这就是在 mysql 中模拟 pivot 的方法。对于更复杂的转换或更多的产品，你可能需要扩展 case 语句来包含更多的条件。

2、列转行的操作

在 mysql 中，没有直接的 unpivot 操作，因为 unpivot 是 sql server 和 oracle 等数据库系统中的功能，用于将多列转换为多行。但是，你可以使用 mysql 的查询技巧来模拟 unpivot 操作。

假设你有一个类似 pivot 后的结果集，并且你想要将其转换回原始的多行格式，你可以使用 union all 或 union（取决于是否要消除重复行）来模拟 unpivot。

以下是一个示例，说明如何在 mysql 中模拟 unpivot 操作：

假设你有一个 t_pivoted_sales 表，它是通过 pivot（或上述的 mysql 模拟方法）得到的：

create table t_pivoted_sales (  
    id int primary key auto_increment,
    col_year int,  
    a_product decimal(18, 2),  
    b_product decimal(18, 2)  
);  
  
insert into t_pivoted_sales (col_year, a_product, b_product) values  
(2020, 100.00, 120.0),  
(2021, 150.00, 150.00),
(2022, 260.00, 240.00),
(2023, 0.00, 330.00),
(2024, 440.00, 0.00);

(root@localhost:mysql.sock)[superdb 11:02:54]>select * from t_pivoted_sales;
+----+----------+-----------+-----------+
| id | col_year | a_product | b_product |
+----+----------+-----------+-----------+
|  1 |     2020 |    100.00 |    120.00 |
|  2 |     2021 |    150.00 |    150.00 |
|  3 |     2022 |    260.00 |    240.00 |
|  4 |     2023 |      0.00 |    330.00 |
|  5 |     2024 |    440.00 |      0.00 |
+----+----------+-----------+-----------+
5 rows in set (0.00 sec)

现在，你想要将 a_product 和 b_product 列转换回多行格式，其中有一个额外的列product来表示产品（a_product 或 b_product）。你可以使用以下查询来模拟 unpivot：

select col_year, 'a_product' as product, a_product as amount from t_pivoted_sales
union all
select col_year, 'b_product' as product, b_product as amount from t_pivoted_sales
order by col_year;

这将返回以下结果