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

最新推荐文章于 2024-10-05 20:46:09 发布

清风挽歌

最新推荐文章于 2024-10-05 20:46:09 发布

阅读量4.4k

点赞数 3

分类专栏： trouble 文章标签：数据库 sql mysql

本文链接：https://blog.youkuaiyun.com/zxy144/article/details/131559893

版权

trouble 专栏收录该内容

3 篇文章

订阅专栏

该文章展示了如何使用MySQL和Oracle数据库的SQL查询来根据身份证号码计算个人的年龄段，分为35岁以下,35-45岁,45-55岁,55岁以上等区间。MySQL可以精确到日，而Oracle只精确到年。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

mysql:

        SELECT
        age,count(*) num
        FROM
        (
        SELECT
        CASE
        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
        FROM
            (
            SELECT
            CASE
            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
        END
        ;

oracle只是年份相减，不够精确
结果：
oracle