数据库sql 根据身份证计算年龄段mysql、oracle
oracle只是年份相减,不够精确。mysql通过函数可以精确到日。
·
数据库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通过函数可以精确到日
结果:
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只是年份相减,不够精确
结果:
更多推荐
已为社区贡献1条内容
所有评论(0)