sql来查不同数字段的人数 - 编程论坛

xiaoyutou2008-01-16 13:27

这样可以吗？

select   case
                when (段数>=2.0 and 段数<=3.0) then '2.0-3.0'
                when (段数>3.0 and 段数<=4.0)   then'3.0-4.0'
                 end
           ，
       count( case
       when (段数>=2.0 and 段数<=3.0) then 1
       when (段数>3.0 and 段数<=4.0)   then 1
       end
) from 表名
  group by     ( case
                when (段数>=2.0 and 段数<=3.0) then '2.0-3.0'
                when (段数>3.0 and 段数<=4.0)   then'3.0-4.0'
                end
               )

purana2008-01-16 13:35

界限最好做一个表出来.

create table tb(id int,段数 decimal(5,1))
insert into tb select 1,     2.0
insert into tb select 2 ,    3.0
insert into tb select 3  ,   2.5
insert into tb select 4   ,  2.7
insert into tb select 5    , 4.0
insert into tb select 6     ,3.5

select rtrim(b.上限)+'~'+rtrim(b.下限) 段数,count(*) 数量
from tb a
  inner join
   (select 上限=2.0,下限=3.0 union all select 3.0,4.0) b
on a.段数>=b.上限 and a.段数<=b.下限
group by b.上限,b.下限

drop table tb

/*
段数            数量
-------------- -----------
2.0~3.0           4
3.0~4.0           3

（所影响的行数为 2 行）
*/