同字段不同值的统计查询(Mysql）

1、同字段不同值分段统计查询：

SELECT

    sum(case when `col_name`= 1 then 1 else 0 end ) as 'num1',

    sum(case when `col_name`BETWEEN 2 and 3 then 1 else 0 end ) as 'num2',

    sum(case when `col_name`> 3 then 1 else 0 end ) as 'num3'

from `table_name`;

2、按最高连续签到次数分段统计：

SELECT 

    sum(case when ma.m = 1 then 1 else 0 end ) as num1,

    sum(case when ma.m BETWEEN 2 and 3 then 1 else 0 end ) as num2,

    sum(case when ma.m BETWEEN 4 and 6 then 1 else 0 end ) as num3,

    sum(case when ma.m = 7 then 1 else 0 end ) as num4,

    sum(case when ma.m > 7 then 1 else 0 end ) as num5

from (SELECT max(sign_num) as m from `sign_record` GROUP BY account_id) ma;

这种方式在做某字段的值分段统计时比较好用，只需扫描一次全表就可以。