mysql 行转列,多行转一行,列转行,一行转多列

最新推荐文章于 2024-07-19 16:12:08 发布

七年·

最新推荐文章于 2024-07-19 16:12:08 发布

阅读量1.4w

点赞数 9

分类专栏： mysql 文章标签： sql mysql

本文链接：https://blog.csdn.net/qq_28603127/article/details/106217276

版权

mysql 专栏收录该内容

16 篇文章 1 订阅

订阅专栏

hive 行转列/列转行多行转一行/一行转多行

Mysql

创建表语句:

CREATE TABLE student_score(
id BIGINT PRIMARY key auto_increment,
s_name VARCHAR(20) ,
s_sub VARCHAR(20),
s_score INT 
);

insert into student_score values(null,'张三','数学',90);
insert into student_score values(null,'张三','语文',85);
insert into student_score values(null,'张三','英语',92);
insert into student_score values(null,'李四','数学',88);
insert into student_score values(null,'李四','语文',91);
insert into student_score values(null,'李四','英语',99);
insert into student_score values(null,'王五','数学',100);
insert into student_score values(null,'王五','语文',82);
insert into student_score values(null,'王五','英语',88);


select * from student_score;

在这里插入图片描述

行转列

业务场景,按照上图看的话很难只看同学们的数学成绩或者语文成绩,所以要按照以下格式展示:

s_name	数学	语文	英语
张三	90	85	92
李四	88	91	99
李四	100	82	88

思路:
第一步可以将每一行根据学科s_sub分成数学,语文,英语三列 , sql语句:

case  s_sub  when '数学' then s_score else 0 end 数学

完整sql:

select s_name,
case s_sub when '数学' then s_score else 0 end 数学,
case s_sub when '语文' then s_score else 0 end 语文,
case s_sub when '英语' then s_score else 0 end 英语
from student_score

结果:
在这里插入图片描述
最后,需要按照s_name为维度group by聚合一下

select s_name,
sum(case s_sub when '数学' then s_score else 0 end) 数学,
sum(case s_sub when '语文' then s_score else 0 end) 语文,
max(case s_sub when '英语' then s_score else 0 end) 英语
from student_score group by s_name

最终结果:
在这里插入图片描述
如果不是int类型而是 varchar 那么上面是行不通的,因为sum里面不能跟字符串,需要用group_concat()函数替代sum,可先看多行转一行之后再看下方sql

select s_name,
GROUP_CONCAT(case s_sub when '数学' then  CAST(s_score AS char) else null end) 数学,
GROUP_CONCAT(case s_sub when '语文' then  CAST(s_score AS char) else null end) 语文,
GROUP_CONCAT(case s_sub when '英语' then  CAST(s_score AS char) else null end) 英语
from student_score group by s_name

多行转一行

业务场景,以张三为例,将张三的三个成绩拼接成字符串放到一个单元格中显示:

'张三'    '数学:90,语文:85,英语92'

上述类似于

select s_name, sum(s_score) from student_score group by s_name

sum 就是一个聚合函数,只不过它是将所有的数字相加得到结果,根据上面业务场景,此时要用到一个函数 group_concat ,类似于sum 但是它的作用是将聚合后的数据进行字符拼接
group_concat (a,b,c,… separator ’ ‘) 或者
group_concat (a,b,c,…)
group_concat 中的参数是可变参数,多个参数拼接,最后一个speparator xxx 是多行数据拼接后的分隔符,如果不写默认的分隔符是’,'逗号.并且记住要用group by 维度聚合,不写group by 会拼接所有的行
完整的sql:

select s_name,group_concat(s_sub,':',s_score separator '@') all_score from  student_score group by s_name

结果:
在这里插入图片描述

列转行

首先要用到上面行转列的数据

create table student_score2 as 
select s_name,
sum(case s_sub when '数学' then s_score else 0 end) 数学,
sum(case s_sub when '语文' then s_score else 0 end) 语文,
max(case s_sub when '英语' then s_score else 0 end) 英语
from student_score group by s_name

在这里插入图片描述
下面是实现列转行的sql

SELECT
	s_name,
	'数学' AS s_sub,
	数学 AS s_score
  FROM s_score
UNION
SELECT
	s_name,
	'语文' AS s_sub,
	语文 AS s_score
  FROM s_score
UNION
SELECT
	s_name,
	'英语' AS s_sub,
	英语 AS s_score
  FROM s_score

结果
在这里插入图片描述

一行转多行

一行转多行在关系型数据库中是有点儿矛盾的,因为关系型数据库设计原则就是单条数据不能再分,如果需要再分是外部(hive/java程序)分完之后进入关系型数据库的,虽然矛盾,但是也是可以实现的.
创建表:

create table student_score3 as
select s_name,group_concat(s_sub,':',s_score separator '@') all_score 
from  student_score group by s_name

先sql再说思路:

SELECT
 a.s_name,
 substring_index( substring_index( a.all_score, '@', b.id), '@',- 1 )
FROM
 student_score3 a
 JOIN account b ON 
 b.id <= ( length( a.all_score ) - length( replace( a.all_score, '@', '' ) ) + 1 );

account表是作为辅助结构的提供1-n个自增长数字
在这里插入图片描述
查询结果:

思路:(根据sql思想一步步替换成可执行的sql)

①根据张三的一条数据来看:首先,把张三分成三条,三是all_score中想切分成的元素个数决定的

name    index          all_score
张三      1         数学:90@语文:85@英语:92
张三      2         数学:90@语文:85@英语:92
张三      3         数学:90@语文:85@英语:92

上图sql思想是:

select a.s_name, allscore from a join b on n <= all_score_arr.size(); n>=1
--这样子可以根据n的个数join出n条数据.

②转化成下面我们想要的这样:

name    index       score
张三      1         数学:90
张三      2         语文:85
张三      3         英语:92

如果把all_score看成一个数组的话,刚好score就是取的all_score_arr的第index个元素
可以得出sql的思路:

select a.s_name,all_score_arr[n] from a join b on n <= all_score_arr.size();

如果把上面的sql转换成mysql可以执行的sql语句,就能完成多行转一行的目的.

③初步思路有了以后,先看mysql的函数
先是函数 substring_index(str,sp,x)
str是要切分的字符串,sp指分隔符,x是int类型通过下面sql

select substring_index(all_score,'@',x) from student_score3;

可以看规律 x 为1 结果是’数学:90’,x为2 结果是’数学:90@语文85’(从右往左取)
x为-1 结果是’英语:90’,x为-2 结果是’语文:85@英语;92’(从左往右取)
所以要用两个substring_index()函数来实现类似于将字符串all_score根据@切分成数组再根据all_score_arr索引取元素.
substring_index( substring_index( all_score, ‘@’, n), ‘@’,- 1 )
n= 1时取的是’数学:90’
n = 2时取的是’语文:85’
…
这就完成了模拟数组取元素
所以all_score_arr[n] 可以用 substring_index( substring_index( all_score, ‘@’, n), ‘@’,- 1 )替代:

select a.s_name,substring_index( substring_index( all_score, '@', n), '@',- 1 )
from a join b on n <= all_score_arr.size();

n代表着整数可以通过另外一个表的自增主键来替代,b.id就代表了从1-n的整数,得到下面sql:

select a.s_name,substring_index( substring_index( all_score, '@', b.id), '@',- 1 )
from a join b on b.id <= all_score_arr.size();

将a与b与实际的表进行替换

select a.s_name,substring_index(substring_index( all_score, '@', b.id), '@',- 1 )
from student_score2 a join account b on b.id <= all_score_arr.size();

all_score_arr.size()也就是元素的个数应该如何表示呢?
可以通过(分隔符+1)来表示元素个数,
length( a.all_score ) 是字符长度 length( replace( a.all_score, ‘@’, ‘’ ) ) 是去掉分隔符后的长度
两者相减就得到分隔符数,分隔符数加1就是元素个数
所以arr.size() 可以用 length( a.all_score ) - length( replace( a.all_score, ‘@’, ‘’ ) ) + 1 表示
替换后得到最终结果:

 select a.s_name,substring_index( substring_index( a.all_score, '@', b.id+ 1 ), '@',- 1 ) 
 from a join b on b.id<= ( length( a.all_score ) - length( replace( a.all_score, '@', '' ) ) + 1 );