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

   需求：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>