身份号码中间部分*代替，查询年龄大于60、查询各个年龄段

--身份号码中间部分替换成**

select replace('620522199201202517',substr('110101198001010117',7,8),'****') from dual; ---身份证号带**

--截取身份证号码获取年龄大于60岁的人

select substr(t.aac002 ,7,8)
from bc01 t
where substr(t.aac002 ,7,8) > (select to_char(sysdate - interval '60' year,'yyyyMMdd') from dual)

--查询各个年龄段

select case
when to_char(sysdate, 'yyyy') - substr(aac002, 7, 4) between 18 and 29 then
'青年'
when to_char(sysdate, 'yyyy') - substr(aac002, 7, 4) between 30 and 59 then
'中年'
when to_char(sysdate, 'yyyy') - substr(aac002, 7, 4) >= 60 then
'老年'
end as 年龄段,
count(*) as 人数
from bc01 t
group by case
when to_char(sysdate, 'yyyy') - substr(aac002, 7, 4) between 18 and 29 then
'青年'
when to_char(sysdate, 'yyyy') - substr(aac002, 7, 4) between 30 and 59 then
'中年'
when to_char(sysdate, 'yyyy') - substr(aac002, 7, 4) >= 60 then
'老年'
end;