user 表 ,其中字段有 id ,name ,..... birthdate,diagnosis
其中 ,birthdate 是生日,diagnosis是一个枚举值,diagnosis.1,diagnosis.2,diagnosis.3,diagnosis.4
显示结果:
年龄 总计 diagnosis.1 diagnosis.2 diagnosis.3 diagnosis.4
0-5 1 1 0 0 0
5-10 0 0 0 0 0
..................
50岁以上 1 0 1 1 1
其中 ,birthdate 是生日,diagnosis是一个枚举值,diagnosis.1,diagnosis.2,diagnosis.3,diagnosis.4
显示结果:
年龄 总计 diagnosis.1 diagnosis.2 diagnosis.3 diagnosis.4
0-5 1 1 0 0 0
5-10 0 0 0 0 0
..................
50岁以上 1 0 1 1 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;http://topic.csdn.net/u/20110524/16/dfce45fa-1a47-4bee-ae24-7b8404a71535.html?76395