表 person_master_index 字段有 age,sex,name.................
现在要统计成 这种
年龄段 男人数 女人数 总数
0-9 岁
10-19岁
.
.
.
.
.
我这么写不行啊select a.age_group ,
count(*) ,
( select count(*) from
(select case age between 0 and 9 ...... end age_group ,sex from person_master_index)b
where b.age_group = a.age_group and b.sex = '男' ) man,
( select count(*) from
(select case age between 0 and 9 ...... end age_group ,sex from person_master_index)d
where d.age_group = a.age_group and b.sex = '女' ) woman
from
(select case age between 0 and 9 ...... end age_group ,sex from person_master_index) a
group by age_group
我试过如果(select case age between 0 and 9 ...... end age_group ,sex from person_master_index)这段语句换成一张实际存在的表 而不是view的话就没问题
可能是oracle不支持view的分组查询了吧以为我把(select case age between 0 and 9 ...... end age_group ,sex from person_master_index)创建为view 然后用这个view来写sql 同样不行 哎郁闷
现在要统计成 这种
年龄段 男人数 女人数 总数
0-9 岁
10-19岁
.
.
.
.
.
我这么写不行啊select a.age_group ,
count(*) ,
( select count(*) from
(select case age between 0 and 9 ...... end age_group ,sex from person_master_index)b
where b.age_group = a.age_group and b.sex = '男' ) man,
( select count(*) from
(select case age between 0 and 9 ...... end age_group ,sex from person_master_index)d
where d.age_group = a.age_group and b.sex = '女' ) woman
from
(select case age between 0 and 9 ...... end age_group ,sex from person_master_index) a
group by age_group
我试过如果(select case age between 0 and 9 ...... end age_group ,sex from person_master_index)这段语句换成一张实际存在的表 而不是view的话就没问题
可能是oracle不支持view的分组查询了吧以为我把(select case age between 0 and 9 ...... end age_group ,sex from person_master_index)创建为view 然后用这个view来写sql 同样不行 哎郁闷
with t as(
select 3 age,'0' sex,'a' name from dual
union all
select 45,'1','b' from dual
union all
select 32,'0','c' from dual
union all
select 12,'0','d' from dual
union all
select 56,'1','e' from dual
union all
select 22,'1','f' from dual
)
select decode(trunc(age / 10),
0,
'0-9岁',
1,
'10-19岁',
2,
'20-29岁',
3,
'30-39岁',
4,
'40-49岁',
5,
'50-59岁',
'其它') "年龄段",
sum(sex) "男人数",
sum(decode(sex, 0, 1, 1, 0)) "女人数",
count(sex) "总数"
from t
group by trunc(age / 10) order by trunc(age / 10);
年龄段 男人数 女人数 总数
------- ---------- ---------- ----------
0-9岁 0 1 1
10-19岁 0 1 1
20-29岁 1 0 1
30-39岁 0 1 1
40-49岁 1 0 1
50-59岁 1 0 1
6 rows selected
select '0' as age, '男' as sex from dual
union all
select '9' as age, '男' as sex from dual
union all
select '0' as age, '女' as sex from dual
union all
select '9' as age, '女' as sex from dual
union all
select '10' as age, '男' as sex from dual
union all
select '19' as age, '男' as sex from dual
union all
select '10' as age, '女' as sex from dual
union all
select '19' as age, '女' as sex from dual
union all
select '20' as age, '男' as sex from dual
union all
select '29' as age, '女' as sex from dual
)
SELECT NLD,
MAX(DECODE(sex, '男', SEXC, '')) AS NANRS,
MAX(DECODE(sex, '女', SEXC, '')) AS NVRS,
SUM(SEXC) AS ZS
FROM (SELECT NLD, sex, COUNT(SEX) AS SEXC
FROM (SELECT CASE
WHEN age >= 0 AND age <= 9 THEN
'0-9歳'
WHEN age >= 10 AND age <= 19 THEN
'10-19歳'
WHEN age >= 20 AND age <= 29 THEN
'20-29歳'
END AS NLD,
sex
FROM TEST)
GROUP BY NLD, SEX
ORDER BY NLD)
GROUP BY NLD
ORDER BY NLD
1 0-9歳 2 2 4
2 10-19歳 2 2 4
3 20-29歳 1 1 2