Mysql根据身份证号计算用户年龄并统计年龄段人数

根据身份证号计算用户年龄SQL语句:

1
SELECT DATE_FORMAT(NOW(), '%Y') - SUBSTRING('370687199005050000',7,4) AS age FROM table_name

统计年龄段人数SQL语句:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SELECT
(
CASE
WHEN age < 50 THEN
'50岁以下'
WHEN age < 60 THEN
'50-59岁'
WHEN age < 70 THEN
'60-69岁'
WHEN age < 80 THEN
'70-79岁'
ELSE
'80岁以上'
END
) AS age_range,
COUNT(age) AS count
FROM
(
SELECT
(
DATE_FORMAT(NOW(), '%Y') - SUBSTRING(sq_party_card_id, 7, 4)
) AS age
FROM
table_name
) AS a
GROUP BY
age_range
坚持原创技术分享,您的支持将鼓励我继续创作!