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

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

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

前言

本文詳細的介紹了多個方法實現(xiàn)列轉(zhuǎn)行，行轉(zhuǎn)列，并提供了案例的材料，有需要的小伙伴可以自行獲取與學習~

數(shù)據(jù)準備

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

我們把其他幾門科目的成績查出來后當做臨時表再使用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沒有共有的屬性，則進行笛卡爾乘積，也就是進行兩兩相乘，如果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é)果集進行并集處理，不包括重復的行；

union all：對兩個結(jié)果集進行并集處理，包括重復行。

日常開發(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查詢出來會有很多重復的行，但由于其他科目沒有的數(shù)據(jù)都是0，我們可以根據(jù)id進行分組，然后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)行，反過來思路也大致一樣就可以實現(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進行groupby聚合，在此基礎上，我們還需要根據(jù)課程名詞去篩選課程成績，因此還需要再添加一個if函數(shù)作為篩選(用case when)也可以，如果if符合條件，就設置本課程的分數(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中自己設置0有異曲同工之妙，只不過這一次是通過if判斷自動的設置為null，我們只需要在此基礎上，對id進行分組，再添加一個sum聚合一下就可以實現(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