1、创建表

CREATE TABLE `person` (
  `id` int(11) NOT NULL,
  `birthday` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf16;

2、插入测试数据

INSERT INTO `test`.`person`(`id`, `birthday`) VALUES (1, '2020-06-24');
INSERT INTO `test`.`person`(`id`, `birthday`) VALUES (2, '1939-07-01');
INSERT INTO `test`.`person`(`id`, `birthday`) VALUES (3, '1975-06-24');
INSERT INTO `test`.`person`(`id`, `birthday`) VALUES (4, '1965-06-24');
INSERT INTO `test`.`person`(`id`, `birthday`) VALUES (5, '1991-06-24');
INSERT INTO `test`.`person`(`id`, `birthday`) VALUES (6, '2005-06-24');

3、统计各个年龄段

select count(z.age), z.age
from (
select case when p.age < 7 then '幼童' when p.age < 18 then '少年' when p.age <=46 then '青年' when p.age <=70 then '中年' else '老年' end as age
from (
select  extract(year from now()) - extract(year from birthday) as age 
from person
) p
) z group by z.age