Mysql按某一字段区间分组

elt函数和interval函数的联合使用，实现按字段区间分组

一、建表语句

DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student`  (
  `id` int NOT NULL PRIMARY KEY,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `age` int NULL DEFAULT NULL
)

二、添加数据

INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (1, '李木', 22);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (2, '尹杰宏', 5);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (3, '尹致远', 61);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (4, '毛璐', 60);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (5, '向云熙', 61);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (6, '韦秀英', 0);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (7, '顾秀英', 49);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (8, '范宇宁', 9);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (9, '方岚', 90);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (10, '傅云熙', 10);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (11, '曾宇宁', 11);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (12, '陶璐', 87);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (13, '李杰宏', 4);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (14, '尹宇宁', 13);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (15, '于晓明', 67);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (16, '戴子韬', 72);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (17, '程子异', 38);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (18, '高子韬', 21);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (19, '何宇宁', 25);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (20, '武震南', 86);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (21, '姚云熙', 73);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (22, '谢云熙', 11);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (23, '段致远', 96);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (24, '袁安琪', 69);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (25, '丁致远', 48);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (26, '江云熙', 8);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (27, '孙璐', 65);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (28, '陈宇宁', 89);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (29, '韩璐', 26);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (30, '刘子异', 73);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (31, '高致远', 58);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (32, '吕秀英', 71);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (33, '段詩涵', 55);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (34, '叶安琪', 50);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (35, '贾睿', 23);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (36, '卢岚', 25);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (37, '任詩涵', 93);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (38, '侯宇宁', 2);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (39, '石詩涵', 94);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (40, '郝子异', 79);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (41, '潘睿', 80);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (42, '沈晓明', 1);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (43, '段秀英', 76);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (44, '邹詩涵', 40);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (45, '萧睿', 28);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (46, '汤宇宁', 98);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (47, '韦震南', 72);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (48, '田晓明', 80);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (49, '顾嘉伦', 73);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (50, '金嘉伦', 85);
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (51, '熊詩涵', 52);

三、使用elt函数加interval函数实现年龄段展示

select id,name,elt(interval(age,0,20,40,60,80),'青年','成年','中年','老年','长寿') ageGroup from t_student

四、统计不同年龄段的人数

select elt(interval(age,0,20,40,60,80),'青年','成年','中年','老年','长寿')  ageGroup, count(*) num from t_student group by ageGroup