MySQL 行变列

1. 概述

MySQL 是一个常用的关系型数据库管理系统，具有功能强大、稳定可靠的特点。在实际应用中，我们通常会遇到将一行数据转化为多列的需求，这在 MySQL 中被称为行变列（Pivot Table）。本文将详细讲解行变列的方法和技巧。

2. 基础知识

在开始讲解行变列之前，我们需要先了解以下几个基本概念。

2.1 数据表

数据表是 MySQL 中最基本的数据组织形式，由若干行和列组成。每行代表一条数据记录，每列代表数据的属性。

2.2 行变列

行变列指的是将一行数据转化为多列的操作。通常情况下，我们会根据某个字段的值将数据进行分组，并对每个分组的数据进行汇总。

2.3 聚合函数

聚合函数是对数据进行统计分析的函数，比如求和、计数、平均值等。在行变列时，我们通常需要结合聚合函数将数据进行汇总。

3. 方法一：使用 CASE WHEN

使用 CASE WHEN 是一种常见且灵活的行变列方法。下面是一个示例表格 orders，包含订单号、订单日期和订单金额三列。

order_id	order_date	amount
1	2020-01-01	100
2	2020-01-02	200
3	2020-01-02	300
4	2020-01-03	400
5	2020-01-03	500

假设我们要按照订单日期将订单金额进行汇总，并将汇总结果展示在多列中。可以使用如下 SQL 语句实现：

SELECT
  order_date,
  SUM(CASE WHEN order_date = '2020-01-01' THEN amount END) AS '2020-01-01',
  SUM(CASE WHEN order_date = '2020-01-02' THEN amount END) AS '2020-01-02',
  SUM(CASE WHEN order_date = '2020-01-03' THEN amount END) AS '2020-01-03'
FROM
  orders
GROUP BY
  order_date;

运行以上 SQL 语句，输出如下：

order_date	2020-01-01	2020-01-02	2020-01-03
2020-01-01	100	NULL	NULL
2020-01-02	NULL	500	NULL
2020-01-03	NULL	NULL	900

从结果可以看出，通过 CASE WHEN 可以将原始数据表的行变为多列，每列对应不同的订单日期，并按日期对订单金额进行汇总。

4. 方法二：使用 GROUP_CONCAT

除了使用 CASE WHEN，还可以使用 GROUP_CONCAT 函数实现行变列的效果。GROUP_CONCAT 可以将多行数据合并为一个单独的字符串，并以指定的分隔符进行分隔。

以下是一个示例表格 employees，包含员工姓名和所属部门两列。

name	department
John	HR
Mary	Finance
Peter	HR
Emma	IT
Jack	IT

假设我们要按照部门对员工姓名进行汇总，并将汇总结果展示在多列中。可以使用如下 SQL 语句实现：

SELECT
  department,
  GROUP_CONCAT(name SEPARATOR ',') AS employees
FROM
  employees
GROUP BY
  department;

运行以上 SQL 语句，输出如下：

department	employees
HR	John,Peter
Finance	Mary
IT	Emma,Jack

从结果可以看出，通过 GROUP_CONCAT 函数可以将原始数据表的行变为多列，每列对应不同的部门，并按部门将员工姓名合并为一个字符串。

5. 方法三：使用动态 SQL

如果需要转化的行数和列数不确定，可以使用动态 SQL 实现行变列的功能。动态 SQL 是指在程序运行期间动态构建 SQL 语句的方法。

以下是一个示例表格 products，包含产品名称和销售额两列。

name	sales
Product A	100
Product B	200
Product C	300

假设我们要将所有产品的销售额转化为多列，并以产品名称作为列名。可以使用如下动态 SQL 语句实现：

SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN name = ''',
      name,
      ''' THEN sales ELSE 0 END) AS ',
      name
    )
  ) INTO @sql
FROM
  products;

SET @sql = CONCAT('SELECT ', @sql, ' FROM products');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

运行以上 SQL 语句，输出如下：