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_scores
group 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_scores
group 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 age
from (
select id
,wm_concat(';',concat(obj_name,':',obj_value)) as column_value
from school
group by id
)
;
-- 如果值存在英文冒号，导致取值为空的原因，看下面两个sql例子即可理解
-- 返回null
select keyvalue('name:小红:3737;age:13','name');
-- 返回3737
select keyvalue('name:小红:3737;age:13','name:小红');
2、列转行(将多列数据转为多行)
2.1、使用 union all
select id, '数学' as subject, 数学 as score from student_scores_pivot union all select id, '语文' as subject, 语文 as score from student_scores_pivot union all select id, '英语' as subject, 英语 as score from student_scores_pivot order 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 score
from student_scores_pivot s
cross 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 score
from student_scores_pivot s
cross join (
values
row('数学')
,row('语文')
,row('英语')
) c(subject);
2.3、使用 json 函数 (mysql 8.0+)
select
id,
jt.subject,
jt.score
from 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 school
values
('1','name','小明'),
('1','age','12'),
('2','name','小红'),
('2','age','13')
;
-- 列转行
select id
,keyvalue(column_value,'name') as name
,keyvalue(column_value,'age') as age
from (
select id
,wm_concat(';',concat(obj_name,':',obj_value)) as column_value
from school
group by id
)
;
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。