有个表的表名是t_kucun
表里有如下内容
nian yue ri id sr zc jc
2002 12 1 001 100 50 50
2002 12 1 002 200 20 180
2002 12 1 003 50 20 30
2002 12 2 001 50 10 ?
2002 12 2 002 0 20 ?
2002 12 2 003 10 0 ?
这个表是典型的库存表
nian 年 yue 月 ri 日 id 商品代码 sr 收入 zc 支出 jc结存
1日的三种商品的收入、支出、结存都已经有数了，请问怎么用sql语句
算出2日的各商品的结存，也就是问号所在位置的数据？
这个问题的难点在于，不要用游标来实现，要用一条sql语句来实现，看
似简单，实际很难啊！！！
---------------------------------------------------------------
变量 sr_jc,br_sr,br_zc,br_jc
nian1 , yue1, ri1
if ri = 1 then
selcet jc into :sr_jc from table where nian = nian1 and yue = yue1 -1 and ri = '找出上个月最后一天来' id 001
同样取出今日的收入、支出
br_jc = sr_jc +br_sr+br_zc
else
selcet jc into :sr_jc from table where nian = nian1 and yue = yue and ri = ri1-1 id = 001
br_jc = sr_jc +br_sr+br_zc
要考虑到年的变化（包或润年），月的变化，商品的种类
---------------------------------------------------------------
做了半天，强烈建议改成DATETIME类型!
declare @t_kucun table (nian int,yue int,ri int,id varchar(10),sr int,zc int,jc int)
insert @t_kucun select 2002,12,1,'001',100,50,50
union all select 2002,12,1,'002',200,20,180
union all select 2002,12,1,'003',50,20,30
union all select 2002,12,2,'001',50,10,null
union all select 2002,12,2,'002', 0,20,null
union all select 2002,12,2,'003',10, 0,null
update A set jc=(select jc
from @t_kucun
where convert(varchar(10),dateadd(day,1,rtrim(nian)+'-'+right('0'+rtrim(yue),2)+'-'+right('0'+rtrim(ri),2)),120)
=rtrim(A.nian)+'-'+right('0'+rtrim(A.yue),2)+'-'+right('0'+rtrim(A.ri),2) and id=A.id)+sr-zc
from @t_kucun A
where nian=2002 and yue=12 and ri=2
select * from @t_kucun
nian yue ri id sr zc jc
----------- ----------- ----------- ---------- ----------- ----------- -----------
2002 12 1 001 100 50 50
2002 12 1 002 200 20 180
2002 12 1 003 50 20 30
2002 12 2 001 50 10 90
2002 12 2 002 0 20 160
2002 12 2 003 10 0 40
---------------------------------------------------------------
select a.id,a.sr,a.zc,a.jc,b.sr,b.zc,b.jc
from (select id,sum(sr)as sr,sum(zc) as zc,sum(jc) as lc
from t_kucun
where nian = datepart( year, getdate() ) and yue = datepart( month, getdate() ) and ri = datepart( day, getdate() )
group by id) a left join (select id,sum(sr)as sr,sum(zc) as zc,sum(jc) as lc
from t_kucun
where nian = datepart( year,dateadd ( day,1, getdate() ) ) and yue = datepart( month, dateadd ( day,1, getdate() ) ) and ri = datepart( day,dateadd ( day,1, getdate() ) )
group by id) b on a.id = b.id
对吗？
---------------------------------------------------------------
update t_kucun set jc=a.jc +b.chazhi
from(select * from t_kucun where ri=1) as a , (select *,(sr-zc) as chazhi from t_kucun where ri=2) as b
where a.id=b.id and a.id=t_kucun.id and t_kucun.ri=2
对年月的判断没有处理,自己添加吧
---------------------------------------------------------------
从技术的角度来说搂主的想法没错，在不影响效率的前提下，在程序的实现过程中尽量少用存储过程
从数据设计的角度来数，支持j9988(j9988)，愿意很简单，数据库既然设计了datetime这个日期类型，就是我了方便我们这群programer,如果在用yyyy+mm+dd = datetime,岂不辜负了ms的美意了，呵呵！
---------------------------------------------------------------
假设 2日 id无重复,也即sr,zc已是当天的总和,只考虑当前问题给出日期
update a
set jc=case when b.jc is null then a.sr-a.zc else b.jc+a.sr-b.zc end
from t_kucun a ledt join t_kucun b on
a.nian=b.nian and a.yue=b.yue and a.ri=2 and b.ri=1 and a.id=b.id