这个sql存储过程怎么写呀?大家帮忙看一下.数据库sql server2000(100分)

系统中有两个表:
表1(vipzk):字段如下:
vipkh(会员卡号),xm(姓名),dz(地址).......
0001 AA
0002 BB
0003 cc
表2(jfb):字段如下:
vipkh(会员卡号),zjjf(增加积分),csjf(减少积分),fsrq(发生日期),zdr(制单人)
0001 100 0.00 2006-01-01 QQ
0001 100 0.00 2006-01-20 QQ
0001 0.00 50 2006-05-01 qq
0002 100 0 2006-03-01 BB
0002 0 20 2006-05-08 bb
现在想根据'发生日期'查询任意时间段的发生额如下2006-01-20~~~~2006-05-08)
vipkh(会员卡号),xm(姓名),dz(地址),期初结余分数,本期增加,本期减少,本期结余
0001 AA 100 100 50 150
0002 BB 0 100 20 80
0003 cc 0 0 0 0
..............

大家帮忙看一下呀

关注中。。。。。。。。。。。。。。。。。。。。。。。。。。。。

真的没有办法做吗?请sql高手帮忙,兄弟急呀?

期待中~~~~~

提供思路：
“期初结余分数”统计：
Select vipkh(会员卡号),sum(zjjf(增加积分))-sum(csjf(减少积分)) as 期初结余分数 from 表2(jfb) where fsrq(发生日期)<'2006-01-20' group by vipkh(会员卡号)
“本期增加,本期减少”统计：
select vipkh(会员卡号),sum(zjjf(增加积分)) as 本期增加,sum(csjf(减少积分)) as 本期减少 from 表2(jfb) where fsrq(发生日期)>='2006-01-20' and fsrq(发生日期)<='2006-05-08' group by vipkh(会员卡号)
然后把查询结果和表1连接起来，同时计算“本期结余”。
我没有测试。希望对你有所帮助。

随手写的, 不一定正确, 用一个查询就可以了
select
a.vipkh,
a.xm,
a.dz,
(select (sum(x.zjjf)-sum(x.csjf)) as qc from vipkh x where x.vipkh=a.vipkh and x.fsrq<'2006-01-20') as 期初结余分数,
b.本期增加,
b.本期减少,
(b.本期增加-b.本期减少) as 本期结余
from vipzk a,
(select
y.vipkh,
sum(z.zjjf) as 本期增加,
sum(z.csjf) as 本期减少
from vipzk y, jfb z
where y.vipkh=z.vipkh and
z.fsrq>='2006-01-20' and z.fsrq<='2006-05-08 23:59:59' group by z.vipkh) b

where a.vipkh=b.vipkh

可以把
(select (sum(x.zjjf)-sum(x.csjf)) as qc from vipkh x where x.vipkh=a.vipkh and x.fsrq<'2006-01-20') as 期初结余分数,
移到 from 里,这样要快一点

重新改了一下
select
a.vipkh,
a.xm,
a.dz,
c.期初结余分数,
b.本期增加,
b.本期减少,
(b.本期增加-b.本期减少) as 本期结余
from vipzk a,
(select
vipkh,
sum(zjjf) as 本期增加,
sum(csjf) as 本期减少
from jfb
where fsrq>='2006-01-20' and fsrq<='2006-05-08 23:59:59' group by vipkh) b,
(select
vipkh,
(sum(zjjf)-sum(csjf)) as 期初结余分数
from jfb
where fsrq<='2006-01-20' group by vipkh) c

where a.vipkh=b.vipkh and a.vipkh=c.vipkh

guanzhu

存储过程应该和在程序里写一样

还是差不少的，看数据量了。
(积分表)jfb
结构：
CREATE TABLE [dbo].[jfb] (
[vipkh] [int] NOT NULL ,
[zjjf] [int] NULL ,
[jsjf] [int] NULL ,
[fsrq] [datetime] NOT NULL ,
[zbr] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO
数据：
1 100 0 2006-1-20 qq
1 100 0 2006-1-20 qq
1 0 50 2006-1-25 dd
2 100 50 2006-1-20 cc
2 500 100 2006-1-25 cc
2 0 150 2006-1-30 cc
1 200 100 2006-1-30 qq
(vip帐号表)vipzh
结构：
CREATE TABLE [dbo].[vipzh] (
[vipkh] [int] NOT NULL ,
[xm] [char] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[addr] [char] (200) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
数据：
1 aa
2 bb
3 cc
4 dd
5 ee

说明：
vipzh.vipkh,（帐号）
vipzh.xm,（姓名）
vipzh.addr,（地址）
tem1.qcs,（期初数）
tem2.bqzj,（本期增加）
tem2.bqjs,（本期减少）
tem2.bqjy （本期节余）
主要是用了两个left join 连接的。
存储过程：
CREATE procedure Cal
(
@startdate datetime,
@enddate datetime
)
as

begin transaction
select vipzh.vipkh,vipzh.xm,vipzh.addr,tem1.qcs,tem2.bqzj,tem2.bqjs,tem2.bqjy from vipzh
left join(select vipkh,sum(zjjf)-sum(jsjf) as qcs from jfb where fsrq<@startdate group by vipkh) tem1 on vipzh.vipkh=tem1.vipkh
left join(select vipkh,sum(zjjf) as bqzj,sum(jsjf) as bqjs,sum(zjjf)-sum(jsjf) as bqjy from jfb where fsrq between @startdate and @enddate group by vipkh) tem2 on vipzh.vipkh=tem2.vipkh;
commit transaction
GO

执行：exec cal '2006-1-25','2006-1-30'
结果：
1 aa NULL 200 200 150 50
2 bb NULL 50 500 250 250
3 cc NULL NULL NULL NULL NULL
4 dd NULL NULL NULL NULL NULL
5 ee NULL NULL NULL NULL NULL

感觉写的还是复杂了，大家看看有什么改进的地方

上面的那一楼的存储过程 大可不心用到事务就是一个查询语句, 我觉得只有那个查询语句就可以了,
CREATE procedure Cal
(
@startdate datetime,
@enddate datetime
)
as
select vipzh.vipkh,vipzh.xm,vipzh.addr,tem1.qcs,tem2.bqzj,tem2.bqjs,tem2.bqjy from vipzh
left join(select vipkh,sum(zjjf)-sum(jsjf) as qcs from jfb where fsrq<@startdate group by vipkh) tem1 on vipzh.vipkh=tem1.vipkh
left join(select vipkh,sum(zjjf) as bqzj,sum(jsjf) as bqjs,sum(zjjf)-sum(jsjf) as bqjy from jfb where fsrq between @startdate and @enddate group by vipkh) tem2 on vipzh.vipkh=tem2.vipkh;
GO
这样就可以了,
还有就是那个事务没有ROLLBACK语句 就不是算是一个完整的事务, 只有强行的提交那是不对的

哦，知道了，3x.

还是有点不是我想要的数据,请大家在看一看.

你说的是null么？

如果是的话，那就用isnull函数替换为0就行了。
select vipzh.vipkh,vipzh.xm,vipzh.addr,isnull(tem1.qcs,0),isnull(tem2.bqzj,0),isnull(tem2.bqjs,0),isnull(tem2.bqjy,0) from vipzh
left join(select vipkh,sum(zjjf)-sum(jsjf) as qcs from jfb where fsrq<@startdate group by vipkh) tem1 on vipzh.vipkh=tem1.vipkh
left join(select vipkh,sum(zjjf) as bqzj,sum(jsjf) as bqjs,sum(zjjf)-sum(jsjf) as bqjy from jfb where fsrq between @startdate and @enddate group by vipkh) tem2 on vipzh.vipkh=tem2.vipkh;

首先谢谢你hityou,isnull这个我知道.
我的意思是:如果指定的时间段内没有发生额,'本期结余'(bqjy)应该等于'期初数'(qcs),而现在本期结余等于0.请大侠再想一想方法.

本期结余(bqjy)算的好像不正确.它应该等期初余额(qcs)+sum(zjjf)-sum(jsjf)

请hityou大侠帮助我想一下方法.我实在是想不出来