declare @tb table (id int,[value] int)
insert into @tb select 1,10
insert into @tb select 2,15
insert into @tb select 3,10
insert into @tb select 4,20
insert into @tb select 5,12select *,
case when id in(select top 2 id from @tb) then 0
else (select cast(avg([value]*1.0) as decimal(10,1)) from @tb
where id in(select top 2 id from @tb where id<a.id order by id desc)) end as av
from @tb aid value av
1 10 0.0
2 15 0.0
3 10 12.5
4 20 12.5
5 12 15.0
insert into @tb select 1,10
insert into @tb select 2,15
insert into @tb select 3,10
insert into @tb select 4,20
insert into @tb select 5,12select *,
case when id in(select top 2 id from @tb) then 0
else (select cast(avg([value]*1.0) as decimal(10,1)) from @tb
where id in(select top 2 id from @tb where id<a.id order by id desc)) end as av
from @tb aid value av
1 10 0.0
2 15 0.0
3 10 12.5
4 20 12.5
5 12 15.0
解决方案 »
- 解释一下这个 过程是如何查询的
- 求Sql语句,不知道怎么写
- cast(a.日期 as varchar(10))=cast(Fdate as varchar(10))
- sql server 2005: 如何获取某一数据库中所有非系统表的表名?
- 关于循环时间段的问题
- 如何删除SQL 2005的计划
- 优化一个考勤的SQL
- sql server表能承担多少条记录,每天一百万条记录,Sql server还能承受啊?
- 关于安装SQL Server 2005的问题
- 在ACCESS中想把字段的数据类型变换,但在变换时会提示:此操作会删除所有的记录.我想知道:这样做是把该字段的值全部删除,还是把所有的值删
- 100分求sql!!!!来者有分
- 连接服务器(link server)
on table1
for insert,update,delete
asdelete a
from table2 a,deleted d
where a.id=d.iddeclare @t table (id int,Value numeric(18,4),Average numeric(18,4))
insert @t
select id,Value,(select avg(value) from (select top 2 value from table1 where id<i.id) as t) as Average
from inserted i--后两条也会影响
insert top 2 @t
select id,Value,(select avg(value) from (select top 2 value from table1 where id<i.id) as t) as Average
from table1 i
where id > (select max(id) from @t)
order by idupdate a
set Value=t.value,Average=t.Average
from table2 a,@t t
where a.id=t.id
insert table2(id,value,Average)
select id,value,Average
from @t t
where not exists (
select 1 from table2 where id=t.id
)go