--> 测试数据:#tb if object_id('tempdb.dbo.#tb') is not null drop table #tb create table #tb([ID] numeric(3,2),[VALUE] numeric(2,1)) insert #tb select 1.1,3 union all select 1.2,4 union all select 1.17,2.3 union all select 2.3,1 union all select 3.4,3 union all select 8.3,1 --[1-3][3-7][7-20] select sum(case when VALUE>=1 and VALUE<3 then 1 else 0 end) as [1-3] , sum(case when VALUE>=3 and VALUE<7 then 1 else 0 end) as [3-7], sum(case when VALUE>=7 and VALUE<10 then 1 else 0 end) as [7-10] from #tb
sum(case when )
create table tb([ID] numeric(3,2),[VALUE] numeric(2,1)) insert tb select 1.1,3 union all select 1.2,4 union all select 1.17,2.3 union all select 2.3,1 union all select 3.4,3 union all select 8.3,1select case when id >=1 and id < 3 then '1-3' when id >=3 and id < 7 then '3-7' when id >=7 and id < 20 then '7-20' end id, count(1) VALUE from tb group by case when id >=1 and id < 3 then '1-3' when id >=3 and id < 7 then '3-7' when id >=7 and id < 20 then '7-20' enddrop table tb/* id VALUE ---- ----------- 1-3 4 3-7 1 7-20 1(所影响的行数为 3 行) */
if object_id('tempdb.dbo.#tb') is not null drop table #tb
create table #tb([ID] numeric(3,2),[VALUE] numeric(2,1))
insert #tb
select 1.1,3 union all
select 1.2,4 union all
select 1.17,2.3 union all
select 2.3,1 union all
select 3.4,3 union all
select 8.3,1
--[1-3][3-7][7-20]
select sum(case when VALUE>=1 and VALUE<3 then 1 else 0 end) as [1-3] ,
sum(case when VALUE>=3 and VALUE<7 then 1 else 0 end) as [3-7],
sum(case when VALUE>=7 and VALUE<10 then 1 else 0 end) as [7-10]
from #tb
insert tb
select 1.1,3 union all
select 1.2,4 union all
select 1.17,2.3 union all
select 2.3,1 union all
select 3.4,3 union all
select 8.3,1select case when id >=1 and id < 3 then '1-3'
when id >=3 and id < 7 then '3-7'
when id >=7 and id < 20 then '7-20'
end id, count(1) VALUE
from tb
group by
case when id >=1 and id < 3 then '1-3'
when id >=3 and id < 7 then '3-7'
when id >=7 and id < 20 then '7-20'
enddrop table tb/*
id VALUE
---- -----------
1-3 4
3-7 1
7-20 1(所影响的行数为 3 行)
*/