Oracle 行转列总结 Case When,Decode,PIVOT 三种方式

最新推荐文章于 2024-07-04 11:18:58 发布

原创最新推荐文章于 2024-07-04 11:18:58 发布 · 572 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle #行转列 #Case When #Decode #PIVOT

oracle 专栏收录该内容

4 篇文章

订阅专栏

本文介绍了在Oracle数据库中实现行转列的三种方法：使用DECODE函数、CASE WHEN语句及11g版本特有的PIVOT操作。通过具体实例展示了如何将性别分类的数据汇总为独立的“男”、“女”两列。

[size=medium]最近又碰到行专列问题了,当时不假思索用的是子查询,做完后我询问面试管行专列标正的写法应该如何写,他告诉我说应该用"Decode",索性我就总结一下,一共三种方式[/size]

--=========建表语句

CREATE TABLE populations(
country VARCHAR2(20),
sex INT,
populcation INT
);

--=========插入记录

insert into populations (COUNTRY, SEX, POPULCATION)
values ('中国 ', 1, 340);

insert into populations (COUNTRY, SEX, POPULCATION)
values ('中国 ', 2, 260);

insert into populations (COUNTRY, SEX, POPULCATION)
values ('美国 ', 1, 45);

insert into populations (COUNTRY, SEX, POPULCATION)
values ('美国 ', 2, 55);

insert into populations (COUNTRY, SEX, POPULCATION)
values ('加拿大', 1, 51);

insert into populations (COUNTRY, SEX, POPULCATION)
values ('加拿大', 2, 49);

insert into populations (COUNTRY, SEX, POPULCATION)
values ('英国 ', 1, 40);

insert into populations (COUNTRY, SEX, POPULCATION)
values ('英国 ', 2, 60);

commit;

--=========三种方式

--------------------------------------

Decode 方式

SELECT a.COUNTRY AS "国家",
SUM(DECODE(a.SEX, 1, a.POPULCATION)) AS "男",
SUM(DECODE(a.SEX, 2, a.POPULCATION)) AS "女"
FROM populations a
GROUP BY a.COUNTRY

--------------------------------------

Case When 方式

SELECT a.COUNTRY AS "国家",
SUM(CASE
WHEN a.SEX = 1 THEN
a.POPULCATION
END) AS "男",
SUM(CASE
WHEN a.SEX = 2 THEN
a.POPULCATION
END) AS "女"
FROM populations a
GROUP BY a.COUNTRY

--------------------------------------

PIVOT 方式 11G特有的方式

SELECT *
FROM (SELECT a.COUNTRY AS "国家", a.SEX, a.POPULCATION FROM populations a)
PIVOT(
SUM(POPULCATION)
FOR SEX IN(1 AS "男",2 AS "女"))