目录
- 一、准备工作
- 二、行转列
- 三、列转行
- 四、特殊的列转行/行转列
- 总结
一、准备工作
# 创建数据表
CREATE TABLE ChengJi
(
Name varchar(32),
Subject varchar(32),
Result int(10)
);
# 插入数据
insert into ChengJi
values ('张三', '语文', 80),
('张三', '数学', 90),
('张三', '物理', 85),
('李四', '语文', 85),
('李四', '数学', 92),
('李四', '物理', 82);
二、行转列
整体分两步走
1、先预处理数据,将数据进行初步的行转列,便于后续的分组处理
select Name,
case
when Subject = '语文' then Result else 0
end as 'Chinese',
case
when Subject = '数学' then Result else 0
end as 'Math',
case
when Subject = '物理' then Result else 0
end as 'Pha'
from ChengJi;
2、对预处理完毕的数据进行分组聚合,使多行数据汇聚到一个组内,达到数据集中的结果,这其中要注意的一点是:要明确按照哪个字段进行聚合操作。
with t1 as(select Name,
case
when Subject = '语文' then Result else 0
end as 'Chinese',
case
when Subject = '数学' then Result else 0
end as 'Math',
case
when Subject = '物理' then Result else 0
end as 'Pha'
from ChengJi)
select Name,
sum(Chinese) as 'Chinese',
sum(Math) as 'Math',
sum(Pha) as 'Pha'
from t1
group by Name;
三、列转行
为便于理解,我们将刚才已经转置好的结果插入到一个结果表内
1、创建一个结果表
create table ChengJi_2(
Name varchar(255),
Chinese int,
Math int,
Pha int
);
2、将行转列结果插入到结果表
insert into chengji_2
with t1 as(select Name,
case
when Subject = '语文' then Result else 0
end as 'Chinese',
case
when Subject = '数学' then Result else 0
end as 'Math',
case
when Subject = '物理' then Result else 0
end as 'Pha'
from ChengJi)
select Name,
sum(Chinese) as 'Chinese',
sum(Math) as 'Math',
sum(Pha) as 'Pha'
from t1
group by Name
;
3、对结果表进行列转行的操作,列转行相对于行转列较为简单,可直接使用 union all 进行操作。
select Name,Chinese from ChengJi_2
union all
select Name,Math from ChengJi_2
union all
select Name,Pha from ChengJi_2;
四、特殊的列转行/行转列
但是对于一些特殊的行列转置,以上方法就不再使用,通常情况下,我们的行列转置是有可以进行分组聚合操作可以完成的,而生产实践中也多数如此,但是有时有一些特殊的操作是以上方法无法完成的,这就需要一些特殊的行列转置来完成,对此,我给出了以下的方案。
1、准备工作,创建数据表并插入数据
CREATE TABLE 2003a
(
seat varchar(255) ,
status varchar(255) ,
rowid varchar(255)
)
;
INSERT INTO 2003a
VALUES ('2', '已预订', 'A');
INSERT INTO 2003a
VALUES ('3', '未预订', 'A');
INSERT INTO 2003a
VALUES ('4', '未预订', 'A');
INSERT INTO 2003a
VALUES ('5', '未预订', 'A');
INSERT INTO 2003a
VALUES ('6', '未预订', 'B');
INSERT INTO 2003a
VALUES ('7', '未预订', 'B');
INSERT INTO 2003a
VALUES ('8', '未预订', 'B');
INSERT INTO 2003a
VALUES ('9', '未预订', 'B');
INSERT INTO 2003a
VALUES ('10', '未预订', 'B');
INSERT INTO 2003a
VALUES ('11', '未预订', 'C');
INSERT INTO 2003a
VALUES ('12', '已预订', 'C');
INSERT INTO 2003a
VALUES ('13', '已预订', 'C');
INSERT INTO 2003a
VALUES ('14', '未预订', 'C');
INSERT INTO 2003a
VALUES ('15', '未预订', 'C');
INSERT INTO 2003a
VALUES ('16', '未预订', 'D');
INSERT INTO 2003a
VALUES ('17', '未预订', 'D');
INSERT INTO 2003a
VALUES ('18', '未预订', 'D');
INSERT INTO 2003a
VALUES ('19', '未预订', 'D');
INSERT INTO 2003a
VALUES ('20', '已预订', 'D');
2、明确需求
原有表的结构:
2,已预订,A
3,未预订,A
需要完成的工作:
2,3
已预定,未预定
A,A
在这里我们可以很明显的看出,我们需要做的就是如何进行 行转列/列转行 的操作,在这里的行列转置是整行/整列进行转置,不再是依靠某个字段进行分组处理或者使用 union all 进行整体操作,因此,我是用以下方案来完成。
1、对原表字段进行 group_concat,指定 “,”为字段值之间的分隔符
SELECT
GROUP_CONCAT(rowid ORDER BY rowid ASC SEPARATOR ', ') AS rowid,
GROUP_CONCAT(status ORDER BY rowid ASC SEPARATOR ', ') AS status,
GROUP_CONCAT(seat ORDER BY rowid ASC SEPARATOR ', ') AS seat
FROM (
SELECT rowid, status, seat from `2003a`
) AS subquery
2、将所有的字段按照值聚合到一个数据表格内之后,我们可以使用 union all 来进行字段拆分
with t1 as (SELECT
GROUP_CONCAT(rowid ORDER BY rowid ASC SEPARATOR ', ') AS rowid,
GROUP_CONCAT(status ORDER BY rowid ASC SEPARATOR ', ') AS status,
GROUP_CONCAT(seat ORDER BY rowid ASC SEPARATOR ', ') AS seat
FROM (
SELECT rowid, status, seat from `2003a`
) AS subquery)
select seat from t1
union all
select status from t1
union all
select rowid from t1
3、使用 SUBSTRING_INDEX来进行拆分,将所有的字段值拆分成单独的值
with t1 as (SELECT
GROUP_CONCAT(rowid ORDER BY rowid ASC SEPARATOR ', ') AS rowid,
GROUP_CONCAT(status ORDER BY rowid ASC SEPARATOR ', ') AS status,
GROUP_CONCAT(seat ORDER BY rowid ASC SEPARATOR ', ') AS seat
FROM (
SELECT rowid, status, seat from `2003a`
) AS subquery)
,t2 as (select seat from t1
union all
select status from t1
union all
select rowid from t1)
select SUBSTRING_INDEX(seat,',',1) as p1,
SUBSTRING_INDEX(SUBSTRING_INDEX(seat,',',2),',',-1) as p2,
SUBSTRING_INDEX(SUBSTRING_INDEX(seat,',',3),',',-1) as p3,
SUBSTRING_INDEX(SUBSTRING_INDEX(seat,',',4),',',-1) as p4,
SUBSTRING_INDEX(SUBSTRING_INDEX(seat,',',5),',',-1) as p5,
SUBSTRING_INDEX(SUBSTRING_INDEX(seat,',',6),',',-1) as p6,
SUBSTRING_INDEX(SUBSTRING_INDEX(seat,',',7),',',-1) as p7,
SUBSTRING_INDEX(SUBSTRING_INDEX(seat,',',8),',',-1) as p8,
SUBSTRING_INDEX(SUBSTRING_INDEX(seat,',',9),',',-1) as p9,
SUBSTRING_INDEX(SUBSTRING_INDEX(seat,',',10),',',-1) as p10,
SUBSTRING_INDEX(SUBSTRING_INDEX(seat,',',11),',',-1) as p11,
SUBSTRING_INDEX(SUBSTRING_INDEX(seat,',',12),',',-1) as p12,
SUBSTRING_INDEX(SUBSTRING_INDEX(seat,',',13),',',-1) as p13,
SUBSTRING_INDEX(SUBSTRING_INDEX(seat,',',14),',',-1) as p14,
SUBSTRING_INDEX(SUBSTRING_INDEX(seat,',',15),',',-1) as p15,
SUBSTRING_INDEX(SUBSTRING_INDEX(seat,',',16),',',-1) as p16,
SUBSTRING_INDEX(SUBSTRING_INDEX(seat,',',17),',',-1) as p17,
SUBSTRING_INDEX(SUBSTRING_INDEX(seat,',',18),',',-1) as p18,
SUBSTRING_INDEX(SUBSTRING_INDEX(seat,',',19),',',-1) as p19
from t2
在这里需要注意的是:第一个SUBSTRING_INDEX我们取的是源数据的第一个值,第二个SUBSTRING_INDEX以及之后的,我们取得是源数据的倒数第一个值,因此这里需要注意一下我们给到的是“-1”
至此,我们使用group_concat()以及SUBSTRING_INDEX()来达到了特殊的行列转置操作。
总结
到此这篇关于SQL行列转置以及非常规的行列转置的文章就介绍到这了,更多相关SQL行列转置内容请搜索编程网(www.lsjlt.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.lsjlt.com)!
免责声明:
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
软考中级精品资料免费领
- 历年真题答案解析
- 备考技巧名师总结
- 高频考点精准押题
- 资料下载
- 历年真题
193.9 KB下载数265
191.63 KB下载数245
143.91 KB下载数1148
183.71 KB下载数642
644.84 KB下载数2756
相关文章
发现更多好内容- 如何在 Java 中向数据库添加一条数据?(java怎么向数据库添加一条数据)
- Uncomtrade数据库异地备份指南
- CORS 在微服务架构中的应用场景有哪些?(cors在微服务架构中的应用场景)
- 如何在 Java 中创建 Maven 项目?(java怎么创建maven项目)
- 如何高效使用Uncomtrade数据库进行查询
- Java 配置环境变量后如何进行使用操作?(java配置环境变量后如何使用)
- Java 中防止接口重复提交的方法有哪些?(java接口防止重复提交的方法是什么)
- 如何提高 Java Office 的性能?(java office 怎样提高性能 )
- Java 抽象工厂模式的优缺点有哪些?(java抽象工厂模式有哪些优缺点)
- 在 Java 中,charsequence 的具体用法究竟是什么?(java中charsequence的用法是什么)