案例驅動
--建表
CREATE TABLE btr.btr.populations(
country VARCHAR2(20),
sex INT,
populcation INT
);
--插入數(shù)據(jù)
insert into btr.populations (COUNTRY, SEX, POPULCATION)
values ('中國 ', 1, 340);
insert into btr.populations (COUNTRY, SEX, POPULCATION)
values ('中國 ', 2, 260);
insert into btr.populations (COUNTRY, SEX, POPULCATION)
values ('美國 ', 1, 45);
insert into btr.populations (COUNTRY, SEX, POPULCATION)
values ('美國 ', 2, 55);
insert into btr.populations (COUNTRY, SEX, POPULCATION)
values ('加拿大', 1, 51);
insert into btr.populations (COUNTRY, SEX, POPULCATION)
values ('加拿大', 2, 49);
insert into btr.populations (COUNTRY, SEX, POPULCATION)
values ('英國 ', 1, 40);
insert into btr.populations (COUNTRY, SEX, POPULCATION)
values ('英國 ', 2, 60);
--------------------------------------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 btr.populations a
GROUP BY a.COUNTRY
--------------------------------------decode 方式-------------------------------------------------------------
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
--------------------------------------PIVOT 方式 11G特有的方式 ----------------------------------------
SELECT
country,男,女
FROM (
SELECT a.COUNTRY , a.SEX, a.POPULCATION
FROM btr.populations a)
PIVOT( SUM(POPULCATION) FOR SEX IN(1 AS 男 ,2 AS 女 ))
資料來源：http://albb0608.iteye.com/blog/1218737
decode詳解：http://www.reibang.com/p/3097b8bcc901
