业务场景与问题描述
在数据分析和报表生成中，我们经常会遇到需要将明细行数据转换成汇总列展示的情况，这通常被称为“行转列”或“数据透视”。更进一步地，如果转换后的列需要聚合多个字段的信息并进行拼接，例如将订单中的不同产品及其数量和规格聚合到同一行中的不同列，问题会变得更加复杂。
考虑以下订单明细表 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 |
我们的目标是将其转换为如下格式，按 order_id 和 batch_id 分组，并将不同 product_id 的信息（数量 x 规格）聚合到各自的列中：
| order_id | batch_id | product1 | product5 |
|---|---|---|---|
| 1 | 1 | 1 x small | 1 x small, 1 medium |
这里的挑战在于：
- 需要将 product_id 的值转换为列名（例如 product1, product5）。
- 每个产品列需要聚合该产品对应的 COUNT(*) 和 size 信息，并以 [数量] x [规格] 的形式拼接。
- 最关键的是，product_id 的种类可能是动态变化的，如果每次都手动编写 CASE 语句，将难以维护。
静态解决方案及其局限性
对于固定数量的 product_id，我们可以使用 GROUP_CONCAT 结合 CASE 语句来实现：
SELECT
order_id,
batch_id,
GROUP_CONCAT(CASE WHEN product_id = 1 THEN CONCAT(1, ' x ', size) END) AS product1,
GROUP_CONCAT(CASE WHEN product_id = 5 THEN CONCAT(1, ' x ', size) END) AS product5
FROM
table1
GROUP BY
order_id,
batch_id;代码解析：
- GROUP_CONCAT(...) 函数用于将组内的字符串值连接起来。
- CASE WHEN product_id = N THEN ... END 语句根据 product_id 的值选择性地生成要拼接的字符串。
- CONCAT(1, ' x ', size) 用于将数量（这里假设为1，因为原始数据中 type_id 始终为1，且示例输出为 1 x small，实际上是该规格的计数）和 size 拼接成 [数量] x [规格] 的格式。
局限性： 这种方法的缺点是显而易见的：如果 product_id 的种类非常多或者会动态变化，查询语句将变得非常庞大且难以维护。每次新增或删除产品种类，都需要手动修改 SQL 查询。
动态 SQL 解决方案（推荐）
为了克服静态解决方案的局限性，我们可以利用 MySQL 的预处理语句（Prepared Statements）来构建动态 SQL。这种方法允许我们根据数据库中的实际数据动态生成查询的列，从而实现高度的灵活性和可维护性。
以下是实现动态聚合和多字段拼接的完整步骤和代码：
-- 步骤1：获取需要动态生成的列名和聚合表达式
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) t1
);
-- 步骤2：构建完整的动态查询语句
SET @query := CONCAT(
'SELECT order_id, batch_id, ',
@columns,
' FROM (SELECT product_id, order_id, batch_id, size, COUNT(*) cnt FROM table1 GROUP BY product_id, order_id, batch_id, size) t1 GROUP BY order_id, batch_id'
);
-- 步骤3：准备并执行动态查询
PREPARE stmt FROM @query;
EXECUTE stmt;
-- 步骤4：释放预处理语句
DEALLOCATE PREPARE stmt;代码解析与步骤详解：
-
*预聚合计数 (`COUNT() cnt)：** 在构建动态列之前，我们需要先对原始数据进行一次预处理，计算每个(product_id, order_id, batch_id, size)组合出现的次数。这是因为最终输出格式是[数量] x [规格]，而这个“数量”是特定规格产品的计数，而不是原始type_id` 列的值。
SELECT product_id, order_id, batch_id, size, COUNT(*) cnt FROM table1 GROUP BY product_id, order_id, batch_id, size
这个子查询的结果将作为外部查询的基础数据源 t1。例如，对于 product_id=5, size='small'，cnt 将为1；对于 product_id=5, size='medium'，cnt 也将为1。
-
动态生成列表达式 (SET @columns := ...)： 这一步是动态 SQL 的核心。我们首先从 table1 中获取所有不重复的 product_id。然后，利用 GROUP_CONCAT 将这些 product_id 转换为一系列 GROUP_CONCAT(CASE WHEN ... END) AS productN 形式的字符串。
- SELECT DISTINCT product_id FROM table1：获取所有不同的产品ID。
- CONCAT(...)：针对每个 product_id，生成一个类似于 GROUP_CONCAT(CASE WHEN product_id=1 THEN CONCAT(cnt,' x ', size) END) AS product1 的字符串片段。这里的 cnt 来自于前面预聚合的计数。
- GROUP_CONCAT(...)：将所有这些片段用逗号连接起来，形成最终的动态列列表字符串，存储在用户变量 @columns 中。例如，如果 product_id 有 1 和 5，@columns 的值将是 "GROUP_CONCAT(CASE WHEN product_id=1 THEN CONCAT(cnt,' x ', size) END) AS product1,GROUP_CONCAT(CASE WHEN product_id=5 THEN CONCAT(cnt,' x ', size) END) AS product5"。
构建完整查询语句 (SET @query := CONCAT(...))： 将静态的 SELECT order_id, batch_id, 部分、动态生成的 @columns 变量以及预聚合的子查询 FROM (...) t1 GROUP BY order_id, batch_id 拼接起来，形成一个完整的 SQL 查询字符串，存储在用户变量 @query 中。
-
准备并执行 (PREPARE stmt FROM @query; EXECUTE stmt;)：
- PREPARE stmt FROM @query;：MySQL 准备一个名为 stmt 的预处理语句，其内容是 @query 变量中存储的动态 SQL 字符串。
- EXECUTE stmt;：执行这个预处理语句，完成数据查询。
释放预处理语句 (DEALLOCATE PREPARE stmt;)： 执行完毕后，释放预处理语句所占用的资源，这是一个良好的编程习惯。
注意事项与总结
- MySQL 特性： 这种动态 SQL 的方法主要依赖于 MySQL 的用户变量和预处理语句功能。在其他数据库（如 PostgreSQL、SQL Server、Oracle）中，实现动态 SQL 的语法和方式会有所不同，例如可能使用存储过程、PL/SQL、T-SQL 或其他特定的动态 SQL 构造。
- 性能考量： 动态 SQL 虽然强大，但在极端情况下（例如 product_id 种类非常多，导致生成的 SQL 字符串过长）可能会有性能开销或达到字符串长度限制。GROUP_CONCAT 也有默认的长度限制（group_concat_max_len 系统变量），可能需要调整。
- 安全性： 在实际应用中，如果动态 SQL 的构建涉及用户输入，务必进行严格的输入验证和过滤，以防止 SQL 注入攻击。本例中 product_id 是从数据库中获取的，相对安全。
- 可读性与调试： 动态 SQL 可能会降低查询的可读性和调试难度。在开发阶段，可以通过打印 @query 变量来查看实际执行的 SQL 语句。
通过上述动态 SQL 的方法，我们能够灵活地将行数据透视并聚合为多列，有效解决了产品种类动态变化带来的维护难题，是处理复杂数据转换需求时一个非常实用的技巧。
