ID Num
1 11
2 22
3 33
...
100 111
要得到
GRP_ID Num_Avg
1 (ID为1~10的Num的平均值)
...
10 (ID为91~100的Num的平均值)
1 11
2 22
3 33
...
100 111
要得到
GRP_ID Num_Avg
1 (ID为1~10的Num的平均值)
...
10 (ID为91~100的Num的平均值)
AVG(NUM) as Num_AVG
FROM TableName
GROUP BY CEILING(ID/10.0)
goselect top 100
id = identity(int,1,1),
num = convert(int,substring(convert(varchar(20),abs(checksum(newid()))),1,2))
into #
from sys.objects a,sys.objects b
goselect
a.id as grp_id,
avg(b.id) as avg_num
from # a,# b
where a.id <=10
and b.id between ((a.id - 1) * 10 + 1) and (a.id * 10)
group by a.id
godrop table #
go
--这有个笨方法:
declare @t table(id int,Num int)
declare @i int
set @i = 1
while @i<=100
begin
insert into @t
select @i,@i
set @i=@i+1
end
select * from @t
select id as GRP_ID ,avg(Num) as Num_Avg
from
(select id = (case when id>=1 and id<=10 then 1
when id>=11 and id<=20 then 2
......
when id>=21 and id<=30 then 10
end),Num from @t) a
group by id
DECLARE @TB TABLE(ID int,Num int)
INSERT @TB
SELECT 1, 10 UNION ALL
SELECT 2, 20 UNION ALL
SELECT 3, 30 UNION ALL
SELECT 4, 40 UNION ALL
SELECT 5,50 UNION ALL
SELECT 6,60 UNION ALL
SELECT 7,70 UNION ALL
SELECT 8,80 UNION ALL
SELECT 9,90 UNION ALL
SELECT 10,20 UNION ALL
SELECT 11,20 UNION ALL
SELECT 12,20 UNION ALL
SELECT 13,20 UNION ALL
SELECT 14,20 UNION ALL
SELECT 15,20 UNION ALL
SELECT 16,20 UNION ALL
SELECT 17,20 UNION ALL
SELECT 18,20 UNION ALL
SELECT 19,20 UNION ALL
SELECT 20,20--SELECT ID,Num,CASE WHEN ID=0 THEN 1 ELSE (ID-1)/10+1 END AS ID2 FROM @TB
SELECT ID2,SUM(Num) AS Num FROM (
SELECT ID,Num,CASE WHEN ID=0 THEN 1 ELSE (ID-1)/10+1 END AS ID2 FROM @TB) A
GROUP BY ID2