Mysql对字段进行分段统计

--测试数据
--检查表是否存在，若存在则删除
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);

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