Mysql出生日期转换为年龄并分组统计人数的方法示例

业务需求是：实现按照年龄和性别来分组统计人数：

select
ifnull(sum(case when age = '39' and sex = 'M' then num end ), 0) as man39,
ifnull(sum(case when age = '39' and sex = 'F' then num end ), 0) as woman39,
ifnull(sum(case when age = '4059' and sex = 'M' then num end ), 0) as man4059,
ifnull(sum(case when age = '4059' and sex = 'F' then num end ), 0) as woman4059,
ifnull(sum(case when age = '6064' and sex = 'M' then num end ), 0) as man6064,
ifnull(sum(case when age = '6064' and sex = 'F' then num end ), 0) as woman6064,
ifnull(sum(case when age = '6569' and sex = 'M' then num end ), 0) as man6569,
ifnull(sum(case when age = '6569' and sex = 'F' then num end ), 0) as woman6569,
ifnull(sum(case when age = '7074' and sex = 'M' then num end ), 0) as man7074,
ifnull(sum(case when age = '7074' and sex = 'F' then num end ), 0) as woman7074,
ifnull(sum(case when age = '7579' and sex = 'M' then num end ), 0) as man7579,
ifnull(sum(case when age = '7579' and sex = 'F' then num end ), 0) as woman7579,
ifnull(sum(case when age = '8084' and sex = 'M' then num end ), 0) as man8084,
ifnull(sum(case when age = '8084' and sex = 'F' then num end ), 0) as woman8084,
ifnull(sum(case when age = '85' and sex = 'M' then num end ), 0) as man85,
ifnull(sum(case when age = '85' and sex = 'F' then num end ), 0) as woman85
from (
select count(table_four.idcard) num,
CASE
WHEN table_four.age <=39 THEN '39'
WHEN table_four.age >=40 AND table_four.age <= 59 THEN '4059'
WHEN table_four.age >=60 AND table_four.age <= 64 THEN '6064'
WHEN table_four.age >=65 AND table_four.age <= 69 THEN '6569'
WHEN table_four.age >=70 AND table_four.age <= 74 THEN '7074'
WHEN table_four.age >=75 AND table_four.age <= 79 THEN '7579'
WHEN table_four.age >=80 AND table_four.age <= 84 THEN '8084'
WHEN table_four.age >=85 THEN '85'
END as age, sex from (
select idcard, max(lastDate) from four group by idcard
-- 此处省略具体表查询内容
-- TIMESTAMPDIFF(YEAR, four.birthDate,CURDATE()) as age
-- 注意上边方法，可以用来获取年龄
) table_four
GROUP BY
CASE
WHEN table_four.age <=39 THEN '39'
WHEN table_four.age >=40 AND table_four.age <= 59 THEN '4059'
WHEN table_four.age >=60 AND table_four.age <= 64 THEN '6064'
WHEN table_four.age >=65 AND table_four.age <= 69 THEN '6569'
WHEN table_four.age >=70 AND table_four.age <= 74 THEN '7074'
WHEN table_four.age >=75 AND table_four.age <= 79 THEN '7579'
WHEN table_four.age >=80 AND table_four.age <= 84 THEN '8084'
WHEN table_four.age >=85 THEN '85'
END, sex) table_four_four

参考出处：https://www.jb51.net/article/199126.htm