mysql 范围统计(-1)
mysql 范围统计(-1)
小张525 发表于1年前
mysql 范围统计(-1)
  • 发表于 1年前
  • 阅读 1
  • 收藏 0
  • 点赞 0
  • 评论 0

腾讯云 新注册用户 域名抢购1元起>>>   

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






 

共有 人打赏支持
粉丝 7
博文 49
码字总数 29769
×
小张525
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: