常用sql语句--计算周岁

两种常用方式（根据身份证号拿到出生年月日进行计算）

1.从年份逐渐对比（精确）

    --常用于获取年龄字段

• 先年份相减

  • 再对比当前月份和出生月份
    • 当前月份大=已过生日
    • 月份相等比较日期，当前日期大=已过生日
• 其他情况都是未过生日，（在年份相减的基础上）再减1

select 
case when month(current_date) > substr(sfz,11,2) then year(current_date) - substr(sfz,7,4)  
 when month(current_date) = substr(sfz,11,2) and day(current_date) > substr(sfz,13,2) then year(current_date) - substr(sfz,7,4) 
 else year(current_date) - substr(sfz,7,4) - 1 
end as age 
from test_age
;

2.直接按照天数/365（粗略）

--常用于按照年龄范围分组展示统计量

select
count(1) as data_amount, --业务数据量
case 
when floor(datediff(to_date(substr(GETDATE(),1,10),'yyyy-mm-dd'),to_date(SUBSTR(id_card,7,8),'yyyymmdd'),'dd')/365)  between  90 and 200  then 10
when floor(datediff(to_date(substr(getdate(),1,10),'yyyy-mm-dd'),to_date(SUBSTR(id_card,7,8),'yyyymmdd'),'dd')/365)  between  80 and 89  then 9 
when floor(datediff(to_date(substr(getdate(),1,10),'yyyy-mm-dd'),to_date(SUBSTR(id_card,7,8),'yyyymmdd'),'dd')/365)  between  70 and 79  then 8
when floor(datediff(to_date(substr(getdate(),1,10),'yyyy-mm-dd'),to_date(SUBSTR(id_card,7,8),'yyyymmdd'),'dd')/365)  between  60 and 69  then 7
when floor(datediff(to_date(substr(getdate(),1,10),'yyyy-mm-dd'),to_date(SUBSTR(id_card,7,8),'yyyymmdd'),'dd')/365)  between  50 and 59  then 6
when floor(datediff(to_date(substr(getdate(),1,10),'yyyy-mm-dd'),to_date(SUBSTR(id_card,7,8),'yyyymmdd'),'dd')/365)  between  40 and 49  then 5
when floor(datediff(to_date(substr(getdate(),1,10),'yyyy-mm-dd'),to_date(SUBSTR(id_card,7,8),'yyyymmdd'),'dd')/365)  between  30 and 39  then 4
when floor(datediff(to_date(substr(getdate(),1,10),'yyyy-mm-dd'),to_date(SUBSTR(id_card,7,8),'yyyymmdd'),'dd')/365)  between  20 and 29  then 3
when floor(datediff(to_date(substr(getdate(),1,10),'yyyy-mm-dd'),to_date(SUBSTR(id_card,7,8),'yyyymmdd'),'dd')/365)  between  10 and 19  then 2
when floor(datediff(to_date(substr(getdate(),1,10),'yyyy-mm-dd'),to_date(SUBSTR(id_card,7,8),'yyyymmdd'),'dd')/365)  between  0 and 9 then 1
end as generation --所属年龄段 1.[0-1岁]、2.[1-3岁]、3.[3-6岁]、4.[6-12岁]、5.[12-20岁]、6.[20-35岁]、7.[35-45岁]、8.[45-65岁]、9.[>65岁]
from ods_zsk_cmop_emr_df as cp
group by 
case 
when floor(datediff(to_date(substr(GETDATE(),1,10),'yyyy-mm-dd'),to_date(SUBSTR(id_card,7,8),'yyyymmdd'),'dd')/365)  between  90 and 200  then 10
when floor(datediff(to_date(substr(getdate(),1,10),'yyyy-mm-dd'),to_date(SUBSTR(id_card,7,8),'yyyymmdd'),'dd')/365)  between  80 and 89  then 9 
when floor(datediff(to_date(substr(getdate(),1,10),'yyyy-mm-dd'),to_date(SUBSTR(id_card,7,8),'yyyymmdd'),'dd')/365)  between  70 and 79  then 8
when floor(datediff(to_date(substr(getdate(),1,10),'yyyy-mm-dd'),to_date(SUBSTR(id_card,7,8),'yyyymmdd'),'dd')/365)  between  60 and 69  then 7
when floor(datediff(to_date(substr(getdate(),1,10),'yyyy-mm-dd'),to_date(SUBSTR(id_card,7,8),'yyyymmdd'),'dd')/365)  between  50 and 59  then 6
when floor(datediff(to_date(substr(getdate(),1,10),'yyyy-mm-dd'),to_date(SUBSTR(id_card,7,8),'yyyymmdd'),'dd')/365)  between  40 and 49  then 5
when floor(datediff(to_date(substr(getdate(),1,10),'yyyy-mm-dd'),to_date(SUBSTR(id_card,7,8),'yyyymmdd'),'dd')/365)  between  30 and 39  then 4
when floor(datediff(to_date(substr(getdate(),1,10),'yyyy-mm-dd'),to_date(SUBSTR(id_card,7,8),'yyyymmdd'),'dd')/365)  between  20 and 29  then 3
when floor(datediff(to_date(substr(getdate(),1,10),'yyyy-mm-dd'),to_date(SUBSTR(id_card,7,8),'yyyymmdd'),'dd')/365)  between  10 and 19  then 2
when floor(datediff(to_date(substr(getdate(),1,10),'yyyy-mm-dd'),to_date(SUBSTR(id_card,7,8),'yyyymmdd'),'dd')/365)  between  0 and 9 then 1 end 

round(datediff(a.jzrqsj,to_date(case when length(sfzjhm)=18 then substr(sfzjhm,7,8) when length(sfzjhm)=15 then concat('19',substr(sfzjhm,7,6)) else null end,'yyyymmdd'),'dd')/365) as age