mysql 区间查询

mysql 区间查询

需求：统计各个班级的学生成绩（0-60,60-80,80-100）三个区域的人数

建表：

CREATE TABLE `tbl_sys_study` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) COLLATE utf8mb4_esperanto_ci DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  `class_name` varchar(20) COLLATE utf8mb4_esperanto_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_esperanto_ci;

新增数据如下

 

查询sql

select class_name
,count(a.score_range="0-60" or null) one
,count(a.score_range="60-80" or null) two
,count(a.score_range="80-100" or null) three

 from (
select DISTINCT study.`name`,study.score,study.class_name,
 CASE 
    when study.score >=0 and study.score <60 THEN "0-60" 
    when study.score >=60 and study.score <80 THEN "60-80" 
    when study.score >=80 and study.score <=100 THEN "80-100" 
end  as score_range
from tbl_sys_study study

) a

GROUP BY class_name

查询结果如下：

解释：

1.先通过 case when else end 将数据表的分数替换为各个区间的范围

2.通过count(a.score_range="80-100" or null) 将特定的字段值进行统计