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

发表于 | 分类于 ， | | 浏览 次
字数统计: 100 | 阅读时长 ≈ 1

根据身份证号计算用户年龄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
坚持原创技术分享，您的支持将鼓励我继续创作！
0%