mysql根据身份证号计算出生日期和年龄,并查询筛选

假设计算年龄只考虑年份的话,这样写即可

SELECT
  YEAR(CURDATE()) - SUBSTRING(ID_CARD, 7, 4) AS age,SUBSTRING(ID_CARD, 7, 4) as year
FROM
  users
HAVING
     age between 20 and 30

假如需要考虑月份和年份一起的话,可以这样写:

SELECT
  YEAR(CURDATE()) - SUBSTRING(ID_CARD, 7, 4) - IF(SUBSTRING(ID_CARD, 10, 2) < 10, 0, 1) AS age,ID_CARD
FROM
 users
HAVING
     age between 20 and 30

拓展知识:

根据身份证号生成出生日期

SELECT
  CONCAT(
        SUBSTRING(ID_CARD, 7, 4),
        '-',
        SUBSTRING(ID_CARD, 11, 2),
        '-',
        SUBSTRING(ID_CARD, 13, 2)
    ) AS birthday,ID_CARD
FROM
  users

根据身份证号生成出生日期和年龄

SELECT
  @birthday:=CONCAT(
        SUBSTRING(ID_CARD, 7, 4),
        '-',
        SUBSTRING(ID_CARD, 11, 2),
        '-',
        SUBSTRING(ID_CARD, 13, 2)
    ) as birthday,
    TIMESTAMPDIFF(YEAR, @birthday, CURDATE()) as age,ID_CARD
FROM
  users
© 版权声明
THE END
喜欢就支持一下吧
点赞14 分享
评论 抢沙发
头像
欢迎您留下宝贵的见解!
提交
头像

昵称

取消
昵称表情代码图片

    请登录后查看评论内容