--x<=30,30<x<=60,x>60
declare @t table(id int primary key,col decimal(10,2))
insert into @t select 1,26
union all select 2,88
union all select 3,4
union all select 4,76
union all select 5,58
union all select 6,18
union all select 7,84
--第一种方法
select des,cou = count(cou),num = cast(count(cou) as decimal(10,2))/b.cout from(
select
cou = case when a.col <= 30 then 1
when a.col > 30 and a.col <= 60 then 2
else 3 end,
des = case when a.col <= 30 then 'col<=30'
when a.col > 30 and a.col <= 60 then '30<col<=60'
else 'col>60' end
from @t a ) a cross join (select cout = count(1) from @t ) b group by des,b.cout
--第二种方法
select a.des,cou=count(1),num = cast(count(1) as decimal(10,2))/c.cout from
(select sid = 1,leftNum = null,rightNum = 30,des = 'col<=30'
union all select sid = 2,leftNum = 30,rightNum =60,des = '30<col<=60'
union all select sid = 3,leftNum = 60,rightNum = null,des = 'col>60'
) a left join @t b on (b.col <= a.rightNum or a.rightNum is null) and (b.col > a.leftNum or a.leftNum is null)
cross join (select cout = count(1) from @t ) c
group by a.des,c.cout