帮写一个游标存储过程

Question

一个表数据
ID  Flag  Amt
1   A1    24
2   A2    32
3   A3    25.4
4   A4    87
5   A5    97
6   A6    56.5
7   A7    12.8传a,b 两个参数，计算前a 行amt的和减去前b 行amt的和
如：a=4,b=3  显示如下结果
ID  Description              Amt
1   A1+A2+A3+A4-(A1+A2+A3)    87
2   A2+A3+A4+A5-(A2+A3+A4)    97
3   A3+A4+A5+A6-(A3+A4+A5)    56.5
4   A4+A5+A6+A7-(A4+A5+A6)    12.8帮写一个带参（a,b）游标存储过程实现上面的功能。

Answer 1

晕，不就是当ID　＞　3　时的amt值　？

Answer 2

create proc P_Test
@id int
as 
select * from tb where id>@id

Answer 3

--> liangCK小梁 于2008-09-16
--> 生成测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (ID int,Flag varchar(2),Amt numeric(3,1))
insert into #T
select 1,'A1',24 union all
select 2,'A2',32 union all
select 3,'A3',25.4 union all
select 4,'A4',87 union all
select 5,'A5',97 union all
select 6,'A6',56.5 union all
select 7,'A7',12.8;--SQL查询如下:declare @a int,@b int;set @a=4;
set @b=3;select top (@a) 
      [description]=STUFF((select top (@a) '+'+Flag from #T where ID>=t.ID for xml path(''))
                           ,1,1,'')+'-('+
                    STUFF((select top (@b) '+'+Flag from #T where ID>=t.ID for xml path(''))
                           ,1,1,'')+')',
      [Amt]=(select SUM(Amt) from #T where ID>=t.ID and ID<t.ID+@a)
           -(select SUM(Amt) from #T where ID>=t.ID and ID<t.ID+@b)
                           
from #T as t;/*
description          Amt
-------------------- ---------------------------------------
A1+A2+A3+A4-(A1+A2+A 87.0
A2+A3+A4+A5-(A2+A3+A 97.0
A3+A4+A5+A6-(A3+A4+A 56.5
A4+A5+A6+A7-(A4+A5+A 12.8(4 行受影响)*/

Answer 4

结果贴错了../*
description               Amt
------------------------- ---------------------------------------
A1+A2+A3+A4-(A1+A2+A3)    87.0
A2+A3+A4+A5-(A2+A3+A4)    97.0
A3+A4+A5+A6-(A3+A4+A5)    56.5
A4+A5+A6+A7-(A4+A5+A6)    12.8(4 行受影响)
*/

Answer 5

怎么我运行出错，我记得SQL中TOP好像不能使用变量。我SQL太菜了，继续请教！

Answer 6

不是，我只是举例说明当a =4;b=3时的运行结果
假如当a=5;b=3的如果就该如下
ID  Description                Amt 
1  A1+A2+A3+A4+A5-(A1+A2+A3)    184 
2  A2+A3+A4+A5+A6-(A2+A3+A4)    153.5
3  A3+A4+A5+A6+A7-(A3+A4+A5)    69.3a,b是变量

Answer 7

------------------------------------
-- Author: happyflystone  
-- Version:V1.001  
-- Date:2008-09-16 19:06:50
-------------------------------------- Test Data: ta
If object_id('ta') is not null 
    Drop table ta
Go
Create table ta(ID int,Flag nvarchar(2),Amt decimal(12,2))
Go
Insert into ta
select 1,'A1',24 union all
select 2,'A2',32 union all
select 3,'A3',25.4 union all
select 4,'A4',87 union all
select 5,'A5',97 union all
select 6,'A6',56.5 union all
select 7,'A7',12.8 
Go
--Start
declare @a int,@b int;set @a=4;
set @b=3;select 
      [Amt]=(select SUM(Amt) from ta where ID between t.ID - @a and t.ID )
           -(select SUM(Amt) from ta where ID between t.ID - @a and t.ID - (@a - @b))
                           
from ta as t
where id > @a - 1set @a=5;
set @b=4;select 
      [Amt]=(select SUM(Amt) from ta where ID between t.ID - @a and t.ID )
           -(select SUM(Amt) from ta where ID between t.ID - @a and t.ID - (@a - @b))
                           
from ta as t
where id > @a - 1set @a=5;
set @b=3;select 
      [Amt]=(select SUM(Amt) from ta where ID between t.ID - @a and t.ID )
           -(select SUM(Amt) from ta where ID between t.ID - @a and t.ID - (@a - @b))
                           
from ta as t
where id > @a - 1--Result:
/*Amt                                      
---------------------------------------- 
87.00
97.00
56.50
12.80（所影响的行数为 4 行）Amt                                      
---------------------------------------- 
97.00
56.50
12.80（所影响的行数为 3 行）Amt                                      
---------------------------------------- 
184.00
153.50
69.30（所影响的行数为 3 行）*/
--End 

Answer 8

关键是帮我解决一下
ID  Description              Amt 
1  A1+A2+A3+A4-(A1+A2+A3)    87 
2  A2+A3+A4+A5-(A2+A3+A4)    97 
3  A3+A4+A5+A6-(A3+A4+A5)    56.5 
4  A4+A5+A6+A7-(A4+A5+A6)    12.8 
结果中Description列如何显示。

Answer 9

2005里写法：
------------------------------------
-- Author: happyflystone  
-- Version:V1.001  
-- Date:2008-09-16 19:06:50
-------------------------------------- Test Data: ta
If object_id('ta') is not null 
    Drop table ta
Go
Create table ta(ID int,Flag nvarchar(2),Amt decimal(12,2))
Go
Insert into ta
select 1,'A1',24 union all
select 2,'A2',32 union all
select 3,'A3',25.4 union all
select 4,'A4',87 union all
select 5,'A5',97 union all
select 6,'A6',56.5 union all
select 7,'A7',12.8 
Go
--Start
create function f_str(@id int,@i int,@j int)
returns varchar(50)
as
begin
declare @s varchar(100)
    select  @s = isnull(@s+'+','')+Flag 
    from (select top (@i) * 
          from ta   
          where id <= @id 
  order by id desc                
         )a
    order by id 
declare @s1 varchar(100)
    select  @s1 = isnull(@s1+'+','')+Flag 
    from (
  select top (@j) *
          from (select top (@i) * 
                from ta   
                where id <= @id 
        order by id desc  ) b   
          order by id asc           
         )a
    order by id 
    return '('+@s+')-('+@s1+')'
end
godeclare @a int,@b int;set @a=4;
set @b=3;select dbo.f_str(id,@a,@b),
      [Amt]=(select SUM(Amt) from ta where ID between t.ID - @a and t.ID )
           -(select SUM(Amt) from ta where ID between t.ID - @a and t.ID - (@a - @b))
                           
from ta as t
where id > @a - 1set @a=5;
set @b=4;select dbo.f_str(id,@a,@b),
      [Amt]=(select SUM(Amt) from ta where ID between t.ID - @a and t.ID )
           -(select SUM(Amt) from ta where ID between t.ID - @a and t.ID - (@a - @b))
                           
from ta as t
where id > @a - 1set @a=5;
set @b=3;select dbo.f_str(id,@a,@b),
      [Amt]=(select SUM(Amt) from ta where ID between t.ID - @a and t.ID )
           -(select SUM(Amt) from ta where ID between t.ID - @a and t.ID - (@a - @b))
                           
from ta as t
where id > @a - 1drop function f_str;
/*
-------------------------------------------------- ---------------------------------------
(A1+A2+A3+A4)-(A1+A2+A3)                           87.00
(A2+A3+A4+A5)-(A2+A3+A4)                           97.00
(A3+A4+A5+A6)-(A3+A4+A5)                           56.50
(A4+A5+A6+A7)-(A4+A5+A6)                           12.80(4 行受影响)                                                   Amt
-------------------------------------------------- ---------------------------------------
(A1+A2+A3+A4+A5)-(A1+A2+A3+A4)                     97.00
(A2+A3+A4+A5+A6)-(A2+A3+A4+A5)                     56.50
(A3+A4+A5+A6+A7)-(A3+A4+A5+A6)                     12.80(3 行受影响)                                                   Amt
-------------------------------------------------- ---------------------------------------
(A1+A2+A3+A4+A5)-(A1+A2+A3)                        184.00
(A2+A3+A4+A5+A6)-(A2+A3+A4)                        153.50
(A3+A4+A5+A6+A7)-(A3+A4+A5)                        69.30(3 行受影响)*/

Answer 10

declare @a int,@b int;set @a=4;
set @b=3;select [description]='('+STUFF((select  '+'+Flag from ta where ID between t.ID - @a+1 and t.ID for xml path(''))
                           ,1,1,'')+')-('+
                    STUFF((select '+'+Flag from ta where ID between t.ID - @a + 1 and t.ID - (@a - @b) for xml path(''))
                           ,1,1,'')+')',
      [Amt]=(select SUM(Amt) from ta where ID between t.ID - @a + 1 and t.ID )
           -(select SUM(Amt) from ta where ID between t.ID - @a + 1 and t.ID - (@a - @b))
                           
from ta as t
description          　   Amt
--------------------　　---------------------------------------
(A1+A2+A3+A4)-(A1+A2+A3)  　  87.00
(A2+A3+A4+A5)-(A2+A3+A4)  　  97.00
(A3+A4+A5+A6)-(A3+A4+A5)  　   56.50
(A4+A5+A6+A7)-(A4+A5+A6) 　 12.80(4 行受影响)

Answer 11

2000的写法：------------------------------------
-- Author: happyflystone  
-- Version:V1.001  
-- Date:2008-09-16 19:06:50
-------------------------------------- Test Data: ta
If object_id('ta') is not null 
    Drop table ta
Go
Create table ta(ID int,Flag nvarchar(2),Amt decimal(12,2))
Go
Insert into ta
select 1,'A1',24 union all
select 2,'A2',32 union all
select 3,'A3',25.4 union all
select 4,'A4',87 union all
select 5,'A5',97 union all
select 6,'A6',56.5 union all
select 7,'A7',12.8 
Go
--Start
create function f_str(@id int,@i int,@j int)
returns varchar(50)
as
begin
declare @s varchar(100)
    select  @s = isnull(@s+'+','')+Flag 
    from ( select *
           from ta 
           where id between @ID - @i + 1 and @ID              
         )a
    order by id  declare @s1 varchar(100)
    select  @s1 = isnull(@s1+'+','')+Flag 
    from (
  select *
           from ta 
           where id between @ID - @i + 1 and @ID - (@i - @j)          
         )a
    order by id 
    return '('+@s+')-('+@s1+')'
end
go
declare @a int,@b int
set @a=4;
set @b=3;
select dbo.f_str(id,@a,@b) ,
      [Amt]=(select SUM(Amt) from ta where ID between t.ID - @a and t.ID )
           -(select SUM(Amt) from ta where ID between t.ID - @a and t.ID - (@a - @b))
                           
from ta as t
where id > @a - 1set @a=5;
set @b=3;select dbo.f_str(id,@a,@b),
      [Amt]=(select SUM(Amt) from ta where ID between t.ID - @a and t.ID )
           -(select SUM(Amt) from ta where ID between t.ID - @a and t.ID - (@a - @b))
                           
from ta as t
where id > @a - 1drop function f_str;
/*
                                                   Amt
-------------------------------------------------- ---------------------------------------
(A1+A2+A3+A4)-(A1+A2+A3)                           87.00
(A2+A3+A4+A5)-(A2+A3+A4)                           97.00
(A3+A4+A5+A6)-(A3+A4+A5)                           56.50
(A4+A5+A6+A7)-(A4+A5+A6)                           12.80(4 行受影响)                                                   Amt
-------------------------------------------------- ---------------------------------------
(A1+A2+A3+A4+A5)-(A1+A2+A3)                        184.00
(A2+A3+A4+A5+A6)-(A2+A3+A4)                        153.50
(A3+A4+A5+A6+A7)-(A3+A4+A5)                        69.30(3 行受影响)*/

Answer 12

[Amt]=(select SUM(Amt) from ta where ID between t.ID - @a and t.ID )
           -(select SUM(Amt) from ta where ID between t.ID - @a and t.ID - (@a - @b))
－－－刚才想了一下，这儿可精减一下：
[Amt]=(select SUM(Amt) from ta where ID between t.ID - (@a - @b)+1 and t.ID  )select dbo.f_str(id,@a,@b),
      [Amt]=(select SUM(Amt) from ta where ID between t.ID - (@a - @b)+1 and t.ID  )
                           
from ta as t
where id > @a - 1

Answer 13

搞定了，太感谢happyflystone了。