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

结果：