mysql分段数据汇总

CREATE TABLE `ljbb`.`Untitled`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `class` int(10) NOT NULL,
  `score` int(3) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `score` (`id`, `name`, `class`, `score`) VALUES
(1, '张三', 1, '60'),
(2, '李四', 2, '80'),
(3, '张三1', 1, '20'),
(4, '李四2', 2, '40'),
(5, '张三2', 1, '50'),
(6, '李四3', 2, '90'),
(7, '王五', 1, '90'),
(8, '李六', 2, '85');

SELECT  
 sum(CASE when score<60 then 1 else 0 end) AS '不及格',  
 sum(CASE when score>=60 and score<70 then 1 else 0 end) AS '差',  
 sum(CASE when score>=70 and score<80 then 1 else 0 end) AS '中',  
 sum(CASE when score>=80 and score<90 then 1 else 0 end) AS '良',  
 sum(CASE when score>=90 and score<=100 then 1 else 0 end) AS '优'  
from score;

如果是每20分段为一组可以这样查询

select count(*),(ceil(score/20)-1)*20 as min,ceil(score/20)*20 as max from score group by ceil(score/20)