mysql 行转多行简介：

MySQL行转多行：解锁数据处理的强大技巧 在数据分析和处理的过程中，数据格式转换是一个常见的需求

    特别是在使用MySQL时，行转多行（也称为行转列或数据透视）的操作尤为常见

    这种转换不仅能帮助我们更好地理解和分析数据，还能显著提升数据处理的效率和灵活性

    本文将详细介绍MySQL中行转多行的技巧，通过实例和理论结合的方式，展示其强大的功能和实用性

     一、行转多行的基本概念 行转多行，通常指的是将数据库表中的多行数据按照某种规则转换为多列显示，或者将某一列中的多个值拆分成多行显示

    这种操作在报表生成、数据分析和数据挖掘等领域非常有用

     MySQL本身并没有直接提供类似Excel中的透视表功能，但我们可以通过联合查询（JOIN）、子查询、UNION ALL、以及存储过程等方式实现行转多行的效果

     二、使用UNION ALL实现行转多行 UNION ALL 是 MySQL 中一个常用的操作符，用于合并两个或多个 SELECT 语句的结果集

    通过巧妙使用 UNION ALL，我们可以将某一列中的多个值拆分成多行显示

     示例数据表 假设我们有一个存储商品信息的表 `product_info`，结构如下： CREATE TABLEproduct_info ( id INT AUTO_INCREMENT PRIMARY KEY, product_nameVARCHAR(255), attributes TEXT ); 表中数据如下： INSERT INTOproduct_info (product_name,attributes) VALUES (Product A, Color:Red,Size:M,Brand:X), (Product B, Color:Blue,Size:L,Brand:Y), (Product C, Color:Green,Size:S,Brand:Z); 其中 `attributes` 列存储了多个属性值，以逗号分隔

    我们希望将这些属性值拆分成多行显示

     步骤一：创建辅助表 首先，我们需要一个辅助表来存储拆分后的属性值

    为了简单起见，这里我们直接创建一个临时表`temp_attributes`： CREATE TEMPORARY TABLEtemp_attributes ( id INT, attributeVARCHAR(25 ); 步骤二：拆分属性值并插入辅助表 接下来，我们使用存储过程和字符串函数来拆分 `attributes` 列中的值，并将其插入到 `temp_attributes` 表中

     DELIMITER // CREATE PROCEDUREsplit_attributes() BEGIN DECLARE done INT DEFAULT FALSE; DECLAREproduct_id INT; DECLAREattr_text TEXT; DECLAREattr_value VARCHAR(255); DECLARE cur CURSOR FOR SELECT id, attributes FROM product_info; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; DROP TEMPORARY TABLE IF EXISTS temp_split; CREATE TEMPORARY TABLE temp_split( id INT, valueVARCHAR(25 ); OPEN cur; read_loop: LOOP FETCH cur INTOproduct_id,attr_text; IF done THEN LEAVEread_loop; END IF; SETattr_value = SUBSTRING_INDEX(attr_text, ,, 1); WHILEattr_value IS NOT NULL DO INSERT INTO temp_split(id, value) VALUES(product_id, TRIM(attr_value)); SETattr_text =REPLACE(attr_text,CONCAT(attr_value,,),); SETattr_value = SUBSTRING_INDEX(attr_text, ,, 1); END WHILE; END LOOP; CLOSE cur; INSERT INTO temp_attributes(id, attribute) SELECT id, value FROMtemp_split; END // DELIMITER ; 步骤三：调用存储过程并查询结果 调用存储过程 `split_attributes`，然后查询 `temp_attributes` 表，即可得到拆分后的结果

     CALL split_attributes(); SELECT p.product_name, a.attribute FROM temp_attributes a JOIN product_info p ON a.id = p.id; 查询结果如下： +--------------+-------------+ | product_name | attribute | +--------------+-------------+ | Product A | Color:Red | | Product A | Size:M | | Product A | Brand:X | | Product B | Color:Blue | | Product B | Size:L | | Product B | Brand:Y | | Product C | Color:Green | | Product C | Size:S | | Product C | Brand:Z | +--------------+-------------+ 通过这种方式，我们成功地将 `attributes` 列中的多个值拆分成了多行显示

     三、使用动态SQL和递归CTE实现复杂行转多行 对于更复杂的行转多行需求，比如嵌套结构的JSON数据或者多级分类数据，我们可以考虑使用动态SQL和递归公用表表达式（CTE）

     示例：JSON数据行转多行 假设我们有一个存储JSON数据的表 `json_data`，结构如下： CREATE TABLEjson_data ( id INT AUTO_INCREMENT PRIMARY KEY, data JSON ); 表中数据如下： INSERT INTOjson_data (data) VALUES ({name: John, skills:【Java, Python, SQL】}), ({name: Jane, skills:【HTML, CSS, JavaScript】}); 我们希望将`skills`数组中的值拆分成多行显示

     步骤一：使用JSON_TABLE函数 MySQL 5.7及以上版本提供了`JSON_TABL