Oracle、MySQL实现行列互转

最新推荐文章于 2023-07-13 19:15:00 发布

张小邪倒斗中

最新推荐文章于 2023-07-13 19:15:00 发布

阅读量690

点赞数 2

分类专栏： Oracle-语法文章标签： oracle mysql

本文链接：https://blog.csdn.net/weixin_40649680/article/details/124175481

版权

Oracle-语法专栏收录该内容

4 篇文章 0 订阅

订阅专栏

Oracle

Oracle可使用转置函数：unpiovt、pivot。

一、列转行：unpiovt

1、构建表

create table score(
姓名 varchar2(10),
语文 integer,
数学 integer,
英语 integer
);
 
insert into score values ('张三',91,92,93);
insert into score values ('李四',80,81,null);
insert into score values ('王五',70,78,80); 
commit ;

select * from score ;

在这里插入图片描述

2、列转行

2.1 NULL值不转

-- 不含null列
select 姓名,科目,成绩 from score
unpivot ( 成绩 for 科目 in ( 语文, 数学, 英语 ) ) ;

在这里插入图片描述
2.2 NULL值转

-- 含null列
select 姓名,科目,成绩 from score
unpivot include nulls ( 成绩 for 科目 in ( 语文, 数学, 英语 ) ) ;

在这里插入图片描述

二、行转列：piovt

1、构建表

create table city(
城市 varchar2(10),
年月 char(6),
数量 INTEGER
);
 
insert into city values ('北京','202001',1000);
insert into city values ('北京','202004',1023);
insert into city values ('北京','202007',1980);
insert into city values ('北京','202010',1098);
insert into city values ('北京','202101',988);
insert into city values ('北京','202104',976);
insert into city values ('北京','202107',1098);
insert into city values ('北京','202110',1221);
insert into city values ('上海','202001',1222);
insert into city values ('上海','202004',800);
insert into city values ('上海','202007',908);
insert into city values ('上海','202010',1009);
insert into city values ('上海','202101',709);
insert into city values ('上海','202104',799);
insert into city values ('上海','202107',980);
insert into city values ('上海','202110',897);

commit ;

select * from city ;

在这里插入图片描述

2、行转列

-- 行转列(Oracle列名不能以数字开头)
select * from city 
pivot(
    SUM(数量) for 年月 in (
        '202001' as Q202001,
        '202004' as Q202004,
        '202007' as Q202007,
        '202010' as Q202010,
        '202101' as Q202101,
        '202104' as Q202104,
        '202107' as Q202107,
        '202110' as Q202110
        )
    ) ;

在这里插入图片描述
也有动态行转列的语法，用于列名不固定的情况，但是生成的是xml格式，后续还需处理。

MySQL

MySQL无unpiovt、pivot这两个函数，unpiovt 用 union all 实现，pivot用聚合函数+ case when/if 等判断语句实现。

一、列转行：用 union all 实现

SELECT 姓名,'语文' AS 科目,语文 AS 成绩 FROM score 
UNION ALL
SELECT 姓名,'数学' AS 科目,数学 AS 成绩 FROM score 
UNION ALL
SELECT 姓名,'英语' AS 科目,英语 AS 成绩 FROM score ;

在这里插入图片描述

二、行转列：用聚合函数+ case when/if 等判断语句实现

SELECT
  城市,
  SUM(IF(年月 = '202001', 数量, 0)) AS Q202001,
  SUM(IF(年月 = '202004', 数量, 0)) AS Q202004,
  SUM(IF(年月 = '202007', 数量, 0)) AS Q202007,
  SUM(IF(年月 = '202010', 数量, 0)) AS Q202010,
  SUM(IF(年月 = '202101', 数量, 0)) AS Q202101,
  SUM(IF(年月 = '202104', 数量, 0)) AS Q202104,
  SUM(IF(年月 = '202107', 数量, 0)) AS Q202107,
  SUM(IF(年月 = '202110', 数量, 0)) AS Q202110
FROM
  city
GROUP BY 城市;