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
最后修改于 2020-06-24 10:59:23
如果觉得我的文章对你有用,请随意赞赏
扫一扫支付

