题目:按不同供应商,本期结存金额=(应付金额-已付金额)+上期结存金额
CREATE TABLE [CW_付款] (
[供应商代码] [int] NULL ,
[应付金额] [numeric](10, 2) NULL ,
[已付金额] [numeric](10, 2) NULL ,
[结存金额] [numeric](10, 2) NULL ,
) ON [PRIMARY]
GO
insert into CW_付款(供应商代码,应付金额,已付金额,结存金额)
select 1,10,5,0
union all select 1,15,10,0
union all select 2,20,5,0
union all select 2,10,0,0
原始数据:
select * from [CW_付款]
---返回结果:
供应商代码 应付金额 已付金额 结存金额
----------- ------------ ------------ ------------
1 10.00 5.00 .00
1 15.00 10.00 .00
2 20.00 5.00 .00
2 10.00 .00 .00
需要得到如下的结果:
供应商代码 应付金额 已付金额 结存金额
----------- ----------- ----------- -----------
1 10 5 5
1 15 10 10
2 20 5 15
2 10 0 25
由于原始表中没有id,所以用到了中间表,而且将每期的"应付金额-已付金额"放在中间表的 差值 列中
之后可以只针对 差值进行操作(不再照顾 应付金额、已付金额)
查询代码如下:
select id=identity(int,1,1)
,供应商代码
,应付金额=cast(应付金额 as int)
,已付金额=cast(已付金额 as int)
,差值=cast(应付金额-已付金额 as int)
,结存金额=cast(结存金额 as int)
into #t
from CW_付款
![](/Images/OutliningIndicators/None.gif)
select 供应商代码,应付金额,已付金额
,结存金额=(b.差值+isnull((select a.差值 from #t a
where a.id=(select max(id)
from #t c
where c.供应商代码=b.供应商代码 and c.id<b.id)),0)
)from #t as b
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
原始数据:
select * from [CW_付款]
---返回结果:
供应商代码 应付金额 已付金额 结存金额
----------- ------------ ------------ ------------
1 10.00 5.00 .00
1 15.00 10.00 .00
2 20.00 5.00 .00
2 10.00 .00 .00
需要得到如下的结果:
供应商代码 应付金额 已付金额 结存金额
----------- ----------- ----------- -----------
1 10 5 5
1 15 10 10
2 20 5 15
2 10 0 25
由于原始表中没有id,所以用到了中间表,而且将每期的"应付金额-已付金额"放在中间表的 差值 列中
之后可以只针对 差值进行操作(不再照顾 应付金额、已付金额)
查询代码如下:
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)