mysql 范围统计(-1)

最新推荐文章于 2023-03-18 09:38:09 发布

转载最新推荐文章于 2023-03-18 09:38:09 发布 · 338 阅读

1 ·

CC 4.0 BY-SA版权

原文链接：https://my.oschina.net/u/1579560/blog/803577

文章标签：

#数据库 #python

本文介绍了如何使用MySQL创建数据表、插入数据，并通过CASE语句实现数据的范围分组，进而统计各分数段的数据数量。同时展示了如何查看索引信息及SQL执行计划。

2019独角兽企业重金招聘Python工程师标准>>>

-- 创建数据表
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  有一个行转列的过程.

转载于:https://my.oschina.net/u/1579560/blog/803577