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 );
结果: