在数据库操作中，我们经常会遇到需要将数据的行转换为列（即“行转列”或“数据透视”），并对转换后的数据进行聚合的需求。特别是在处理具有多类别属性的数据时，例如将不同产品id的详细信息聚合到同一行中，这会变得尤为重要。本文将深入探讨如何使用mysql中的group_concat函数和动态sql来实现这种复杂的数据转换和聚合。
1. 问题描述
假设我们有一个名为table1的订单详情表，其结构和数据示例如下：
| id | order_id | batch_id | bucket_id | menu_id | product_id | type_id | size |
|---|---|---|---|---|---|---|---|
| 1 | 1 | 1 | 1 | 1 | 1 | 1 | small |
| 2 | 1 | 1 | 1 | 1 | 5 | 1 | small |
| 3 | 1 | 1 | 1 | 1 | 5 | 1 | medium |
我们的目标是将其转换为以下“宽格式”，其中product_id的值（例如1和5）变为列名，并且每个产品下的type_id、size以及它们的计数被聚合为一个字符串：
| order_id | batch_id | product1 | product5 |
|---|---|---|---|
| 1 | 1 | 1 x small | 1 x small, 1 medium |
2. 静态行转列与聚合方案
对于已知且数量有限的product_id，我们可以使用GROUP_CONCAT结合CASE WHEN语句来实现。
示例代码：
SELECT
order_id,
batch_id,
GROUP_CONCAT(CASE WHEN product_id = 1 THEN CONCAT(type_id, ' x ', size) END) AS product1,
GROUP_CONCAT(CASE WHEN product_id = 5 THEN CONCAT(type_id, ' x ', size) END) AS product5
FROM
table1
GROUP BY
order_id,
batch_id;解释：
- GROUP_CONCAT(expression)：此函数将组内expression的所有非NULL值连接成一个字符串。默认分隔符是逗号。
- CASE WHEN product_id = N THEN CONCAT(type_id, ' x ', size) END：对于每个product_id，我们使用CASE WHEN来有条件地生成一个字符串（例如'1 x small'）。如果product_id不匹配，则CASE语句返回NULL，GROUP_CONCAT会忽略这些NULL值。
- GROUP BY order_id, batch_id：确保我们按订单和批次对数据进行分组，以便聚合。
局限性： 这种方法的主要问题在于其非动态性。如果product_id的数量非常多，或者product_id的值会动态变化，那么手动编写和维护这样的查询将变得极其困难且不切实际。例如，如果有成百上千个产品，查询语句会变得非常庞大且难以管理。
3. 动态行转列与聚合方案（MySQL预处理语句）
为了解决静态方案的局限性，我们可以利用MySQL的预处理语句（Prepared Statement）来动态构建查询。这允许我们根据实际数据自动生成列名和聚合逻辑。
核心思想：
- 首先，查询出所有不重复的product_id。
- 然后，根据这些product_id动态构建GROUP_CONCAT和CASE WHEN的列表达式字符串。
- 最后，将这个动态生成的列表达式插入到主查询中，并通过预处理语句执行。
示例代码：
-- 步骤1：预先计算每个产品、订单、批次和尺寸组合的计数
-- 这是为了正确处理类似 "1 x small" 和 "1 medium" 中的数量前缀
-- 因为原始数据中，product_id=5 有两行，但size不同，需要分别计数
SET @pre_aggregated_data := '
(SELECT
product_id,
order_id,
batch_id,
size,
COUNT(*) AS cnt
FROM
table1
GROUP BY
product_id,
order_id,
batch_id,
size) AS t_pre_agg';
-- 步骤2：动态生成列名和对应的GROUP_CONCAT表达式
-- 例如，如果 product_id 有 1 和 5，则会生成 "GROUP_CONCAT(...) AS product1, GROUP_CONCAT(...) AS product5"
SET @columns := (
SELECT
GROUP_CONCAT(
CONCAT("GROUP_CONCAT(CASE WHEN product_id=", product_id, " THEN CONCAT(cnt,' x ', size) END) AS product", product_id)
)
FROM
(SELECT DISTINCT product_id FROM table1) AS t_distinct_products
);
-- 步骤3：拼接完整的动态SQL查询语句
SET @query := CONCAT('SELECT order_id, batch_id, ', @columns, ' FROM ', @pre_aggregated_data, ' GROUP BY order_id, batch_id');
-- 步骤4：准备并执行动态查询
PREPARE stmt FROM @query;
EXECUTE stmt;
-- 步骤5：释放预处理语句资源
DEALLOCATE PREPARE stmt;详细解释：
-
SET @pre_aggregated_data := '...' (内部预聚合子查询)： 为了正确处理聚合计数（例如1 x small和1 medium），我们首先需要一个子查询来计算每个product_id、order_id、batch_id和size组合的出现次数。这个子查询的结果集被命名为t_pre_agg，它包含了我们最终聚合所需的所有基础数据和计数。
SELECT product_id, order_id, batch_id, size, COUNT(*) AS cnt -- 这里的cnt就是我们需要的数量前缀 FROM table1 GROUP BY product_id, order_id, batch_id, size -
SET @columns := (...) (动态生成列表达式)：
- SELECT DISTINCT product_id FROM table1：这个内部子查询获取table1中所有不重复的product_id。
- GROUP_CONCAT(CONCAT(...))：对于每个唯一的product_id，它构造一个GROUP_CONCAT表达式字符串。例如，当product_id为1时，它会生成"GROUP_CONCAT(CASE WHEN product_id=1 THEN CONCAT(cnt,' x ', size) END) AS product1"。所有这些生成的字符串会通过逗号连接起来，形成一个完整的列列表，如"..., product1, ..., product5, ..."。
- CONCAT(cnt,' x ', size)：这里的cnt就是从步骤1的预聚合子查询中得到的计数。
SET @query := CONCAT(...) (拼接完整查询)： 这一步将固定的SELECT order_id, batch_id,部分与动态生成的列表达式@columns，以及预聚合子查询@pre_aggregated_data拼接起来，形成一个完整的SQL查询字符串。
-
PREPARE stmt FROM @query; EXECUTE stmt; (执行动态查询)：
- PREPARE stmt FROM @query;：MySQL会解析并编译存储在@query变量中的SQL字符串，创建一个名为stmt的预处理语句。
- EXECUTE stmt;：执行这个预处理语句，返回查询结果。
DEALLOCATE PREPARE stmt; (释放资源)： 执行完毕后，释放stmt预处理语句所占用的系统资源。这是一个良好的编程习惯。
4. 注意事项与总结
- MySQL特有性： 本文中的GROUP_CONCAT函数和预处理语句的语法是MySQL特有的。在其他数据库系统（如PostgreSQL、SQL Server、Oracle）中，实现行转列可能需要使用不同的函数（如STRING_AGG, LISTAGG）或技术（如PIVOT操作符，XML/JSON路径）。
- GROUP_CONCAT的长度限制： GROUP_CONCAT函数有一个默认的最大长度限制（group_concat_max_len系统变量，默认为1024字节）。如果聚合的字符串非常长，可能会被截断。可以通过SET GLOBAL group_concat_max_len = N;来增加此限制。
- 性能考量： 动态SQL虽然提供了灵活性，但在非常大的数据集上，它的性能可能不如静态查询或数据库内置的PIVOT功能（如果可用）。动态生成和编译查询会带来一定的开销。
- 数据类型与NULL值： GROUP_CONCAT会忽略NULL值。如果CONCAT函数中的任何参数为NULL，则整个CONCAT结果为NULL。在实际应用中，可能需要使用COALESCE或其他函数处理潜在的NULL值。
- 安全性： 虽然这里我们是从数据库内部查询数据来构建动态SQL，降低了SQL注入的风险，但在其他场景（例如用户输入来构建动态SQL）中，务必注意SQL注入防护。
通过上述动态SQL方案，我们成功地解决了product_id不确定或数量庞大时进行行转列和多字段聚合的挑战。这种方法极大地提高了SQL查询的灵活性和可维护性，是处理复杂数据转换任务的强大工具。
