mysql统计各个年龄段有多少人数

最新推荐文章于 2024-02-13 20:32:33 发布

红茶两块五

最新推荐文章于 2024-02-13 20:32:33 发布

阅读量4.1k

点赞数 1

分类专栏： java 文章标签： mysql

本文链接：https://blog.csdn.net/weixin_38417148/article/details/120014334

版权

java 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

通过生日字段来计算年龄

select distinct u.user_id,TIMESTAMPDIFF(YEAR,u.birthday,CURDATE()) AS 'age'
        from sys_user u

将计算出来的年龄进行分组

这里我用1代表小于20，2代表20-30，3代表30-40以此类推，
后面的union 表示如果该年龄段没有人，则默认为0

select 
	count(1) `value`,
	if(u.age<=20,'1',
		if(20<u.age and u.age<=30,'2',
			if(30<u.age and u.age<=40,'3',
				if(40<u.age and u.age<=50,'4',
					if(50<u.age and u.age<=60,'5',
						if(60<u.age,'6','0')
					)
				)
			)
		)
	) `type`
	from 
	(
	select distinct u.user_id,TIMESTAMPDIFF(YEAR,u.birthday,CURDATE()) AS 'age'
			from sys_user u
	) u
	GROUP BY `type`
	UNION (SELECT 0,'0')
	UNION (SELECT 0,'1')
	UNION (SELECT 0,'2')
	UNION (SELECT 0,'3')
	UNION (SELECT 0,'4')
	UNION (SELECT 0,'5')
	UNION (SELECT 0,'6')

最后将年龄段进行处理

select `value`,
CASE `type` 
WHEN '1' THEN '小于20' 
WHEN '2' THEN '21-30' 
WHEN '3' THEN '31-40' 
WHEN '4' THEN '41-50' 
WHEN '5' THEN '51-60' 
WHEN '6' THEN '大于60' 
ELSE '无'
END `name`
from 
(
	select 
	count(1) `value`,
	if(u.age<=20,'1',
		if(20<u.age and u.age<=30,'2',
			if(30<u.age and u.age<=40,'3',
				if(40<u.age and u.age<=50,'4',
					if(50<u.age and u.age<=60,'5',
						if(60<u.age,'6','0')
					)
				)
			)
		)
	) `type`
	from 
	(
	select distinct u.user_id,TIMESTAMPDIFF(YEAR,u.birthday,CURDATE()) AS 'age'
			from sys_user u
	) u
	GROUP BY `type`
	UNION (SELECT 0,'0')
	UNION (SELECT 0,'1')
	UNION (SELECT 0,'2')
	UNION (SELECT 0,'3')
	UNION (SELECT 0,'4')
	UNION (SELECT 0,'5')
	UNION (SELECT 0,'6')
) a
GROUP BY `name`
ORDER BY `type`