最近又碰到行专列问题了,当时不假思索用的是子查询,做完后我询问面试管行专列标正的写法应该如何写,他告诉我说应该用"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 "女"))