Oracle 行列转换

因为数据是Oracle,所以可以使用Decode函数.我的表结构是这样的:

id	gender	age
1	boy	22
2	boy	23
3	girl	20

现在需求是按照性别分组,统计出年龄段的记录来

 SELECT   gender, SUM (20age) AS age20, SUM (21age) AS age21,
         SUM (22age) AS age22, SUM (23age) AS age23 , SUM (24age) AS age24
    FROM (SELECT   gender, DECODE (age, 20, COUNT (*), 0) AS 20age,
                   DECODE (age, 21, COUNT (*), 0) AS 21age,
                   DECODE (age, 22, COUNT (*), 0) AS 22age,
                   DECODE (age, 23, COUNT (*), 0) AS 23age,
                   DECODE (age, 24, COUNT (*), 0) AS 24age
              FROM USERS
          GROUP BY gender)
GROUP BY gender

这样结果就是:

gender	age20	age21	age22	age23	age24
boy	123	64	32	18	53
girl	56	23	55	123	26

很好用...

评论

1 楼 snoopy3384 2010-06-22  

明了，易懂