MySql行轉(zhuǎn)列&列轉(zhuǎn)行方式

更新時(shí)間：2024年11月05日 09:06:26 作者：江南春失憶夢(mèng)i

在MySQL數(shù)據(jù)庫(kù)管理中,行轉(zhuǎn)列和列轉(zhuǎn)行是常見的數(shù)據(jù)處理需求,行轉(zhuǎn)列通常涉及將表中的行數(shù)據(jù)按照某種規(guī)則轉(zhuǎn)換成列形式,常用于報(bào)表生成、數(shù)據(jù)分析等場(chǎng)景,列轉(zhuǎn)行則是將原本以列形式存儲(chǔ)的數(shù)據(jù)轉(zhuǎn)換成行形式,以便于進(jìn)行進(jìn)一步的數(shù)據(jù)處理或分析

MySql行轉(zhuǎn)列&列轉(zhuǎn)行

行轉(zhuǎn)列

創(chuàng)建語(yǔ)句：

create table test1(
id int  auto_increment primary key ,
name varchar(255),
course varchar(255),
score int
)

insert into test1(name,course,score) values ('張三','語(yǔ)文',120);
insert into test1(name,course,score) values ('張三','數(shù)學(xué)',100);
insert into test1(name,course,score) values ('張三','英語(yǔ)',82);
insert into test1(name,course,score) values ('李四','語(yǔ)文',89);
insert into test1(name,course,score) values ('李四','數(shù)學(xué)',99);
insert into test1(name,course,score) values ('李四','英語(yǔ)',87);
insert into test1(name,course,score) values ('王五','語(yǔ)文',78);
insert into test1(name,course,score) values ('王五','數(shù)學(xué)',85);
insert into test1(name,course,score) values ('王五','英語(yǔ)',145);
insert into test1(name,course,score) values ('王五','物理',40);
insert into test1(name,course,score) values ('王五','化學(xué)',62);

原始數(shù)據(jù)：

1、第一種方法：

-- 使用case when then else，這里使用sum函數(shù)也可以
select name,
max(case course when '語(yǔ)文' then score else 0 end)as 'chinese',
max(case course when '數(shù)學(xué)' then score else 0 end)as 'math',
max(case course when '英語(yǔ)' then score else 0 end)as 'english',
max(case course when '物理' then score else 0 end)as 'wuli',
max(case course when '化學(xué)' then score else 0 end)as 'huaxue'
from test1 group by name

第一種結(jié)果：

2、第二種方法：

-- 使用if語(yǔ)句，這里使用sum函數(shù)也可以
select name,
max(if(course = '語(yǔ)文',score,0))as 'chinese',
max(if(course = '數(shù)學(xué)',score,0))as 'math',
max(if(course = '英語(yǔ)',score,0))as 'english',
max(if(course = '物理',score,0))as 'wuli',
max(if(course = '化學(xué)',score,0))as 'huaxue'
from test1 group by name

第二種結(jié)果：

3、第三種方法：

-- 動(dòng)態(tài)拼接sql語(yǔ)句，不管多少行都會(huì)轉(zhuǎn)列
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;  -- 動(dòng)態(tài)生成腳本，預(yù)備一個(gè)語(yǔ)句
execute stmt;            -- 動(dòng)態(tài)執(zhí)行腳本，執(zhí)行預(yù)備的語(yǔ)句
deallocate prepare stmt; -- 釋放預(yù)備的語(yǔ)句 

-- 通過(guò)這個(gè)查詢拼接的sql
select @sql

第三種結(jié)果：

查詢出來(lái)的sql結(jié)果：

-- 查詢出來(lái)的結(jié)果
select name,max(if(a.course = '語(yǔ)文', a.score, 0)) as '語(yǔ)文',max(if(a.course = '數(shù)學(xué)', a.score, 0)) as '數(shù)學(xué)',max(if(a.course = '英語(yǔ)', a.score, 0)) as '英語(yǔ)',max(if(a.course = '物理', a.score, 0)) as '物理',max(if(a.course = '化學(xué)', a.score, 0)) as '化學(xué)',max(if(a.course = '政治', a.score, 0)) as '政治'from test1 a group by a.name

4、第四種方法：

-- 使用distinct
select distinct  a.name,  
(select score from test1 b where a.name=b.name and b.course='語(yǔ)文' ) as 'chinese',  
(select score from test1 b where a.name=b.name and b.course='數(shù)學(xué)' ) as 'math',
(select score from test1 b where a.name=b.name and b.course='英語(yǔ)' ) 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='化學(xué)' ) as 'huaxue'  
from test1 a

第四種結(jié)果：

5、第五種方法：

以下三種方法是可以做統(tǒng)計(jì)的用的，有三種統(tǒng)計(jì)寫法，有需求的話可以使用

-- 使用with rollup統(tǒng)計(jì)第一種
select ifnull(name,'總計(jì)') as name,
max(if(course = '語(yǔ)文',score,0))as 'chinese',
max(if(course = '數(shù)學(xué)',score,0))as 'math',
max(if(course = '英語(yǔ)',score,0))as 'english',
max(if(course = '物理',score,0))as 'wuli',
max(if(course = '化學(xué)',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統(tǒng)計(jì)第二種
select name,
max(if(course = '語(yǔ)文',score,0))as 'chinese',
max(if(course = '數(shù)學(xué)',score,0))as 'math',
max(if(course = '英語(yǔ)',score,0))as 'english',
max(if(course = '物理',score,0))as 'wuli',
max(if(course = '化學(xué)',score,0))as 'huaxue',
sum(score) as total from test1 group by name
union all
select 'total',
max(if(course = '語(yǔ)文',score,0))as 'chinese',
max(if(course = '數(shù)學(xué)',score,0))as 'math',
max(if(course = '英語(yǔ)',score,0))as 'english',
max(if(course = '物理',score,0))as 'wuli',
max(if(course = '化學(xué)',score,0))as 'huaxue',
sum(score) from test1


-- 使用if與with rollup統(tǒng)計(jì)
select ifnull(name,'total') as name,
max(if(course = '語(yǔ)文',score,0))as 'chinese',
max(if(course = '數(shù)學(xué)',score,0))as 'math',
max(if(course = '英語(yǔ)',score,0))as 'english',
max(if(course = '物理',score,0))as 'wuli',
max(if(course = '化學(xué)',score,0))as 'huaxue',
sum(score) AS total 
from test1
group by name with rollup

第五種結(jié)果：

以上三種語(yǔ)句結(jié)果都是一樣的

6、第六種方法：

-- 使用group_concat，這個(gè)一般不推薦
select name,
group_concat(course,':',score separator '@')as course
from test1 group by name

第六種結(jié)果：

7、第七中方法：

-- 這個(gè)也不推薦使用
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;

第七種結(jié)果：

列轉(zhuǎn)行

創(chuàng)建語(yǔ)句：

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);

原始數(shù)據(jù)：

1、第一種方法：

有兩種寫法：

union與union all的區(qū)別就是：
union可以去除重復(fù)結(jié)果集，union all不會(huì)去除重復(fù)的結(jié)果集

-- 第一種使用union實(shí)現(xiàn)列傳行
select name,'語(yǔ)文' as course, chinese as 'score' from test2 union 
select name,'數(shù)學(xué)' as course, math as 'score' from test2 union 
select name,'英語(yǔ)' as course, english as 'score' from test2 union 
select name,'物理' as course, wuli as 'score' from test2 union 
select name,'化學(xué)' as course, huaxue as 'score' from test2 order by name asc

-- 第二種寫法可以對(duì)null值結(jié)果集處理
select * from
(select name,'語(yǔ)文' as course, chinese as 'score' from test2 union 
select name,'數(shù)學(xué)' as course, math as 'score' from test2 union 
select name,'英語(yǔ)' as course, english as 'score' from test2 union 
select name,'物理' as course, wuli as 'score' from test2 union 
select name,'化學(xué)' as course, huaxue as 'score' from test2)a
where a.score is not null order by name asc

第一種結(jié)果：

有null結(jié)果：