mysql根据身份证号取得年岁并对年岁进行分组查询在MyBatis中的运用A8源码交易平台
需求：1.检测表只要身份证号，无年岁和性别字段，需求对检测数据进行年岁分组
2.需求根据性别的不同对数据进行比较，性别不同，比较的标准也不一样
A8源码交易平台
实现思路：
1.先根据身份证号运用mysql函数TIMESTAMPDIFF( YEAR STR_TO_DATE( substr (patient_id/7/8),'%Y%m%d'),sysdate())得到年岁，对mysql函数不明白的请自行百度
2. 运用case when then条件分支语句得到年岁分组
3.我这儿联合用户表得到性别，当然根据身份证号倒数第二位数也可以算出性别，我这儿是运用了联合查询，用户表中也没有年岁字段，
4.根据性别拿检测值同对应性别的标准值进行比照。
A8源码交易平台
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()) = ]]>0 and TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(patient_id,7,8),'%Y%m%d'),sysdate()) 20 then '0~20'
when TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(patient_id,7,8),'%Y%m%d'),sysdate()) = ]]> 21 and TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(patient_id,7,8),'%Y%m%d'),sysdate()) 30 then '21~30'
when TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(patient_id,7,8),'%Y%m%d'),sysdate()) = ]]> 31 and TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(patient_id,7,8),'%Y%m%d'),sysdate()) 40 then '31~40'
when TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(patient_id,7,8),'%Y%m%d'),sysdate()) = ]]> 41 and TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(patient_id,7,8),'%Y%m%d'),sysdate()) 50 then '41~50'
when TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(patient_id,7,8),'%Y%m%d'),sysdate()) = ]]> 51 and TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(patient_id,7,8),'%Y%m%d'),sysdate()) 60 then '51~60'
when TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(patient_id,7,8),'%Y%m%d'),sysdate()) = ]]> 61 and TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(patient_id,7,8),'%Y%m%d'),sysdate()) 70 then '61~70'
when TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(patient_id,7,8),'%Y%m%d'),sysdate()) = ]]> 71 and TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(patient_id,7,8),'%Y%m%d'),sysdate()) 80 then '71~80'
when TIMESTAMPDIFF(YEAR,STR_TO_DATE(substr(patient_id,7,8),'%Y%m%d'),sysdate()) ]]> 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 0.2 or a.ua ]]> 0.42) and b.GENDER='GB_T_2261.1_2003_1' or (a.ua 0.14 or a.ua ]]> 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
---------------------
A8源码交易平台
