MySql行转列&列转行方式！

admin • • SQL技巧

MySql行转列&列转行方式！

在MySQL数据库管理中,行转列和列转行是常见的数据处理需求,行转列通常涉及将表中的行数据按照某种规则转换成列形式,常用于报表生成、数据分析等场景,列转行则是将原本以列形式存储的数据转换成行形式,以便于进行进一步的数据处理或分析。

MySql行转列&列转行

行转列

创建语句：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
create table test1(
id int  auto_increment primary key ,
name varchar(255),
course varchar(255),
score int
)
insert into test1(name,course,score) values ('张三','语文',120);
insert into test1(name,course,score) values ('张三','数学',100);
insert into test1(name,course,score) values ('张三','英语',82);
insert into test1(name,course,score) values ('李四','语文',89);
insert into test1(name,course,score) values ('李四','数学',99);
insert into test1(name,course,score) values ('李四','英语',87);
insert into test1(name,course,score) values ('王五','语文',78);
insert into test1(name,course,score) values ('王五','数学',85);
insert into test1(name,course,score) values ('王五','英语',145);
insert into test1(name,course,score) values ('王五','物理',40);
insert into test1(name,course,score) values ('王五','化学',62);

原始数据：

2024110509022713

1、第一种方法：

1
2
3
4
5
6
7
8
-- 使用case when then else，这里使用sum函数也可以
select name,
max(case course when '语文' then score else 0 end)as 'chinese',
max(case course when '数学' then score else 0 end)as 'math',
max(case course when '英语' then score else 0 end)as 'english',
max(case course when '物理' then score else 0 end)as 'wuli',
max(case course when '化学' then score else 0 end)as 'huaxue'
from test1 group by name

第一种结果：

pEVn4Df.png

2、第二种方法：

1
2
3
4
5
6
7
8
-- 使用if语句，这里使用sum函数也可以
select name,
max(if(course = '语文',score,0))as 'chinese',
max(if(course = '数学',score,0))as 'math',
max(if(course = '英语',score,0))as 'english',
max(if(course = '物理',score,0))as 'wuli',
max(if(course = '化学',score,0))as 'huaxue'
from test1 group by name

第二种结果：

pEVnoVS.png

3、第三种方法：

1
2
3
4
5
6
7
8
9
10
-- 动态拼接sql语句，不管多少行都会转列
set @sql = null;
select group_concat(distinct concat('max(if(a.course = ''',a.course,''', a.score, 0)) as ''',a.course, '''')) into @sql from test1 a;
set @sql = concat('select name,', @sql, 'from test1 a group by a.name' );
prepare stmt from @sql;  -- 动态生成脚本，预备一个语句
execute stmt;            -- 动态执行脚本，执行预备的语句
deallocate prepare stmt; -- 释放预备的语句
-- 通过这个查询拼接的sql
select @sql

第三种结果：

pEVnTUg.png

查询出来的sql结果：

1
2
-- 查询出来的结果
select name,max(if(a.course = '语文', a.score, 0)) as '语文',max(if(a.course = '数学', a.score, 0)) as '数学',max(if(a.course = '英语', a.score, 0)) as '英语',max(if(a.course = '物理', a.score, 0)) as '物理',max(if(a.course = '化学', a.score, 0)) as '化学',max(if(a.course = '政治', a.score, 0)) as '政治'from test1 a group by a.name

4、第四种方法：

1
2
3
4
5
6
7
8
-- 使用distinct
select distinct  a.name, 
(select score from test1 b where a.name=b.name and b.course='语文' ) as 'chinese', 
(select score from test1 b where a.name=b.name and b.course='数学' ) as 'math',
(select score from test1 b where a.name=b.name and b.course='英语' ) as 'english', 
(select score from test1 b where a.name=b.name and b.course='物理' ) as 'wuli', 
(select score from test1 b where a.name=b.name and b.course='化学' ) as 'huaxue'
from test1 a

第四种结果：

pEVn75Q.png

5、第五种方法：

以下三种方法是可以做统计的用的，有三种统计写法，有需求的话可以使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- 使用with rollup统计第一种
select ifnull(name,'总计') as name,
max(if(course = '语文',score,0))as 'chinese',
max(if(course = '数学',score,0))as 'math',
max(if(course = '英语',score,0))as 'english',
max(if(course = '物理',score,0))as 'wuli',
max(if(course = '化学',score,0))as 'huaxue',
sum(IF(course='total',score,0)) as 'total'
from (select name,ifnull(course,'total') as course,sum(score) as score
from test1 group by name, course with rollup having name is not null )as a group by name with rollup;
-- 使用with rollup与union all统计第二种
select name,
max(if(course = '语文',score,0))as 'chinese',
max(if(course = '数学',score,0))as 'math',
max(if(course = '英语',score,0))as 'english',
max(if(course = '物理',score,0))as 'wuli',
max(if(course = '化学',score,0))as 'huaxue',
sum(score) as total from test1 group by name
union all
select 'total',
max(if(course = '语文',score,0))as 'chinese',
max(if(course = '数学',score,0))as 'math',
max(if(course = '英语',score,0))as 'english',
max(if(course = '物理',score,0))as 'wuli',
max(if(course = '化学',score,0))as 'huaxue',
sum(score) from test1
-- 使用if与with rollup统计
select ifnull(name,'total') as name,
max(if(course = '语文',score,0))as 'chinese',
max(if(course = '数学',score,0))as 'math',
max(if(course = '英语',score,0))as 'english',
max(if(course = '物理',score,0))as 'wuli',
max(if(course = '化学',score,0))as 'huaxue',
sum(score) AS total
from test1
group by name with rollup

第五种结果：

以上三种语句结果都是一样的

6、第六种方法：

1
2
3
4
-- 使用group_concat，这个一般不推荐
select name,
group_concat(course,':',score separator '@')as course
from test1 group by name

第六种结果：

7、第七中方法：

1
2
3
4
5
6
7
8
-- 这个也不推荐使用
set @EE='';
select @EE :=concat(@EE,'sum(if(course= \'',course,'\',score,0)) as ',course, ',') as aa from (select distinct course from test1) a ;
set @QQ = concat('select ifnull(name,\'total\')as name,',@EE,' sum(score) as total from test1 group by name with rollup');
-- SELECT @QQ;
prepare stmt from @QQ;
execute stmt;
deallocate prepare stmt;

第七种结果：

列转行

创建语句：

1
2
3
4
5
6
7
8
9
10
11
12
13
create table test2(
id int  auto_increment primary key ,
name varchar(255) ,
chinese int,
math int,
english int,
wuli int,
huaxue int
)
insert into test2(name,chinese,math,english,wuli,huaxue) values ('张三',110,120,85,null,null);
insert into test2(name,chinese,math,english,wuli,huaxue) values ('李四',130,88,89,null,null);
insert into test2(name,chinese,math,english,wuli,huaxue) values ('王五',93,124,87,98,67);

原始数据：

1、第一种方法：

有两种写法：

1
2
union与union all的区别就是：
union可以去除重复结果集，union all不会去除重复的结果集
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 第一种使用union实现列传行
select name,'语文' as course, chinese as 'score' from test2 union
select name,'数学' as course, math as 'score' from test2 union
select name,'英语' as course, english as 'score' from test2 union
select name,'物理' as course, wuli as 'score' from test2 union
select name,'化学' as course, huaxue as 'score' from test2 order by name asc
-- 第二种写法可以对null值结果集处理
select * from
(select name,'语文' as course, chinese as 'score' from test2 union
select name,'数学' as course, math as 'score' from test2 union
select name,'英语' as course, english as 'score' from test2 union
select name,'物理' as course, wuli as 'score' from test2 union
select name,'化学' as course, huaxue as 'score' from test2)a
where a.score is not null order by name asc

第一种结果：

有null结果：

没有null结果：

总结

以上为个人经验，希望能给大家一个参考。

 

 

学习资料见知识星球。

以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。

快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利​​​​！

更多技巧， www.excelbook.cn

欢迎 加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；

Excelbook.cn Excel技巧 SQL技巧 Python 学习！

你将获得：

1、价值上万元的专业的PPT报告模板。

2、专业案例分析和解读笔记。

3、实用的Excel、Word、PPT技巧。

4、VIP讨论群，共享资源。

5、优惠的会员商品。

6、一次付费只需99元，即可下载本站文章涉及的文件和软件。

文章版权声明 1、本网站名称：Excelbook
2、本站永久网址：http://www.excelbook.cn
3、本网站的文章部分内容可能来源于网络，仅供大家学习与参考，如有侵权，请联系站长王小琥进行删除处理。
4、本站一切资源不代表本站立场，并不代表本站赞同其观点和对其真实性负责。
5、本站一律禁止以任何方式发布或转载任何违法的相关信息，访客发现请向站长举报。
6、本站资源大多存储在云盘，如发现链接失效，请联系我们我们会第一时间更新。

THE END
分享
二维码
< <上一篇
下一篇>>