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

更新時間：2024年11月05日 09:06:26 作者：江南春失憶夢i

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

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

行轉(zhuǎn)列

創(chuàng)建語句：

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 ('張三','數(shù)學',100);
insert into test1(name,course,score) values ('張三','英語',82);
insert into test1(name,course,score) values ('李四','語文',89);
insert into test1(name,course,score) values ('李四','數(shù)學',99);
insert into test1(name,course,score) values ('李四','英語',87);
insert into test1(name,course,score) values ('王五','語文',78);
insert into test1(name,course,score) values ('王五','數(shù)學',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);

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

1、第一種方法：

-- 使用case when then else，這里使用sum函數(shù)也可以
select name,
max(case course when '語文' then score else 0 end)as 'chinese',
max(case course when '數(shù)學' 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

第一種結(jié)果：

2、第二種方法：

-- 使用if語句，這里使用sum函數(shù)也可以
select name,
max(if(course = '語文',score,0))as 'chinese',
max(if(course = '數(shù)學',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

第二種結(jié)果：

3、第三種方法：

-- 動態(tài)拼接sql語句，不管多少行都會轉(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;  -- 動態(tài)生成腳本，預備一個語句
execute stmt;            -- 動態(tài)執(zhí)行腳本，執(zhí)行預備的語句
deallocate prepare stmt; -- 釋放預備的語句 

-- 通過這個查詢拼接的sql
select @sql

第三種結(jié)果：

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

-- 查詢出來的結(jié)果
select name,max(if(a.course = '語文', a.score, 0)) as '語文',max(if(a.course = '數(shù)學', a.score, 0)) as '數(shù)學',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、第四種方法：

-- 使用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='數(shù)學' ) 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

第四種結(jié)果：

5、第五種方法：

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

-- 使用with rollup統(tǒng)計第一種
select ifnull(name,'總計') as name,
max(if(course = '語文',score,0))as 'chinese',
max(if(course = '數(shù)學',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統(tǒng)計第二種
select name,
max(if(course = '語文',score,0))as 'chinese',
max(if(course = '數(shù)學',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 = '數(shù)學',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統(tǒng)計
select ifnull(name,'total') as name,
max(if(course = '語文',score,0))as 'chinese',
max(if(course = '數(shù)學',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

第五種結(jié)果：

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

6、第六種方法：

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

第六種結(jié)果：

7、第七中方法：

-- 這個也不推薦使用
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)建語句：

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可以去除重復結(jié)果集，union all不會去除重復的結(jié)果集

-- 第一種使用union實現(xiàn)列傳行
select name,'語文' as course, chinese as 'score' from test2 union 
select name,'數(shù)學' 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值結(jié)果集處理
select * from
(select name,'語文' as course, chinese as 'score' from test2 union 
select name,'數(shù)學' 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

第一種結(jié)果：

有null結(jié)果：

沒有null結(jié)果：

總結(jié)

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

您可能感興趣的文章:

mysql中使用instr進行模糊查詢方法介紹
這篇文章主要介紹了mysql中使用instr進行模糊查詢方法介紹，具有一定參考價值，需要的朋友可以了解下。
2017-10-10
MySQL查詢隨機數(shù)據(jù)的4種方法和性能對比
從MySQL隨機選取數(shù)據(jù)也是我們最常用的一種發(fā)發(fā)，其最簡單的辦法就是使用”O(jiān)RDER BY RAND()”,本文介紹了包括ORDER BY RAND()的4種獲取隨機數(shù)據(jù)的方法，并分析了各自的優(yōu)缺點。
2014-04-04
詳解mysql索引總結(jié)----mysql索引類型以及創(chuàng)建
索引是一種特殊的文件(InnoDB數(shù)據(jù)表上的索引是表空間的一個組成部分)，它們包含著對數(shù)據(jù)表里所有記錄的引用指針。這篇文章主要介紹了詳解mysql索引總結(jié)----mysql索引類型以及創(chuàng)建，有興趣的可以了解一下。
2016-11-11
Mysql查詢時間區(qū)間日期列表實例代碼
最近常用到mysql的日期范圍搜索,下面這篇文章主要給大家介紹了關于Mysql查詢時間區(qū)間日期列表的相關資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下
2022-04-04
linux 下配置安裝mysql以及配置【經(jīng)驗】
這篇文章主要介紹了linux 下配置安裝mysql以及配置【經(jīng)驗】,需要的朋友可以參考下
2016-05-05
MySQL關閉過程詳解和安全關閉MySQL的方法
這篇文章主要介紹了MySQL關閉過程詳解和安全關閉MySQL的方法,在了解了關閉過程后,出現(xiàn)故障能迅速定位,本文還給出了安全關閉MySQL的建議及方法,需要的朋友可以參考下
2014-08-08
MySQL 中 datetime 和 timestamp 的區(qū)別與選擇
MySQL 中常用的兩種時間儲存類型分別是datetime和 timestamp。如何在它們之間選擇是建表時必要的考慮。下面就談談他們的區(qū)別和怎么選擇,需要的朋友可以參考一下
2021-09-09
Linux mysql命令安裝允許遠程連接的安裝設置方法
對大家推薦很好使用的Linux mysql系統(tǒng)，像讓大家對Linux mysql系統(tǒng)有所了解，然后對Linux mysql系統(tǒng)全面講解介紹，希望對大家有用今天特意配置了mysql apache php ，雖然網(wǎng)上很多這方面的例子，但是很多是作者再回憶寫的，所以難免有筆誤的地方。
2010-08-08
六條比較有用的MySQL數(shù)據(jù)庫操作的SQL語句小結(jié)
本文我們主要介紹了MySQL數(shù)據(jù)庫中的六條比較有用的SQL語句，對于初學者來說，可以直接套用下面的格式即可完成相應的功能，希望本次的介紹能夠?qū)δ兴鶐椭?/div> 2011-08-08
詳談mysqldump數(shù)據(jù)導出的問題
下面小編就為大家?guī)硪黄斦刴ysqldump數(shù)據(jù)導出的問題。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧
2017-03-03