数据库sql根据身份证计算年龄段

mysql: 

        SELECT
        age,count(*) num
        SELECT
        WHEN TIMESTAMPDIFF(YEAR,DATE(substring(id_card,7,8)),CURDATE())<35 THEN	'35岁以下'
        WHEN TIMESTAMPDIFF(YEAR,DATE(substring(id_card,7,8)),CURDATE()) >= 35
             AND TIMESTAMPDIFF(YEAR,DATE(substring(id_card,7,8)),CURDATE()) < 45 THEN	'35-45岁'
        WHEN TIMESTAMPDIFF(YEAR,DATE(substring(id_card,7,8)),CURDATE()) >= 45
             AND TIMESTAMPDIFF(YEAR,DATE(substring(id_card,7,8)),CURDATE()) <= 55 THEN	'45-55岁'
        WHEN TIMESTAMPDIFF(YEAR,DATE(substring(id_card,7,8)),CURDATE()) >55 THEN '55岁以上'
        ELSE '未知'
        END AS age
        FROM person
        ) AS a
        GROUP BY age;

mysql通过函数可以精确到日
 结果：
 mysql
 

Oracle：
 

        SELECT
        age name,count(*) numb
            SELECT
            WHEN to_char(sysdate, 'yyyy') - substr(id_card, 7, 4) between 0 and 50 THEN	'50岁以下'
            WHEN to_char(sysdate, 'yyyy') - substr(id_card, 7, 4) between 50 and 60 THEN	'50-60岁'
            WHEN to_char(sysdate, 'yyyy') - substr(id_card, 7, 4) between 60 and 70 THEN	'60-70岁'
            WHEN to_char(sysdate, 'yyyy') - substr(id_card, 7, 4) >70 THEN '70岁以上'
            END AS age
            FROM person
        GROUP BY age
        ORDER BY
        CASE age
        WHEN '50岁以下' THEN 1
        WHEN '50-60岁' THEN 2
        WHEN '60-70岁' THEN 3
        WHEN '70岁以上' THEN 4

oracle只是年份相减，不够精确
 结果：
 oracle
# 数据库 # sql # mysql
Logo
亚马逊云科技技术品牌专区

  • 浏览量 450
  • 收藏 0
  • 0

所有评论(0)

加载更多

清风挽歌

@zxy144