-- 创建数据表
create table score(id int not null auto_increment,score int not null,primary key (id))engine=myisam;
-- 插入数据
insert into score(`score`) values(100),(200),(200),(100),(300),(400),(500),(300),(200),(400),(500),(200),(500),(300);
-- 范围分组
SELECT
count(a.score_class),a.score_class
FROM
(SELECT
id,
score,
(CASE
WHEN score >= 100 AND score <= 200 THEN 1
WHEN score > 200 AND score <= 300 THEN 2
WHEN score > 300 AND score <= 400 THEN 3
WHEN score > 400 AND score <= 500 THEN 4
ELSE 5
END) AS score_class
FROM
score) AS a group by a.score_class;
-- output
+----------------------+-------------+
| count(a.score_class) | score_class |
+----------------------+-------------+
| 7 | 1 |
| 3 | 2 |
| 2 | 3 |
| 3 | 4 |
+----------------------+-------------+
4 rows in set (0.00 sec)
-- index
mysql> show index from score;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| score | 0 | PRIMARY | 1 | id | A | 15 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
-- explain
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 15 | Using temporary; Using filesort |
| 2 | DERIVED | score | ALL | NULL | NULL | NULL | NULL | 15 | NULL |
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
2 rows in set (0.04 sec)
-- 写在最后, 这篇文章和上一篇文章是有关系的,
-- 区别点:
-- 1 分组求和的方式不同
-- 2 有一个行转列的过程.