因为数据是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 |
很好用...