请教二次分组统计查询应该怎么写？

[xhzlhc]

表 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 同样不行 哎郁闷

[xhzlhc]

多谢多谢2位 太感谢了

[ssqtjffcu]

 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

[小海葵1]

with test as (
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