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