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
    );

结果：