在数据分析和报表生成中，我们经常会遇到需要将行式数据（如不同产品的详细信息）转换为列式汇总数据（如每个产品作为一个单独的列，并显示其聚合信息）的场景。这种操作通常被称为“数据透视”或“交叉表查询”。当需要透视的列（例如产品ID）是动态的，即其数量和具体值不固定时，传统的静态SQL查询将难以维护。
1. 静态数据透视方法
对于已知且数量有限的产品ID，我们可以利用MySQL的GROUP_CONCAT函数结合CASE WHEN语句来实现静态的数据透视。
假设我们有如下订单产品明细表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的type_id x size信息聚合到各自的列中：
| order_id | batch_id | product1 | product5 |
|---|---|---|---|
| 1 | 1 | 1 x small | 1 x small, 1 x medium |
使用静态SQL可以这样实现：
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;优点： 简单直观，易于理解。 缺点： 缺乏灵活性。如果product_id的数量很多或者会动态变化，每次新增或删除产品都需要手动修改SQL查询，这使得查询难以维护且容易出错。
2. 动态数据透视方法：利用MySQL预处理语句
为了解决静态透视的局限性，我们可以利用MySQL的预处理语句（Prepared Statements）来构建动态SQL。这种方法允许我们根据数据库中的实际数据来生成SQL查询的列名和逻辑。
核心思想是分两步走：
- 首先，动态地生成所有需要透视的列（例如product1, product5等）。
- 然后，将这些动态生成的列名和聚合逻辑嵌入到一个完整的SQL查询字符串中，并通过预处理语句执行。
下面是实现动态透视的详细步骤和代码：
-- 步骤1：声明一个变量用于存储动态生成的列名和聚合表达式
SET @columns := NULL;
-- 步骤2：构建动态的列名和聚合表达式字符串
-- 这一步会查询所有不重复的product_id，并为每个product_id生成一个GROUP_CONCAT(CASE WHEN ...) AS productX 的表达式。
-- 注意：为了正确计算每个产品在特定订单批次下的数量，我们需要一个内层子查询来预先统计每个product_id、order_id、batch_id和size组合的出现次数。
SELECT
GROUP_CONCAT(
CONCAT(
"GROUP_CONCAT(CASE WHEN product_id=", product_id, " THEN CONCAT(cnt,' x ', size) END) AS product", product_id
)
) INTO @columns
FROM
(SELECT DISTINCT product_id FROM table1) AS t1;
-- 步骤3：构建完整的动态SQL查询字符串
-- 外层查询对预先统计好数量的临时表进行GROUP_CONCAT聚合。
SET @query := CONCAT(
'SELECT order_id, batch_id, ',
@columns,
' FROM (SELECT product_id, order_id, batch_id, size, COUNT(*) AS cnt FROM table1 GROUP BY product_id, order_id, batch_id, size) AS t_counted GROUP BY order_id, batch_id'
);
-- 步骤4：准备并执行动态SQL查询
PREPARE stmt FROM @query;
EXECUTE stmt;
-- 步骤5：释放预处理语句资源
DEALLOCATE PREPARE stmt;代码解析：
- SET @columns := NULL;: 初始化一个用户会话变量@columns，用于存储动态生成的列表达式。
-
SELECT GROUP_CONCAT(...) INTO @columns FROM (SELECT DISTINCT product_id FROM table1) AS t1;:
- 内层子查询 (SELECT DISTINCT product_id FROM table1) 获取所有不重复的product_id。
- 外层 GROUP_CONCAT 遍历这些 product_id，为每个产品ID构建一个字符串，例如 "GROUP_CONCAT(CASE WHEN product_id=1 THEN CONCAT(cnt,' x ', size) END) AS product1"。
- CONCAT(cnt,' x ', size) 中的 cnt 是一个关键点。它来源于下一步骤中的内层子查询，表示特定产品在特定order_id、batch_id和size组合下的计数。这是为了解决原始数据中可能存在相同product_id但不同size或多次出现的情况，确保聚合结果正确显示“数量 x 尺寸”（例如“1 x small”, “2 x large”）。
-
SET @query := CONCAT('SELECT ...', @columns, ' FROM ...');:
- 将之前生成的 @columns 字符串嵌入到完整的 SELECT 语句中。
- *关键的内层子查询 `(SELECT product_id, order_id, batch_id, size, COUNT() AS cnt FROM table1 GROUP BY product_id, order_id, batch_id, size) AS t_counted**: 这个子查询在外部GROUP BY order_id, batch_id之前，预先计算了每个product_id、order_id、batch_id和size组合的出现次数 (cnt)。这是为了确保GROUP_CONCAT在拼接字符串时，能够正确地包含每个尺寸的计数，避免重复或遗漏。例如，如果product_id=5有两条记录：一条small，一条medium，经过这个子查询后，会得到product_id=5, size='small', cnt=1和product_id=5, size='medium', cnt=1两条记录，然后外层GROUP_CONCAT才能正确地将它们拼接为1 x small, 1 x medium`。
-
PREPARE stmt FROM @query; EXECUTE stmt; DEALLOCATE PREPARE stmt;:
- PREPARE stmt FROM @query;：准备一个名为 stmt 的预处理语句，其内容是 @query 变量中存储的SQL字符串。
- EXECUTE stmt;：执行准备好的语句。
- DEALLOCATE PREPARE stmt;：释放预处理语句占用的资源。
3. 注意事项与总结
- 适用性： 这种动态SQL方法主要适用于MySQL数据库。其他数据库系统可能有其特有的动态SQL或透视表功能（如SQL Server的PIVOT、PostgreSQL的crosstab扩展）。
- 可读性与调试： 动态SQL虽然功能强大，但相比静态SQL，其可读性较差，调试也相对复杂。在开发和测试阶段，可以通过 SELECT @query; 来查看生成的完整SQL字符串，以便调试。
- 性能考量： 对于非常大的数据集，动态SQL的性能可能需要进一步优化。例如，确保所有必要的列都有索引，以及评估内层子查询的效率。
- 安全性： 如果动态SQL的构建涉及到用户输入，务必进行严格的输入验证和过滤，以防止SQL注入攻击。在本教程的例子中，product_id来自数据库自身，因此安全性风险较低。
-
GROUP_CONCAT的限制： GROUP_CONCAT 函数有默认的最大长度限制（group_concat_max_len系统变量）。如果聚合的字符串非常长，可能会被截断。可以通过 SET SESSION group_concat_max_len =
; 临时提高此限制。
通过动态SQL和预处理语句，我们能够灵活地应对数据透视中列名不确定的挑战，极大地提高了SQL查询的适应性和可维护性，是处理复杂报表和数据分析场景的强大工具。
