mysql 根据身份证查询 年龄 性别 

        SELECT 
         
          IFNULL(fm.`resName`, "") AS 'name',
          IFNULL(fm.`sfcode`, "") AS 'sfcode',
          IFNULL(fm.`birthday`, "") AS 'birthday',
            
            CASE
                IF
                    (
                        length( fm.`sfcode` )= 18,
                        cast( substring( fm.`sfcode`, 17, 1 ) AS UNSIGNED )% 2,
                    IF
                    ( length( fm.`sfcode` )= 15, cast( substring( fm.`sfcode`, 15, 1 ) AS UNSIGNED )% 2, 3 )) 
            WHEN 1 
                THEN '男' 
            WHEN 0 
                THEN '女' 
            ELSE '未知' 
            END AS 'sex',
            
          CAST(
            IF(
              IFNULL(fm.`sfcode`, '') = '',
              IF(
                IFNULL(fm.`birthday`, '') = '',
                '',
                TIMESTAMPDIFF(YEAR, fm.`birthday`, CURDATE())
              ),
              IF(
                LENGTH(fm.`sfcode`) = 18,
                TIMESTAMPDIFF( YEAR, SUBSTRING(fm.`sfcode`, 7, 8), CURDATE() ),
                IF(
                  LENGTH(fm.`sfcode`) = 15,
                  TIMESTAMPDIFF( YEAR, CONCAT('19', SUBSTRING(fm.`sfcode`, 7, 6)), CURDATE()
                  ),
                  ''
                )
              )
            ) AS CHAR
          ) AS 'age'
        FROM
          resident fm         
        WHERE fm.`mobile` = '157****3163'

 

posted @ 2021-04-09 14:28  ジ绯色月下ぎ  阅读(838)  评论(0)  编辑  收藏  举报