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;
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 是这个需求么?
借用楼上的数据 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 <= 16union select to_char(t.age) a,count(1) from tmp_zt_test1 t group by t.age having t.age between 17 and 20union select '>= 21' a,count(1) from tmp_zt_test1 t where t.age >= 21 order by a
好典型的case when 行转列
select ..... from ( select age from ( select rownum age from dual connect by level <= 21) where age >=16 and age <=21) A, T B where ......
楼主可以考虑用这个: 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;
group by 年龄
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;
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
是这个需求么?
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 <= 16union select to_char(t.age) a,count(1)
from tmp_zt_test1 t
group by t.age
having t.age between 17 and 20union select '>= 21' a,count(1)
from tmp_zt_test1 t
where t.age >= 21
order by a
from (
select age from (
select rownum age from dual connect by level <= 21)
where age >=16 and age <=21) A,
T B
where ......
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;