mysql根据身份证查询年龄、性别、出生日期

最新推荐文章于 2023-09-20 10:02:57 发布

小小花__

最新推荐文章于 2023-09-20 10:02:57 发布

阅读量3.9k

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/m0_49605579/article/details/124009037

版权

SELECT
	a.user_id,
IF
	(
		CHAR_LENGTH( idcard )< 18,(
			SUBSTR(now(),1,4)-(
			1900+SUBSTR(idcard,7,2)))-(
			SUBSTR(idcard,9,4)- date_format( now(), '%m%d')> 0 
			),(
			SUBSTR(now(),1,4)- SUBSTR(idcard,7,4))-(
			SUBSTR(idcard,11,4)- date_format(now(),'%m%d')> 0 
		)) AS age,
		cast(substring(idcard,7,8) as date) as 出生日期,
		case if(length(idcard)=18, cast(substring(idcard,17,1) as UNSIGNED)%2, if(length(idcard)=15,cast(substring(idcard,15,1) as UNSIGNED)%2,3))
  when 1 then '男'
  when 0 then '女'
  else '未知'
  end AS 性别,
	idcard 
FROM
	sys_user a INNER JOIN edu_retirement_manage b where a.user_id = b.user_id and  idcard is not null HAVING 1=1 and IF
	(
		CHAR_LENGTH( idcard )< 18,(
			SUBSTR(now(),1,4)-(
			1900+SUBSTR(idcard,7,2)))-(
			SUBSTR(idcard,9,4)- date_format( now(), '%m%d')> 0 
			),(
			SUBSTR(now(),1,4)- SUBSTR(idcard,7,4))-(
			SUBSTR(idcard,11,4)- date_format(now(),'%m%d')> 0 
		)) =  32 
		and 
		cast(substring(idcard,7,8) as date) =  '1990-03-07' 
		and 
	case if(length(idcard)=18, cast(substring(idcard,17,1) as UNSIGNED)%2, if(length(idcard)=15,cast(substring(idcard,15,1) as UNSIGNED)%2,3))   when 1 then '1'
  when 0 then '0'   else '未知'
  end = '1'