--测试数据 --检查表是否存在,若存在则删除 DROP TABLE IF EXISTS yingxiao; --创建测试表:yingxiao CREATE TABLE yingxiao (name varchar(10) NOT NULL , pf double(10,2) NOT NULL, khl double(10,2) NOT NULL, PRIMARY KEY (name) ); --插入测试数据 INSERT INTO yingxiao VALUES('a','15','0.15'); INSERT INTO yingxiao VALUES('b','25','0.25'); INSERT INTO yingxiao VALUES('c','35','0.35'); INSERT INTO yingxiao VALUES('d','40','0.4'); INSERT INTO yingxiao VALUES('e','45','0.45'); INSERT INTO yingxiao VALUES('f','55','0.55'); INSERT INTO yingxiao VALUES('g','65','0.65'); INSERT INTO yingxiao VALUES('h','54','0.66'); INSERT INTO yingxiao VALUES('i','75','0.75'); INSERT INTO yingxiao VALUES('j','85','0.85'); INSERT INTO yingxiao VALUES('k','95','0.95');
select (ceil(pf/20)-1)*20 as min, ceil(pf/20)*20 as max, count(*) from yingxiao group by ceil(pf/20);
实际上,在统计分组中习惯规定:“上组限不在内”,即:a≤x<b。
SELECT sum(CASE when pf<20 then 1 else 0 end) AS '[0,20)', sum(CASE when pf>20 and pf<=40 then 1 else 0 end) AS '[20,40)', sum(CASE when pf>40 and pf<=60 then 1 else 0 end) AS '[40,60)', sum(CASE when pf>60 and pf<=80 then 1 else 0 end) AS '[60,80)', sum(CASE when pf>80 then 1 else 0 end) AS '[80,100]' FROM yingxiao;
select (ceil(khl*100/20)-1)*0.2 as khl_min, ceil(khl*100/20)*0.2 as khl_max, sum(CASE when pf>=0 and pf<=20 then 1 else 0 end) AS 'pf[0,20]', sum(CASE when pf>=20 and pf<40 then 1 else 0 end) AS 'pf[20,40)', sum(CASE when pf>=40 and pf<60 then 1 else 0 end) AS 'pf[40,60)', sum(CASE when pf>=60 and pf<80 then 1 else 0 end) AS 'pf[60,80)', sum(CASE when pf>=80 and pf<=100 then 1 else 0 end) AS 'pf[80,100]', count(*) AS 'pf合计' FROM yingxiao group by ceil(khl*100/20);
—————————————————————————
【版权申明】
如非注明,本站文章均为 数据小雄 原创,转载请注明出处:数据小雄博客,并附带本文链接,谢谢合作!
本文地址:http://www.zhangzhengxiong.com/?id=97。
—————————————————————————
流泪
0人
打酱油
3人
开心
3人
鼓掌
5人
恐怖
2人
发表评论
已有1位网友发表了看法: