Oracle 行转列总结 Case When,Decode,PIVOT 三种方式
发布时间：2020-12-12 14:17:18 所属栏目：百科 来源：网络整理
导读：Oracle 行转列总结 Case When,Decode,PIVOT 三种方式 最近又碰到行专列问题了,当时不假思索用的是子查询,做完后我询问面试管行专列标正的写法应该如何写,他告诉我说应该用"Decode",索性我就总结一下,一共三种方式 --=========建表语句 CREATE TABLE populati
Oracle 行转列总结 Case When,Decode,PIVOT 三种方式最近又碰到行专列问题了,当时不假思索用的是子查询,做完后我询问面试管行专列标正的写法应该如何写,他告诉我说应该用"Decode",索性我就总结一下,一共三种方式 --=========建表语句 CREATE TABLE populations( country VARCHAR2(20),sex INT,populcation INT ); --=========插入记录 insert into populations (COUNTRY,SEX,POPULCATION) values ('中国 ',1,340); insert into populations (COUNTRY,2,260); insert into populations (COUNTRY,POPULCATION) values ('美国 ',45); insert into populations (COUNTRY,55); insert into populations (COUNTRY,POPULCATION) values ('加拿大',51); insert into populations (COUNTRY,49); insert into populations (COUNTRY,POPULCATION) values ('英国 ',40); insert into populations (COUNTRY,60); commit; --=========三种方式 -------------------------------------- Decode 方式 SELECT a.COUNTRY AS "国家",SUM(DECODE(a.SEX,a.POPULCATION)) AS "男",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 "女"))（编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |