快捷導(dǎo)航

SQL行列轉(zhuǎn)換超詳細(xì)四種方法詳解

更新時(shí)間：2022年12月14日 10:06:06 作者：楚生輝

在數(shù)據(jù)分析的面試中SQL問(wèn)題基本上是必問(wèn)的,其中SQL行列轉(zhuǎn)換的問(wèn)題出鏡率極其高,重要性也是不言而喻,下面這篇文章主要給大家介紹了關(guān)于SQL行列轉(zhuǎn)換超詳細(xì)四種方法的相關(guān)資料,需要的朋友可以參考下

前言

本文詳細(xì)的介紹了多個(gè)方法實(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';

我們把其他幾門科目的成績(jī)查出來(lái)后當(dāng)做臨時(shí)表再使用join不就解決了該問(wèn)題嗎？！而連接條件便是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

然后我們只需要對(duì)上述的結(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.自然拼接

自動(dòng)的尋找2表中的（所有）同名且屬性相同的列作為連接條件。使用natural join子句來(lái)完成。

例如：A表中有列a，b，c，d B表中有a，b，x，z

自然連接會(huì)將A.a=B.a and A.b=B.b 作為連接條件

select * from A natural join B (natural 不可以省略)。他們所得的結(jié)果中，同名且屬性相同的字段只顯示一個(gè)。

對(duì)于自然連接而言，連接兩個(gè)table之后，兩個(gè)table共用的屬性就會(huì)合并在一起。如果連個(gè)table沒(méi)有共有的屬性，則進(jìn)行笛卡爾乘積，也就是進(jìn)行兩兩相乘，如果table 1有3行，table 2有4行，自然連接后就有12行。自然連接的語(yǔ)法如下：

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：會(huì)將兩個(gè)結(jié)果集進(jìn)行并集處理，不包括重復(fù)的行；

union all：對(duì)兩個(gè)結(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');

此時(shí)，我們發(fā)現(xiàn)目前的sql查詢出來(lái)會(huì)有很多重復(fù)的行，但由于其他科目沒(méi)有的數(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)行，反過(guò)來(lái)思路也大致一樣就可以實(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)橐恍?，自然而然的就要想要?duì)id進(jìn)行g(shù)roupby聚合，在此基礎(chǔ)上，我們還需要根據(jù)課程名詞去篩選課程成績(jī)，因此還需要再添加一個(gè)if函數(shù)作為篩選(用case when)也可以，如果if符合條件，就設(shè)置本課程的分?jǐn)?shù)，如果不符合條件，就設(shè)置為null，最后我們?cè)偻ㄟ^(guò)一個(gè)sum聚合函數(shù)提取成績(jī)即可

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有異曲同工之妙，只不過(guò)這一次是通過(guò)if判斷自動(dòng)的設(shè)置為null，我們只需要在此基礎(chǔ)上，對(duì)id進(jìn)行分組，再添加一個(gè)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