假如一个表中的数据
id flag price
1 A1 23
2 A2 12
3 A3 45
5 A4 12
6 A5 25
写一个带参(a)的存储过程实现前a 行的price的 和
例如a=3;
那么A1+A2+A3=?
A2+A3+A4=?
A3+A5+A5=?
A4+A5+A6=?
要是a=4
那么A1+A2+A3+A4=?
A2+A3+A4+A5=?
A3+A4+A5+A6=?
id flag price
1 A1 23
2 A2 12
3 A3 45
5 A4 12
6 A5 25
写一个带参(a)的存储过程实现前a 行的price的 和
例如a=3;
那么A1+A2+A3=?
A2+A3+A4=?
A3+A5+A5=?
A4+A5+A6=?
要是a=4
那么A1+A2+A3+A4=?
A2+A3+A4+A5=?
A3+A4+A5+A6=?
解决方案 »
- SQL表的查询与更新,麻烦大侠救命
- 我创建一个复制数据库的包将远程数据库复制到本地来,为什么这个包却保存在远程的主机了不是在本地sql server上?
- SQL 2005中怎样查询存储过程和view或表什么时候被修改
- 怎么判断某个月有多少天呢??
- 为什么 查询器 里看见的表是 只读 的
- 還是昨天的問題,我想知道究竟該怎么解決
- 我现在要判断一个格式为2002-**-** **:**:**的时间是否在两个格式都为2002****之间,找出相应的记录,该怎么写?
- 网上邻居中,两部机子都装了sql server应该如何配置才能从一部机连到另一部机上呢?
- 求助 Sql Express 安装问题
- 如何對TABLE進行這樣的操作(急)
- 新手提问,ACCESS数据库布局问题。
- 有汉字列的查询语句查不出结果!!!急救!!!
@TopN int
AS DECLARE @t INT
--ID值是连续的就可以这样取
SET @t=(SELECT SUM(price) FROM TableName WHERE ID<=@TopN) SELECT @t AS TotalPrice
CREATE PROC spGetTotalPrice
@TopN int
AS DECLARE @t INT
select iD,
(select top (@TopN) sum(price)
from @t
where id>=a.id
) as total
from @t a
(
[id] int ,
flag varchar(2),
price int
)
insert into t values(1,'A1',23)
insert into t values(2,'A2',12)
insert into t values(3,'A3',45)
insert into t values(5,'A4',12)
insert into t values(6,'A5',25)create procedure t_proc
(@a int)
as
declare @price int
declare @sum_price int
set @sum_price = 0declare c_t cursor for
select top(@a) price from t
open c_t
fetch next from c_t into @price
while(@@fetch_status = 0)
begin
set @sum_price = @sum_price + @price
fetch next from c_t into @price
end
close c_t
deallocate c_t
print @sum_price
goexec t_proc 4
@a int
as begin
select
id
, sum_price = (select sum(price) from tablename b where b.id>=a.id and b.id<=a.id+@a)
from tablename a
end
go
(
[id] int ,
flag varchar(2),
price int
)
insert into t values(1,'A1',23)
insert into t values(2,'A2',12)
insert into t values(3,'A3',45)
insert into t values(5,'A4',12)
insert into t values(6,'A5',25)
goalter procedure proc_getTotalPrice
@a int
as beginselect *,(select count(*) from t where id<=a.id) as id0
into #
from t aselect id,
sum_price = (select sum(price) from # b where b.id0>=a.id0 and b.id0<=a.id0+@a-1)
from # a
where a.id0<=(select max(id0) from #)-@a+1end
go
exec proc_getTotalPrice 3go--结果
id sum_price
----------- -----------
1 80
2 69
3 82(所影响的行数为 3 行)
insert into tb select 1, 'A1', 23 union all select
2 , 'A2' , 12 union all select
3 , 'A3' , 45 union all select
5 , 'A4' , 12 union all select
6 , 'A5' , 25
go
create procedure gettotal
@topN int
as
declare @a table(s_id int identity(1,1),price int)
insert into @a (price) select price from tbselect a.s_id,
(select sum(price) from @a where s_id>=a.s_id and s_id<a.s_id+@topN) as sum_price
from @a a
where a.s_id<=(select max(s_id) from @a)-@topN+1
go
exec gettotal 3
go
drop procedure gettotal
drop table tb
/*
s_id sum_price
----------- -----------
1 80
2 69
3 82(3 行受影响)
*/