有一组数据如下
编号(id) 重量(weight)
1 20
2 15
3 5
4 60
5 12
6 33
7 45
8 59
9 89
10 110
现在想在程序里动态的按区间统计。
比如 在程序里选择10——50 之间间隔10的有多少个。
希望的结果:
10-20 4
20-30 2
30-40 1
40-50 1这里区间10,50 和间隔10 ,都是可以动态设定的。
这样的sql语句该怎样处理呢,请指教。
谢谢
编号(id) 重量(weight)
1 20
2 15
3 5
4 60
5 12
6 33
7 45
8 59
9 89
10 110
现在想在程序里动态的按区间统计。
比如 在程序里选择10——50 之间间隔10的有多少个。
希望的结果:
10-20 4
20-30 2
30-40 1
40-50 1这里区间10,50 和间隔10 ,都是可以动态设定的。
这样的sql语句该怎样处理呢,请指教。
谢谢
insert into @t select 1, 20
insert into @t select 2, 15
insert into @t select 3, 5
insert into @t select 4, 60
insert into @t select 5, 12
insert into @t select 6, 33
insert into @t select 7, 45
insert into @t select 8, 59
insert into @t select 9, 89
insert into @t select 10,110declare @p int
set @p=10
select
rtrim(p*@p)+'-'+rtrim((p+1)*@p) as p,
num
from
(select (weight/@p) as p,count(*) as num from @t where weight between 10 and 50 group by (weight/@p)) a/*
p num
------------------------- -----------
10-20 2
20-30 1
30-40 1
40-50 1
*/
要加入统计时间
declare @t table(id int,weight int)
insert into @t select 1, 20
insert into @t select 2, 15
insert into @t select 3, 5
insert into @t select 4, 60
insert into @t select 5, 12
insert into @t select 6, 33
insert into @t select 7, 45
insert into @t select 8, 59
insert into @t select 9, 89
insert into @t select 10,110declare @p int
set @p=10
select
rtrim(p*@p)+'-'+rtrim((p+1)*@p) as p,
num
from
(select (weight/@p) as p,count(*) as num from @t where weight between 10 and 50 group by (weight/@p)
--where 在这里加入你的时间条件
) a当然如果
比如说 10-20不存在记录的话,那么这行会不存在
insert into @t select 1, 20.3
insert into @t select 2, 15.5
insert into @t select 3, 5.6
insert into @t select 4, 60.5
insert into @t select 5, 12.4
insert into @t select 6, 33.2
insert into @t select 7, 45.2
insert into @t select 8, 59.2
insert into @t select 9, 89.1
insert into @t select 10,110.8declare @p int
set @p=10
select
rtrim(p*@p)+'-'+rtrim((p+1)*@p) as p,
num
from
(select (weight/@p) as p,count(*) as num from @t where weight between 10 and 50 group by (weight/@p)) a结果是
p num12.000000000-22.00000000 1
16.000000000-26.00000000 1
20.000000000-30.00000000 1
33.000000000-43.00000000 1
45.000000000-55.00000000 1这样就不符合要求了。
set @p=10
select
rtrim(p*@p)+'-'+rtrim((p+1)*@p) as p,
num
from
(select cast(weight/@p as int) as p,count(*) as num from @t where weight between 10 and 50 group by cast(weight/@p as int)) a