SQL行列转换超详细四种方法详解

时间:2022-12-14 楚生辉人气:0

前言

本文详细的介绍了多个方法实现列转行，行转列，并提供了案例的材料，有需要的小伙伴可以自行获取与学习~

数据准备

 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

然后我们只需要对上述的结果，挑选出我们想要的数据即可

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 不可以省略)。他们所得的结果中，同名且属性相同的字段只显示一个。

对于自然连接而言，连接两个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：会将两个结果集进行并集处理，不包括重复的行；

union all：对两个结果集进行并集处理，包括重复行。

日常开发中，能使用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');

此时，我们发现目前的sql查询出来会有很多重复的行，但由于其他科目没有的数据都是0，我们可以根据id进行分组，然后sum()聚合相加一下，这样就能得到我们想要的结果

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

以上都是列转行，反过来思路也大致一样就可以实现从行转列

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.经典sum+if

思路：由多行变为一行，自然而然的就要想要对id进行groupby聚合，在此基础上，我们还需要根据课程名词去筛选课程成绩，因此还需要再添加一个if函数作为筛选(用case when)也可以，如果if符合条件，就设置本课程的分数，如果不符合条件，就设置为null，最后我们再通过一个sum聚合函数提取成绩即可

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聚合一下就可以实现我们的需求

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

总结

加载全部内容