create table test ( id int, value int )
goinsert test select 1, 28 union select 1,31 union select 1,35
union select 1,50 union select 1,49
goselect identity(int,1,1) as n into #t
from (select top 100 name from sysobjects )a ,
(select top 10 name from syscoluminss) b
declare @diff int, @step int,@begin int
select @begin=25, @step=5,@diff=min(value)-@begin from test
--根据需要替换@begin 和 @step
--比如1 就是 @begin=25 @step=5
--2 就是 @begin=25 @step=6select n as step,
mins + (maxs-mins+@diff)/@step*(n-1)-@diff as begins,
mins + (maxs-mins+@diff)/@step*n-@diff as ends
from
( select min(value) as mins,max(value) as maxs from test ) as maxs
cross join
( select n from #t where n<=@step ) as ns
----
step begins ends
1 25 30
2 30 35
3 35 40
4 40 45
5 45 50
--连起来declare @diff int, @step int,@begin int
select @begin=25, @step=5,@diff=min(value)-@begin from test
select cast(begins as varchar)+'-'+cast(ends as varchar) as '间隔' ,count(a.value) as '数量'
from
(select n as step,
mins + (maxs-mins+@diff)/@step*(n-1)-@diff as begins,
mins + (maxs-mins+@diff)/@step*n-@diff as ends
from
( select min(value) as mins,max(value) as maxs from test ) as maxs
cross join
( select n from #t where n<=@step ) as ns
) b left join test a
on a.value>=b.begins and a.value<=b.ends
group by cast(begins as varchar)+'-'+cast(ends as varchar)
------
间隔 数量
25-30 1
30-35 2
35-40 1
40-45 0
45-50 2
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货