MySQL中的行转列和列转行操作，附SQL实战

原创

网络技术联盟站

发布于 2023-06-08 09:34:36

14.4K2

发布于 2023-06-08 09:34:36

MySQL是一款常用的关系型数据库，广泛应用于各种类型的应用程序和数据存储需求。在MySQL中，我们经常需要对表格进行行转列或列转行的操作，以满足不同的分析或报表需求。本文将详细介绍MySQL中的行转列和列转行操作，并提供相应的SQL语句进行操作。

行转列

行转列操作指的是将表格中一行数据转换为多列数据的操作。在MySQL中，可以通过以下两种方式进行行转列操作。

1. PIVOT函数

PIVOT函数是MySQL8.0版本中新增的函数，用于实现行转列操作。其基本语法如下：

SELECT
   aggregated_column,
   [pivot_value_1], [pivot_value_2], ..., [pivot_value_n]
FROM
   (select...) AS source_table
PIVOT (
   aggregate_function(column_for_aggregation)
   FOR column_for_pivot
   IN ([pivot_value_1], [pivot_value_2], ..., [pivot_value_n])
) AS pivot_table;

其中，aggregated_column是基于哪个列进行聚合；pivot_value_x是对哪列进行行转列操作；source_table是原始数据表，pivot_table是转换后的表格。

例如，假设我们有一个订单表，包含订单编号、订单日期和订单金额等字段。如果想要将所有不同日期的订单金额作为列进行展示，可以使用如下SQL语句：

SELECT
    order_id,
    [2010], [2011], [2012], [2013], [2014]
FROM
    (SELECT
         order_id, YEAR(order_date) AS order_year, order_amount
     FROM
         orders) AS source_table
PIVOT (
    SUM(order_amount)
    FOR order_year
    IN ([2010], [2011], [2012], [2013], [2014])
) AS pivot_table;

在上面的例子中，order_year列的值被转换成了新表格的列。SUM(order_amount)部分是对原始数据中相同年份的订单金额进行求和。

2. 自定义SQL语句

除了使用PIVOT函数外，还可以使用自定义的SQL语句实现行转列操作。这种方法需要使用到MySQL的聚合函数和CASE语句。其基本语法如下：

SELECT
    aggregated_column,
    MAX(CASE WHEN column_name=x THEN value ELSE NULL END) AS pivot_value_x,
    MAX(CASE WHEN column_name=y THEN value ELSE NULL END) AS pivot_value_y,
    ...
FROM
    table_name
GROUP BY
    aggregated_column;

代码中的aggregated_column是需要聚合的列，pivot_value_x则是需要转换为列的值。例如，假设我们有一个表格记录每月销售额，字段包括年份、月份和销售额。要将不同月份的销售额作为列展示，可以使用如下SQL语句：

SELECT year, MAX(CASE WHEN month = 'Jan' THEN sales ELSE NULL END) AS Jan,
               MAX(CASE WHEN month = 'Feb' THEN sales ELSE NULL END) AS Feb,
               MAX(CASE WHEN month = 'Mar' THEN sales ELSE NULL END) AS Mar,
               MAX(CASE WHEN month = 'Apr' THEN sales ELSE NULL END) AS Apr,
               MAX(CASE WHEN month = 'May' THEN sales ELSE NULL END) AS May,
               MAX(CASE WHEN month = 'Jun' THEN sales ELSE NULL END) AS Jun,
               MAX(CASE WHEN month = 'Jul' THEN sales ELSE NULL END) AS Jul,
               MAX(CASE WHEN month = 'Aug' THEN sales ELSE NULL END) AS Aug,
               MAX(CASE WHEN month = 'Sep' THEN sales ELSE NULL END) AS Sep,
               MAX(CASE WHEN month = 'Oct' THEN sales ELSE NULL END) AS Oct,
               MAX(CASE WHEN month = 'Nov' THEN sales ELSE NULL END) AS Nov,
               MAX(CASE WHEN month = 'Dec' THEN sales ELSE NULL END) AS Dec
FROM sales_table
GROUP BY year;

在这个例子中，year列的值被保留，而每月的销售额则被转换成新的列。

列转行

列转行操作指的是将表格中多列数据转换为一行数据的操作。在MySQL中，可以通过以下两种方式进行列转行操作。

1. UNPIVOT函数

UNPIVOT函数是MySQL8.0版本中新增的函数，用于实现列转行操作。其基本语法如下：

SELECT
    identifier_column,
    pivot_column,
    value_column
FROM table_name
UNPIVOT (
    value_column for pivot_column IN ([column1], [column2], ..., [columnN])
) AS unpivot_table;

其中，identifier_column是唯一标识每个转换后的行的列，pivot_column是需要将其转换为行的列，value_column是转换后的列的值。例如，假设我们有一个表格记录每个月的销售额，字段包括年份、月份和销售额。如果想要将不同月份的销售额作为多行数据展示，可以使用如下SQL语句：

SELECT
    CONCAT_WS('-', year, month) AS identifier_column, 'sales' AS pivot_column, sales_amount AS value_column
FROM
    sales_table;

在这个例子中，year、month和sales_amount三列被转换成了一行数据。

2. 自定义SQL语句

除了使用UNPIVOT函数外，还可以使用自定义的SQL语句实现列转行操作。这种方法需要使用到MySQL的UNION ALL语句。其基本语法如下：

SELECT
    identifier_column,
    'column1' AS pivot_column,
    column1_value AS value_column
FROM table_name
UNION ALL
SELECT
    identifier_column,
    'column2' AS pivot_column,
    column2_value AS value_column
FROM table_name
UNION ALL
...
SELECT
    identifier_column,
    'columnN' AS pivot_column,
    columnN_value AS value_column
FROM table_name;

以上代码将多个SELECT子句连接到一起，使用UNION ALL语句将多个结果集合并成一组结果集。在每个子查询中，pivot_column部分是列的名称，value_column则是该列的值。例如，假设我们有一个表格记录每月销售额，字段包括年份、月份和销售额。要将多列数据转换为行展示，可以使用如下SQL语句：

SELECT CONCAT_WS('-', year, month) AS identifier_column, 'Jan' AS pivot_column, Jan_sales AS value_column FROM sales_table
UNION ALL
SELECT CONCAT_WS('-', year, month) AS identifier_column, 'Feb' AS pivot_column, Feb_sales AS value_column FROM sales_table
UNION ALL
SELECT CONCAT_WS('-', year, month) AS identifier_column, 'Mar' AS pivot_column, Mar_sales AS value_column FROM sales_table
...
UNION ALL
SELECT CONCAT_WS('-', year, month) AS identifier_column, 'Dec' AS pivot_column, Dec_sales AS value_column FROM sales_table;

在这个例子中，每月的销售额被转换成了新的行数据。