mysql 范围统计,并且行转列
mysql 范围统计,并且行转列
小张525 发表于1年前
mysql 范围统计,并且行转列
  • 发表于 1年前
  • 阅读 32
  • 收藏 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 
	sum(if(a.score_class = 1,1,0)) as w_1,
	sum(if(a.score_class =2 ,1,0)) as w_2,
    sum(if(a.score_class = 3,1,0)) as w_3,
	sum(if(a.score_class =4 ,1,0)) as w_4,
    sum(if(a.score_class = 5,1,0)) as w_5

  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;

-- output
+------+------+------+------+------+
| w_1  | w_2  | w_3  | w_4  | w_5  |
+------+------+------+------+------+
|    7 |    3 |    2 |    3 |    0 |
+------+------+------+------+------+
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 | NULL  |
|  2 | DERIVED     | score      | ALL  | NULL          | NULL | NULL    | NULL |   15 | NULL  |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+

-- 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)




 

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