mysql - 使用日期开始的年龄并创建一个年龄阶段列

Question

我在仅使用出生日期显示年龄阶段时遇到问题，这是我的查询和示例图片

SELECT 
res_fName,res_mName,
res_lName,rs.suffix,
rg.gender_Name,
TIMESTAMPDIFF(YEAR,res_Bday,CURDATE()) AS age,
(SELECT IF (TIMESTAMPDIFF(YEAR,res_Bday,CURDATE()) <=1,'INFANT','')) Age_Stage 
FROM resident_detail rd 
LEFT JOIN ref_suffixname rs ON rd.suffix_ID = rs.suffix_ID 
LEFT JOIN ref_gender rg ON rd.gender_ID = rg.gender_ID

SQL RESULT DISPLAY:

年龄阶段

• 孕产妇和新生儿(0 到 1 个月)
• 婴儿(1 到 12 个月)
• 幼儿(13 至 24 个月)
• 学龄前 child (2 至 4 岁)
• 学龄 child (5 至 8 岁)
• Tweens(9 到 12 岁)
• 青少年(13 至 19 岁)
• 青年(20 至 35 岁)
• 中年人(36 至 55 岁)
• 高级(56 到 100 岁)

Answer 1

使用案例陈述来确定年龄阶段；

   SELECT res_fName,res_mName,res_lName,rs.suffix,rg.gender_Name,TIMESTAMPDIFF(YEAR,res_Bday,CURDATE()) AS age,
(case  
 when (TIMESTAMPDIFF(Month,res_Bday,CURDATE())<=1) then 'Maternal and Newborn'
 when (TIMESTAMPDIFF(Month,res_Bday,CURDATE())<=1 and TIMESTAMPDIFF(Month,res_Bday,CURDATE())<=12) then 'Babies'
when (TIMESTAMPDIFF(Month,res_Bday,CURDATE())<=13 and TIMESTAMPDIFF(Month,res_Bday,CURDATE())<=24) then 'Toddlers'
when (TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=2 and TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=4) then 'Preschoolers'
 when (TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=5 and TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=8) then 'School Age Children'
 when (TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=9 and TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=12) then 'Tweens '
when (TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=13 and TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=19) then 'Teenager'
when (TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=20 and TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=35) then 'Young Adult'
when (TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=36 and TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=55) then 'Middle-Aged Adults'
when (TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=56 and TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=100) then 'Senior'
   end) Age_Stage 
FROM resident_detail rd LEFT JOIN ref_suffixname rs ON rd.suffix_ID = rs.suffix_ID LEFT JOIN ref_gender rg ON rd.gender_ID = rg.gender_ID