按年龄统计SQL问题

[520NET]

年 龄 人数
16岁及以下
17岁
...
...
...
20岁
21岁及以上

请问SQL怎么写，谢谢！

[dws2004]

select 年龄,count(1) from table
group by 年龄

[salinrong]

select
num_total,
age from
(SELECT
count(id) num_total
,16 as age
FROM tmp_zt_test1
WHERE age<17
union
SELECT
count(id) num_total
,21 as age
FROM tmp_zt_test1
WHERE age>21
union
select
num_total,
age from(
SELECT
count(id) num_total
,age
FROM tmp_zt_test1
where age>16
and age<21
group by age))
order by age;

[salinrong]

create table tmp_zt_test1(id number(4),age number(3));

insert into tmp_zt_test1 values(1,13);
insert into tmp_zt_test1 values(2,23);
insert into tmp_zt_test1 values(3,20);
insert into tmp_zt_test1 values(4,16);
insert into tmp_zt_test1 values(5,17);
insert into tmp_zt_test1 values(6,24);
insert into tmp_zt_test1 values(7,11);
insert into tmp_zt_test1 values(8,18);
insert into tmp_zt_test1 values(9,33);

结果是:
num_total age
3 16
1 17
1 18
1 20
3 21
是这个需求么？

[horizonlyhw]

借用楼上的数据

create table tmp_zt_test1(id number(4),age number(3));

insert into tmp_zt_test1 values(1,13);
insert into tmp_zt_test1 values(2,23);
insert into tmp_zt_test1 values(3,20);
insert into tmp_zt_test1 values(4,16);
insert into tmp_zt_test1 values(5,17);
insert into tmp_zt_test1 values(6,24);
insert into tmp_zt_test1 values(7,11);
insert into tmp_zt_test1 values(8,18);
insert into tmp_zt_test1 values(9,33);



select '<= 16' a,count(1)
from tmp_zt_test1 t
where t.age <= 16

union 

select to_char(t.age) a,count(1)
from tmp_zt_test1 t
group by t.age
having t.age between 17 and 20

union 

select '>= 21' a,count(1)
from tmp_zt_test1 t
where t.age >= 21
order by a

[大明湖畔_帅锅]

这个 只能把年龄段分开查询 应该没啥简便方法能简单的一句搞定

[buryMyLove]

好典型的case when 行转列

[20804036]

select .....
from (
select age from (
select rownum age from dual connect by level <= 21)
where age >=16 and age <=21) A,
T B
where ......

[x_smooth]

楼主可以考虑用这个：
create table tmp_zt_test1(id number(4),age number(3));
insert into tmp_zt_test1 values(1,13);
insert into tmp_zt_test1 values(2,23);
insert into tmp_zt_test1 values(3,20);
insert into tmp_zt_test1 values(4,16);
insert into tmp_zt_test1 values(5,17);
insert into tmp_zt_test1 values(6,24);
insert into tmp_zt_test1 values(7,11);
insert into tmp_zt_test1 values(8,18);
insert into tmp_zt_test1 values(9,33);

select case when age<=16 then '16岁及以下'
when age>=21 then '21岁及以上'
else to_char(age) end age
,count(*) rn
from tmp_zt_test1 group by case when age<=16 then '16岁及以下'
when age>=21 then '21岁及以上'
else to_char(age) end order by 1；