SQL行列轉(zhuǎn)換超詳細(xì)四種方法詳解
前言
本文詳細(xì)的介紹了多個方法實(shí)現(xiàn)列轉(zhuǎn)行,行轉(zhuǎn)列,并提供了案例的材料,有需要的小伙伴可以自行獲取與學(xué)習(xí)~
數(shù)據(jù)準(zhǔn)備
CREATE TABLE `score` ( `id` varchar(255), `subject` char(10), `score` int ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `score`(`id`,`subject`,`score`) values ('1','MATH',90),('1','ENGLISH',98),('1','CHINESE',85),('2','MATH',87),('2','ENGLISH',78),('2','CHINESE',89);

1.使用join拼接
SELECT id,score as 'MATH' FROM score WHERE subject = 'MATH';
我們把其他幾門科目的成績查出來后當(dāng)做臨時表再使用join不就解決了該問題嗎?!而連接條件便是std??吹竭@,大家可以自己試一試。完整代碼如下:
SELECT * FROM ( SELECT id, score AS 'MATH' FROM score WHERE subject = 'MATH' ) AS t1 JOIN ( SELECT id, score FROM score WHERE subject = 'ENGLISH' ) AS t2 ON t1.id = t2.id JOIN ( SELECT id, score FROM score WHERE subject = 'CHINESE' ) AS t3 ON t1.id = t3.id

然后我們只需要對上述的結(jié)果,挑選出我們想要的數(shù)據(jù)即可
SELECT t1.id, t1.MATH, t2.score AS 'ENGLISH',t3.score AS 'CHINESE' FROM ( SELECT id, score AS 'MATH' FROM score WHERE subject = 'MATH' ) AS t1 JOIN ( SELECT id, score FROM score WHERE subject = 'ENGLISH' ) AS t2 ON t1.id = t2.id JOIN ( SELECT id, score FROM score WHERE subject = 'CHINESE' ) AS t3 ON t1.id = t3.id

2.自然拼接
自動的尋找2表中的(所有)同名且屬性相同的列作為連接條件。使用natural join子句來完成。
例如:A表中有列a,b,c,d B表中有a,b,x,z
自然連接會將A.a=B.a and A.b=B.b 作為連接條件
select * from A natural join B (natural 不可以省略)。他們所得的結(jié)果中,同名且屬性相同的字段只顯示一個。
對于自然連接而言,連接兩個table之后,兩個table共用的屬性就會合并在一起。如果連個table沒有共有的屬性,則進(jìn)行笛卡爾乘積,也就是進(jìn)行兩兩相乘,如果table 1有3行,table 2有4行,自然連接后就有12行。自然連接的語法如下:
SELECT * FROM ( SELECT id, score AS 'MATH' FROM score WHERE subject = 'MATH' ) AS t1 NATURAL JOIN ( SELECT id, score AS 'ENGLISH' FROM score WHERE SUBJECT = 'ENGLISH' ) AS t2 NATURAL JOIN ( SELECT id, score AS 'CHINESE' FROM score WHERE SUBJECT = 'CHINESE' ) AS t3
???? ????????????
3.使用union拼接
union:會將兩個結(jié)果集進(jìn)行并集處理,不包括重復(fù)的行;
union all:對兩個結(jié)果集進(jìn)行并集處理,包括重復(fù)行。
日常開發(fā)中,能使用union all就使用union all
SELECT id,score AS 'MATH',0 AS 'ENGLISH',0 AS 'CHINESE' FROM score WHERE subject = 'MATH';

(SELECT id,score AS 'MATH',0 AS 'ENGLISH',0 AS 'CHINESE' FROM score WHERE subject = 'MATH') UNION ALL (SELECT id,0 AS 'MATH',score AS 'ENGLISH',0 AS 'CHINESE' FROM score WHERE subject = 'ENGLISH') UNION ALL (SELECT id,0 AS 'MATH',0 AS 'ENGLISH',score AS 'CHINESE' FROM score WHERE subject = 'CHINESE');

此時,我們發(fā)現(xiàn)目前的sql查詢出來會有很多重復(fù)的行,但由于其他科目沒有的數(shù)據(jù)都是0,我們可以根據(jù)id進(jìn)行分組,然后sum()聚合相加一下,這樣就能得到我們想要的結(jié)果
select id,SUM(MATH) AS 'MATH',SUM(ENGLISH) AS 'ENGLISH',SUM(CHINESE)AS CHINESE from ( (SELECT id,score AS 'MATH',0 AS 'ENGLISH',0 AS 'CHINESE' FROM score WHERE subject = 'MATH') UNION ALL (SELECT id,0 AS 'MATH',score AS 'ENGLISH',0 AS 'CHINESE' FROM score WHERE subject = 'ENGLISH') UNION ALL (SELECT id,0 AS 'MATH',0 AS 'ENGLISH',score AS 'CHINESE' FROM score WHERE subject = 'CHINESE')) t GROUP BY id
以上都是列轉(zhuǎn)行,反過來思路也大致一樣就可以實(shí)現(xiàn)從行轉(zhuǎn)列
SELECT id, 'MATH' subject, MATH score FROM products WHERE MATH IS NOT NULL UNION SELECT id, 'ENGLISH' subject, ENGLISH score FROM products WHERE ENGLISH IS NOT NULL UNION SELECT id, 'CHINESE' subject, CHINESE score FROM products WHERE CHINESE IS NOT NULL;
4.經(jīng)典sum+if
思路:由多行變?yōu)橐恍?,自然而然的就要想要對id進(jìn)行g(shù)roupby聚合,在此基礎(chǔ)上,我們還需要根據(jù)課程名詞去篩選課程成績,因此還需要再添加一個if函數(shù)作為篩選(用case when)也可以,如果if符合條件,就設(shè)置本課程的分?jǐn)?shù),如果不符合條件,就設(shè)置為null,最后我們再通過一個sum聚合函數(shù)提取成績即可
SELECT id,
if(subject='MATH', score, NULL) as `MATH`,
if(subject='ENGLISH', score, NULL) as `ENGLISH`,
if(subject='CHINESE', score, NULL) as `CHINESE`
FROM score

該步驟與上面union中自己設(shè)置0有異曲同工之妙,只不過這一次是通過if判斷自動的設(shè)置為null,我們只需要在此基礎(chǔ)上,對id進(jìn)行分組,再添加一個sum聚合一下就可以實(shí)現(xiàn)我們的需求
SELECT id,
sum(if(subject='MATH', score, NULL)) as `MATH`,
sum(if(subject='ENGLISH', score, NULL)) as `ENGLISH`,
sum(if(subject='CHINESE', score, NULL)) as `CHINESE`
FROM score
GROUP BY id

總結(jié)
到此這篇關(guān)于SQL行列轉(zhuǎn)換超詳細(xì)四種方法的文章就介紹到這了,更多相關(guān)SQL行列轉(zhuǎn)換方法內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
- sql 普通行列轉(zhuǎn)換
- 一個簡單的SQL 行列轉(zhuǎn)換語句
- sqlserver2005 行列轉(zhuǎn)換實(shí)現(xiàn)方法
- Sql實(shí)現(xiàn)行列轉(zhuǎn)換方便了我們存儲數(shù)據(jù)和呈現(xiàn)數(shù)據(jù)
- 玩轉(zhuǎn)-SQL2005數(shù)據(jù)庫行列轉(zhuǎn)換
- 深入SQL中PIVOT 行列轉(zhuǎn)換詳解
- PostgreSQL實(shí)現(xiàn)交叉表(行列轉(zhuǎn)換)的5種方法示例
- sql server通過pivot對數(shù)據(jù)進(jìn)行行列轉(zhuǎn)換的方法
- SQL Server 使用 Pivot 和 UnPivot 實(shí)現(xiàn)行列轉(zhuǎn)換的問題小結(jié)
- SQL Server使用PIVOT與unPIVOT實(shí)現(xiàn)行列轉(zhuǎn)換
- MySQL實(shí)現(xiàn)行列轉(zhuǎn)換
- SQL Server行列轉(zhuǎn)換的實(shí)現(xiàn)示例
- SQLServer使用 PIVOT 和 UNPIVOT行列轉(zhuǎn)換
相關(guān)文章
win10下mysql 5.7.23 winx64安裝配置方法圖文教程
這篇文章主要為大家詳細(xì)介紹了win10下mysql 5.7.23 winx64安裝配置方法圖文教程,具有一定的參考價值,感興趣的小伙伴們可以參考一下2018-09-09
mysql用戶創(chuàng)建與授權(quán)的簡單實(shí)例
MySQL等主流數(shù)據(jù)庫的最高權(quán)限一般是root用戶,有時我們需要提供數(shù)據(jù)庫的賬號和密碼以使用某些服務(wù),下面這篇文章主要給大家介紹了關(guān)于mysql用戶創(chuàng)建與授權(quán)的相關(guān)資料,需要的朋友可以參考下2022-03-03
mysql 修改密碼和設(shè)置允許遠(yuǎn)程登錄
這篇文章主要介紹了mysql 修改密碼和設(shè)置允許遠(yuǎn)程登錄的相關(guān)資料,需要的朋友可以參考下2015-07-07
SQL優(yōu)化老出錯,那是你沒弄明白MySQL解釋計(jì)劃用法
本篇文章講的是SQL優(yōu)化老出錯,那是你沒弄明白MySQL解釋計(jì)劃用法,有興趣的小伙伴速度來看看吧,希望本篇文章能夠幫助到你2021-11-11
MySQL數(shù)據(jù)庫之?dāng)?shù)據(jù)表操作
這篇文章主要介紹了MySQL數(shù)據(jù)庫之?dāng)?shù)據(jù)表操作,文章基于MySQL數(shù)據(jù)庫的相關(guān)資料展開詳細(xì)的數(shù)據(jù)表操作的詳情,具有一定的參考價值,需要的小伙伴可以參考一下2022-05-05

