大家看一下这个sql语句如何写? sql server2000数据库 在线等.......(50分)

有一个数据库jfb(积分表)<br>vipkh(会员卡号),zjjf(增加积分),csjf(减少积分),fsrq(发生日期),zdr(制单人)<br> &nbsp; &nbsp;0001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 100 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.00 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2006-01-01 &nbsp; &nbsp; &nbsp; QQ<br> &nbsp; &nbsp;0001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 100 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.00 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2006-01-20 &nbsp; &nbsp; &nbsp; QQ<br> &nbsp; &nbsp;0001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.00 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 50 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2006-05-01 &nbsp; &nbsp; &nbsp; qq<br> &nbsp; &nbsp;0002 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 100 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2006-03-01 &nbsp; &nbsp; &nbsp; &nbsp;BB<br> &nbsp; &nbsp;0002 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2006-05-08 &nbsp; &nbsp; &nbsp; bb<br>写实现如下功能:<br> &nbsp; 根据查询时间段(2006-01-02~~~~2006-05-05) and vipkh=0001得到如下明细<br> &nbsp; &nbsp;期初结余:100 <br> &nbsp; &nbsp;vipkh &nbsp; &nbsp; &nbsp; 本期增加 &nbsp; &nbsp;本期减少 &nbsp; &nbsp; 本期结余<br> &nbsp; &nbsp;0001 &nbsp; &nbsp; &nbsp; &nbsp;100 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 200<br> &nbsp; &nbsp;0001 &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;50 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;150<br>关健是本期结余如何算出来.请大家帮帮忙 可以写存储过程,

只算增加的本期结余<br>select &nbsp;vipkh,selectlist.....,(select sum(zjjf-csjf) from jfb &nbsp;where &nbsp;fsrq&lt;'2006-01-02' and &nbsp;vipkh=a.vipkh)+(select sum(zjjf) from jfb where fsrq betweem '2006-01-01' and 2006-05-05 and &nbsp;vipkh=a.vipkh) as 本期结余 from jfb a where a.fsrq betweem '2006-01-01' and 2006-05-05 and &nbsp;vipkh='0001'<br>union<br>计算所有的本期结余<br>select &nbsp;vipkh,selectlist.....,(select sum(zjjf-csjf) from jfb &nbsp;where &nbsp;fsrq&lt;='2006-05-05' and &nbsp;vipkh=a.vipkh) as 本期结余 from jfb a where a.fsrq betweem '2006-01-01' and 2006-05-05 and &nbsp;vipkh='0001'<br>其他字段自己添加就可以了

同上所方法！！

我先试一下先

对于上下文关联的数字我一般都是得在本地另外建立临时表，然后搬一条纪录算一次，好像感觉这样最快最直接，修改也方便。<br><br>如果能排除顺序的问题，最后那个数也存在可以直接sum出来的可能性。<br><br>2种情况lz考虑。<br><br>虽然我sql用了很久，但是碰到这样的问题一样也是很烦恼，希望能听到高手们的高见啊。

to williamlea<br><br>辛苦啊，写了这么多。<br>不过是好像没有每一行的结余数啊，我只是看了半天，没调试看，感觉你只是算了头尾的结余数。<br>谢谢赐教。

是呀~~我运行了一下还是不太正确!!<br>现在期初结余这块我已经算出来了.就是如下明细的本期结存算不出来:<br>vipkh &nbsp; &nbsp; &nbsp; 本期增加 &nbsp; &nbsp;本期减少 &nbsp; &nbsp; 本期结余<br> &nbsp; &nbsp;0001 &nbsp; &nbsp; &nbsp; &nbsp;100 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 200<br> &nbsp; &nbsp;0001 &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;50 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;150<br>请高手在帮忙看看~~~

你的本期结余应该是从该查询时间段的起始时间之前的时间段算出来的<br>也就是说你要算（2006-01-02~~~~~2006-05-05）之间的你要先算出<br>2006-01-02之前的（增加-减少）结余然后在和本时间段的进行计算<br>因为条件不同一个（2006-01-02~~~~~2006-05-05）之间一个在2006-01-02<br>之前在涉及到聚集函数建议用一下临时表或存取过程，在一个查询语句中是<br>很难实现的<br>先建一这样的临时表（hzlsb--汇总临时表）并从jfb导入数据如下：<br>vipkh(会员卡号),zjjf(增加积分),csjf(减少积分),fsrq(发生日期),zdr(制单人)，bqjy(本期结余)<br> &nbsp; &nbsp;0001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 100 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.00 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2006-01-01 &nbsp; &nbsp; &nbsp; QQ &nbsp; &nbsp; &nbsp;null<br> &nbsp; &nbsp;0001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 100 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.00 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2006-01-20 &nbsp; &nbsp; &nbsp; QQ &nbsp; &nbsp; &nbsp;null<br> &nbsp; &nbsp;0001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.00 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 50 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2006-05-01 &nbsp; &nbsp; &nbsp; qq &nbsp; &nbsp; &nbsp;null<br> &nbsp; &nbsp;0002 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 100 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2006-03-01 &nbsp; &nbsp; &nbsp; &nbsp;BB &nbsp; &nbsp; null<br> &nbsp; &nbsp;0002 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2006-05-08 &nbsp; &nbsp; &nbsp; bb &nbsp; &nbsp; &nbsp;null<br>然后更新表：<br>update hzlsb set bqjy=sum(jfb.zjjf-jfb.csjf)<br> &nbsp;from jfb<br> &nbsp;where jfb.fsrq&lt;fsrq and vipkh=jfb.vipkh<br>然后在执行以下查询：<br>select vipkh,sum(jfb.zjjf) as 本增,sum(jfb.csjf) as 本减,hzlsb.bqjy as 本期结余<br>from jfb,hzlsb<br>where fsrq&lt;='2006-.....' and fsrq&lt;='200.......' and jfb.vipkh=hzlsb.vipkh<br>注意临时表的字段最好和实表的区别开，临时表的有的字段也多余可去掉看着办吧 试一下吧<br>呵呵

select vipkh,zjjf,csjf,(select sum(b.zjjf-b.csjf) from jfb b where b.fsrq&lt;=a.fsrq) bqjy<br>from jfb a<br>where fsrq between '2006-01-02' and '2006-05-05'

还是不行,大家在看一下

我是用自定义函数做的,<br>create function ss_func(@qcs numeric(10,2),@bg datetime,@ed datetime,@kh int)<br>returns numeric(10,2)<br>as<br>begin<br>declare @num numeric(10,2)<br>set @num=@qcs<br>select @num=<br> &nbsp; &nbsp; &nbsp; case<br> &nbsp; &nbsp; &nbsp; when fsrq=@bg then @qcs<br> &nbsp; &nbsp; &nbsp; else @num+isnull(zjjf,0)-isnull(jsjf,0)<br> &nbsp; &nbsp; &nbsp; end <br> &nbsp; &nbsp; &nbsp; from jfb where vipkh=@kh and fsrq between @bg and @ed<br>return @num<br>end<br>--drop function ss_func<br><br>select vipkh,zjjf,jsjf,dbo.ss_func(100,'2006-01-02',fsrq,1) from jfb <br> &nbsp; &nbsp; &nbsp;where vipkh=1 and fsrq between 2006-01-02'and '2006-05-05'<br><br>函数的四个参数分别是 期初值,开始时间,fsrq字段,卡号,

来自：hityou, 时间：2006-5-19 15:40:14, ID：3447458<br>我是用自定义函数做的,<br>create function ss_func(@qcs numeric(10,2),@bg datetime,@ed datetime,@kh int)<br>returns numeric(10,2)<br>....<br><br>楼上辛苦了，诚然这样子是解决问题的方法，可是写函数到server中好像有点罗嗦啊，比如迁徙数据、字段变更、日后修改（参数定义）等，回头都很麻烦啊，那还不如create一个tmp表，先算一下，再drop，那不是更方便？<br><br>楼上高手！<br>能说一说做成函数的好处不？谢谢。

/*<br><br>create table jfb (vipkh int, zjjf int, csjf int, fsrq datetime)<br><br>insert into jfb values(1,100,0,'2006-1-1')<br>insert into jfb values(1,100,0,'2006-1-20')<br>insert into jfb values(1,0,50,'2006-5-1')<br><br>drop table jfb<br>*/<br><br>select vipkh,zjjf,csjf,(select sum(b.zjjf-b.csjf) from jfb b where b.fsrq&lt;=a.fsrq) bqjy<br>from jfb a<br>where fsrq between '2006-01-02' and '2006-05-05'<br><br>哪错了???

你那里面没有期初值,而且,在他的表里,可能有比开始日期还要早的数据,还有就是你的两个表卡号没有关联,所以不对.<br>把你的改成如下就可以了<br>select vipkh,zjjf,jsjf,(select sum(b.zjjf-b.jsjf)+100 from jfb b where b.fsrq between '2006-01-01' and a.fsrq and b.vipkh=a.vipkh) bqjy<br>from jfb a<br>where fsrq between '2006-01-01' and '2006-02-01' order by a.vipkh<br><br>不要期初值也可以,只要他的期初值也是通过这个表统计出来的<br>select vipkh,zjjf,jsjf,(select sum(b.zjjf-b.jsjf) from jfb b <br> &nbsp; &nbsp; &nbsp; &nbsp;where b.fsrq&lt;=a.fsrq and b.vipkh=a.vipkh) bqjy<br>from jfb a<br>where fsrq between '2006-01-01' and '2006-02-01' order by a.vipkh

to jenhon<br>呵呵,我最近用到了写了一个类似的东西,所以上来就直接改的原来的,其实直接用sql可以实现的,就是觉得用函数写的sql比较简短,容易理解.而且内置表函数可以交给数据库管理员写啊,不用程序员的.

来自：hityou, 时间：2006-5-19 16:47:52, ID：3447539<br>to jenhon<br>呵呵,我最近用到了写了一个类似的东西,所以上来就直接改的原来的,其实直接用sql可以实现的,就是觉得用函数写的sql比较简短,容易理解.而且内置表函数可以交给数据库管理员写啊,不用程序员的. &nbsp;<br><br><br>受教了。

楼上的兄弟 贴出来让大家看看

to: hityou<br> 我试了一下觉得如下方法差不多,不过还在点问题:帮助看一下:<br> &nbsp;select vipkh,zjjf,jsjf,(select sum(b.zjjf-b.jsjf) from jfb b <br> &nbsp; &nbsp; &nbsp; &nbsp;where b.fsrq&lt;=a.fsrq and b.vipkh=a.vipkh) bqjy<br>from jfb awhere fsrq between '2006-01-01' and '2006-02-01' order by a.vipkh<br>按照这个方法,如果本日内有多笔业务发生,就会出现本日的结存数都会相同.例如:<br>期初结余:100<br> 发生日期 &nbsp; &nbsp; &nbsp; &nbsp;会员卡号 &nbsp; &nbsp; &nbsp; 本期增加 &nbsp; &nbsp;本期减少 &nbsp; &nbsp; 本期结余<br>2006-06-01 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;100 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 300<br>2006-06-01 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;100 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 300<br>2006-06-02 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;50 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;250<br>如何改为:<br>期初结余:100<br> 发生日期 &nbsp; &nbsp; &nbsp; &nbsp;会员卡号 &nbsp; &nbsp; &nbsp; 本期增加 &nbsp; &nbsp;本期减少 &nbsp; &nbsp; 本期结余<br>2006-06-01 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;100 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 200<br>2006-06-01 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;100 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 300<br>2006-06-02 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;50 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;250<br>谢谢啦

这个问题还是没有解决

如果再建一个剩余表，如以下字段(会员卡号）（前期结余）（本期结余）(发生日期)(制单人)<br>在提交数据时同时对剩余表操作：获取（前期结余）直接处理（增加积分）或（减少积分）最后添加（本期结余）不就更简单了。