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

通过生日字段来计算年龄

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) uGROUP 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) uGROUP 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`

ps：如果放在mybatis中，注意下<和>,采用"<“或”>"
有不对的地方，还望大神指点

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！