mysql 实现（行转列）和（列转行）_mysql列转行

文章目录

• 1、行转列(将多行数据转为单行多列)
• • 1.1、使用 CASE WHEN + 聚合函数
  • 1.2、使用 IF + 聚合函数
  • 1.3、使用 PIVOT (MySQL 8.0+)
  • 1.4、dataworks使用wm_concat函数和keyvalue
• 2、列转行(将多列数据转为多行)
• • 2.1、使用 UNION ALL
  • 2.2、使用 CROSS JOIN + 条件筛选
  • 2.3、使用 JSON 函数 (MySQL 8.0+)
• 3、动态行转列
• 4、详细测试demo
• • 4.1、dataworks使用wm_concat函数和keyvalue实现行转列

1、行转列(将多行数据转为单行多列)

1.1、使用 CASE WHEN + 聚合函数

SELECT id, MAX(CASE WHEN subject = \'数学\' THEN score ELSE NULL END) AS \'数学\', MAX(CASE WHEN subject = \'语文\' THEN score ELSE NULL END) AS \'语文\', MAX(CASE WHEN subject = \'英语\' THEN score ELSE NULL END) AS \'英语\'FROM student_scoresGROUP BY id;

1.2、使用 IF + 聚合函数

SELECT id, MAX(IF(subject = \'数学\', score, NULL)) AS \'数学\', MAX(IF(subject = \'语文\', score, NULL)) AS \'语文\', MAX(IF(subject = \'英语\', score, NULL)) AS \'英语\'FROM student_scoresGROUP BY id;

1.3、使用 PIVOT (MySQL 8.0+)

SELECT id, JSON_UNQUOTE(JSON_EXTRACT(pivot_data, \'$.数学\')) AS \'数学\', JSON_UNQUOTE(JSON_EXTRACT(pivot_data, \'$.语文\')) AS \'语文\', JSON_UNQUOTE(JSON_EXTRACT(pivot_data, \'$.英语\')) AS \'英语\'FROM ( SELECT id, JSON_OBJECTAGG(subject, score) AS pivot_data FROM student_scores GROUP BY id) AS t;

1.4、dataworks使用wm_concat函数和keyvalue

• 缺点：当字符串存在英文冒号时会导致获取的值为空；中文冒号不受影响
• 如果存在重复的数据，将导致取数时随机取其中一个；核心原因为wm_concat函数在拼接时顺序不固定，哪怕是增加了order by也没有用
• keyvalue从字符串中取值时，如果有重复key，从左到右取第一个key的值

select id ,keyvalue(column_value,\'name\') as name ,keyvalue(column_value,\'age\') as agefrom ( select id  ,wm_concat(\';\',concat(obj_name,\':\',obj_value)) as column_value from school group by id ) ;

-- 如果值存在英文冒号，导致取值为空的原因，看下面两个sql例子即可理解-- 返回nullselect keyvalue(\'name:小红:3737;age:13\',\'name\');-- 返回3737select keyvalue(\'name:小红:3737;age:13\',\'name:小红\');

2、列转行(将多列数据转为多行)

2.1、使用 UNION ALL

SELECT id, \'数学\' AS subject, 数学 AS score FROM student_scores_pivotUNION ALLSELECT id, \'语文\' AS subject, 语文 AS score FROM student_scores_pivotUNION ALLSELECT id, \'英语\' AS subject, 英语 AS score FROM student_scores_pivotORDER BY id, subject;

2.2、使用 CROSS JOIN + 条件筛选

• 优点是不用频繁读取磁盘

SELECT s.id, c.subject, CASE c.subject WHEN \'数学\' THEN s.数学 WHEN \'语文\' THEN s.语文 WHEN \'英语\' THEN s.英语 END AS scoreFROM student_scores_pivot sCROSS JOIN ( SELECT \'数学\' AS subject UNION ALL SELECT \'语文\' UNION ALL SELECT \'英语\') c;

• 同样的语句，使用values和row

SELECT s.id, c.subject, CASE c.subject WHEN \'数学\' THEN s.数学 WHEN \'语文\' THEN s.语文 WHEN \'英语\' THEN s.英语 END AS scoreFROM student_scores_pivot sCROSS JOIN (values row(\'数学\') ,row(\'语文\') ,row(\'英语\')) c(subject);

2.3、使用 JSON 函数 (MySQL 8.0+)

SELECT id, jt.subject, jt.scoreFROM student_scores_pivot,JSON_TABLE( JSON_OBJECT( \'数学\', 数学, \'语文\', 语文, \'英语\', 英语 ), \'$.*\' COLUMNS( subject VARCHAR(10) PATH \'$.key\', score INT PATH \'$.value\' )) AS jt;

3、动态行转列

• 对于不确定列名的情况，可以使用存储过程动态生成SQL：

DELIMITER //CREATE PROCEDURE dynamic_pivot(IN table_name VARCHAR(100), IN row_id VARCHAR(100), IN pivot_col VARCHAR(100), IN value_col VARCHAR(100))BEGIN DECLARE done INT DEFAULT FALSE; DECLARE col_name VARCHAR(100); DECLARE col_list TEXT DEFAULT \'\'; DECLARE cur CURSOR FOR SELECT DISTINCT pivot_col FROM table_name; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN cur; read_loop: LOOP FETCH cur INTO col_name; IF done THEN LEAVE read_loop; END IF; SET col_list = CONCAT(col_list, IF(col_list = \'\', \'\', \', \'), \'MAX(CASE WHEN \', pivot_col, \' = \'\'\', col_name, \'\'\' THEN \', value_col, \' ELSE NULL END) AS `\', col_name, \'`\'); END LOOP; CLOSE cur; SET @sql = CONCAT(\'SELECT \', row_id, \', \', col_list, \' FROM \', table_name, \' GROUP BY \', row_id, \';\'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;END //DELIMITER ;-- 调用存储过程CALL dynamic_pivot(\'student_scores\', \'id\', \'subject\', \'score\');

4、详细测试demo

4.1、dataworks使用wm_concat函数和keyvalue实现行转列

-- 创建表create table if not exists school (`id` string,`obj_name` string,`obj_value` string);-- 插入测试数据insert into schoolvalues (\'1\',\'name\',\'小明\'),(\'1\',\'age\',\'12\'),(\'2\',\'name\',\'小红\'),(\'2\',\'age\',\'13\');-- 列转行select id ,keyvalue(column_value,\'name\') as name ,keyvalue(column_value,\'age\') as agefrom ( select id  ,wm_concat(\';\',concat(obj_name,\':\',obj_value)) as column_value from school group by id ) ;

热门小吃推荐