mysql根据身份证号获得年龄并对年龄进行分组查询在MyBatis中的应用

约定不等于承诺〃 2023-10-18 20:59 52阅读 0赞

需求：1.检测表只有身份证号，无年龄和性别字段，需要对检测数据进行年龄分组

2.需要根据性别的不同对数据进行比较，性别不同，比较的标准也不一样

实现思路：

1.先根据身份证号运用mysql函数TIMESTAMPDIFF(YEAR,STR\_TO\_DATE(substr(patient\_id,7,8),'%Y%m%d'),sysdate())得到年龄，对mysql函数不懂的请自行百度

2. 使用case when then条件分支语句得到年龄分组

3.我这里联合用户表得到性别，当然根据身份证号倒数第二位数也可以算出性别，我这里是运用了联合查询，用户表中也没有年龄字段，

4.根据性别拿检测值同对应性别的标准值进行对比。

<select id="getExamDataGroudByAge"  resultMap="BaseResultMap">
       SELECT  age,uw.patient_id,IFNULL(count,0) as count
            FROM UW uw
            LEFT JOIN (
       select age as age,a.patient_id,a.ua,count(*) as count from
        (
        select 
            case 
                  when TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(patient_id,7,8),'%Y%m%d'),sysdate())  <![CDATA[ >= ]]>0  and TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(patient_id,7,8),'%Y%m%d'),sysdate())  <![CDATA[ <= ]]> 20  then '0~20' 
                  when TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(patient_id,7,8),'%Y%m%d'),sysdate())  <![CDATA[ >= ]]>  21 and TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(patient_id,7,8),'%Y%m%d'),sysdate())  <![CDATA[ <= ]]>  30  then '21~30'
                  when TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(patient_id,7,8),'%Y%m%d'),sysdate())  <![CDATA[ >= ]]> 31 and TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(patient_id,7,8),'%Y%m%d'),sysdate())  <![CDATA[ <= ]]>  40  then '31~40'
                  when TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(patient_id,7,8),'%Y%m%d'),sysdate())  <![CDATA[ >= ]]>  41 and TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(patient_id,7,8),'%Y%m%d'),sysdate())  <![CDATA[ <= ]]>  50  then '41~50'
                  when TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(patient_id,7,8),'%Y%m%d'),sysdate())  <![CDATA[ >= ]]>  51 and TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(patient_id,7,8),'%Y%m%d'),sysdate())  <![CDATA[ <= ]]>  60 then '51~60'
                 when TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(patient_id,7,8),'%Y%m%d'),sysdate())  <![CDATA[ >= ]]>  61 and TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(patient_id,7,8),'%Y%m%d'),sysdate())   <![CDATA[ <= ]]>  70  then '61~70'
                 when TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(patient_id,7,8),'%Y%m%d'),sysdate())  <![CDATA[ >= ]]> 71 and TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(patient_id,7,8),'%Y%m%d'),sysdate())<![CDATA[ <= ]]>  80 then '71~80'
                 when TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(patient_id,7,8),'%Y%m%d'),sysdate()) <![CDATA[ > ]]> 80 then '80+' 
                 END AS age,
                 patient_id as patient_id,
                 ua as ua  from UW a INNER JOIN archive b on a.patient_id=b.ARCHIVEID  where ((a.ua  <![CDATA[ < ]]>  0.2 or a.ua  <![CDATA[ > ]]> 0.42) and b.GENDER='GB_T_2261.1_2003_1' or (a.ua <![CDATA[ < ]]> 0.14 or a.ua  <![CDATA[ > ]]> 0.36) and b.GENDER='GB_T_2261.1_2003_2')
                 )a group by age
                  )tb  on uw.patient_id=tb.patient_id group by age
      </select>