table表 中字段id ,name ,birthday(出生日期) ,class(班级)
其中班级是枚举值,1,2,3
查询结果是:
年龄 1 2 3 总计
<15 0 0 0 0
15-20 1 0 0 1
...................
...............
总计 1 0 0 1
其中班级是枚举值,1,2,3
查询结果是:
年龄 1 2 3 总计
<15 0 0 0 0
15-20 1 0 0 1
...................
...............
总计 1 0 0 1
with tt as (
select 1 id, 'zs' name, date'2010-01-01' birthday, 1 class from dual union all
select 2 id, 'as' name, date'2009-03-01' birthday, 2 class from dual union all
select 3 id, 'bs' name, date'1988-05-01' birthday, 3 class from dual union all
select 4 id, 'cs' name, date'1990-11-01' birthday, 1 class from dual union all
select 5 id, 'ds' name, date'2000-09-01' birthday, 2 class from dual union all
select 6 id, 'es' name, date'1995-01-01' birthday, 1 class from dual)select class,
count(case when age<15 then 1 else 0 end) "<15",
count(case when age>=15 and age<20 then 1 else 0 end) "15-20",
count(case when age>=20 and age<25 then 1 else 0 end) "20-25"
from (
select case when sysdate> to_date(to_char(sysdate,'yyyy')||to_char(birthday,'mmdd'),'yyyymmdd') then
to_char(sysdate,'yyyy')-to_char(birthday,'yyyy')
else
to_char(sysdate,'yyyy')-to_char(birthday,'yyyy') -1
end age,tt.*
from tt) group by class;