MySQL 如何将动态列转换为行

MySQL是一个流行的关系型数据库。有时候，我们需要将动态的列转换为行。这些动态表通常没有预定义的列，并且根据不同的情况它们的列数量可能不同。在这种情况下，我们需要使用转置技术。

在本文中，我们将研究MySQL中如何将动态列转换为行。我们将使用MySQL 5.7版本。

阅读更多：MySQL 教程

数据模型和样本测试数据

在本文中，我们将使用一个名为sales的表。表格由以下列组成：

SaleID	Item	Price	Month
1	Kitchen Towel	5	January
2	Hand Towel	2.5	January
3	Kitchen Towel	4	February
4	Hand Towel	2.2	February
5	Dish Towel	3	January
6	Dish Towel	3.1	February

这张表有四列。每列都是固定的且事先已知的。有时，我们需要使用类似的数据，但列不是固定的。例如，sales表可以包含更多的列，例如仓库和地区，甚至可以包含更多不同的产品。我们将学习如何使用这种数据模型来转换动态列。

转置动态列

我们将展示如何将动态列转换为行。为此，我们将创建一个由item、month和price组成的二维数组。我们将先使用静态列进行示例，并在之后处理动态列。

我们可以使用以下查询来获取将列转换为行的结果：

SELECT* FROM (SELECT item, 
               MAX(IF(month = 'January', price, NULL)) AS January, 
               MAX(IF(month = 'February', price, NULL)) AS February
          FROM sales GROUP BY item) sales_transpose

该查询将输出以下结果：

item	January	February
Kitchen Towel	5	4
Hand Towel	2.5	2.2
Dish Towel	3	3.1

我们现在让该查询处理动态列。

在这个例子中，我们将使用sales表，并添加额外的列warehouse和region。我们的任务将是将所有的动态列与转置月份相结合。这将使我们能够动态将列转换为行，而不必使用硬编码列名称。

我们需要使用以下查询来执行此操作：

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(month = ''',
      month,''', price, NULL)) AS ',month)) INTO @sql
FROM sales;
SET @sql = CONCAT('SELECT item, ', @sql, ' FROM sales GROUP BY item');

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

在这个例子中，我们首先定义了一个变量@ sql。我们将使用它来存储动态地构造的SQL查询的文本。我们然后选择一个DISTINCT month列的值，并使用它们来构造MAX(IF())句子。这个句子将执行条件语句，以将price值添加到我们动态构造的列中。

接下来，我们将SELECT语句动态地设置为包含所构造的查询。最后，我们准备并执行该语句。

该查询将输出以下结果：