mysql 区间范围分组_MySQL区间分组查询

假设a表为会员信息表，需要统计男性会员年龄各阶段的出现的人数

CREATE TABLE `a` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(255) NOT NULL DEFAULT '' COMMENT '会员名称',

`sex` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '性别，1、男 2、女',

`age` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '年龄',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

假设现在数据库中有数据如下：

方法一：

SELECT

ELT(

INTERVAL (age, 0, 20, 30, 40),

"1-20",

"21-30",

"31-40",

"40+"

) AS age_area,

COUNT(name) AS num

FROM

`a`

WHERE

sex = 1

GROUP BY

ELT(

INTERVAL (age, 0, 20, 30, 40),

"1-20",

"21-30",

"31-40",

"40+"

);

说明：

利用 interval 划出4个区间

再利用 elt 函数将4个区间分别返回一个列名

方法二：

SELECT

(

CASE

WHEN age >= 1

AND age <= 20 THEN

"1-20"

WHEN age > 20

AND age <= 30 THEN

"21-30"

WHEN age > 30

AND age <= 40 THEN

"31~40"

ELSE

"40+"

END

) AS age_area,

count(name) AS num

FROM

a

WHERE

sex = 1

GROUP BY

(

CASE

WHEN age >= 1

AND age <= 20 THEN

"1-20"

WHEN age > 20

AND age <= 30 THEN

"21-30"

WHEN age > 30

AND age <= 40 THEN

"31~40"

ELSE

"40+"

END

);

结果：