日期:2014-05-18 浏览次数:20649 次
create table tb(num int,id int,va int) insert into tb select 1,null,1 union all select 1,null,2 union all select 1,null,3 union all select 1,null,4 union all select 1,null,5 union all select 2,null,6 union all select 2,null,7 union all select 3,null,8 union all select 3,null,9 union all select 3,null,1 union all select 3,null,2 union all select 3,null,3 union all select 4,null,4 union all select 4,null,5 union all select 4,null,6 union all select 4,null,7 go declare @cnt int set @cnt = 3 ;with ach as ( select *,px=row_number() over (partition by num order by getdate()) from tb ) select num,(px-1)/@cnt+1 as zuhao,cast(avg(va*1.) as decimal(18,2)) as a_value --平均 from ach group by num,(px-1)/@cnt order by num drop table tb /**************** num zuhao a_value ----------- -------------------- --------------------------------------- 1 1 2.00 1 2 4.50 2 1 6.50 3 1 6.00 3 2 2.50 4 1 5.00 4 2 7.00 (7 行受影响)