数据库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通过函数可以精确到日
结果：
文章来源：https://www.toymoban.com/news/detail-523063.html
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只是年份相减，不够精确
结果：
文章来源地址https://www.toymoban.com/news/detail-523063.html
到了这里，关于数据库sql 根据身份证计算年龄段mysql、oracle的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
