數(shù)據(jù)庫sql根據(jù)身份證計算年齡段
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通過函數(shù)可以精確到日
結(jié)果：
文章來源：http://www.zghlxwxcb.cn/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只是年份相減，不夠精確
結(jié)果：
文章來源地址http://www.zghlxwxcb.cn/news/detail-523063.html
到了這里，關(guān)于數(shù)據(jù)庫sql 根據(jù)身份證計算年齡段mysql、oracle的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
