MSSQL 怎么把查询到的上一行的数据更新到下一行

meiam5 2016-06-03 11:17:20

需要将 BeginMoney + ReceivableMoney = EndMoney

然后将上一行 EndMoney 等于下一行的 BeginMoney

...全文

455 18 打赏收藏转发到动态举报

写回复

用AI写文章

18 条回复

切换为时间正序

请发表友善的回复…

发表回复

meiam5 2016-06-04

打赏
举报

已用游标解决。谢谢各位

meiam5 2016-06-03

打赏
举报

应该说是根据上一行的期末更新下一行的期初让后计算出下一行的期末在更新到下一行循环

meiam5 2016-06-03

打赏
举报

引用 6 楼 xxfvba 的回复:

select id,TermId,CustomerId, BeginMoney=(select sum(BeginMoney) from Base_CustomerMoneyDetial where id<=a.id)+ (select sum(ReceivableMoney) from Base_CustomerMoneyDetial where id<a.id), EndMoney=(select sum(BeginMoney)+sum(ReceivableMoney) from Base_CustomerMoneyDetial where id<=a.id) from Base_CustomerMoneyDetial a WHERE TermID IN ('201606') AND CustomerID='010279'

是现有的数据. 需要根据第一行的期末数据更新成下一行的期初数据因为现在期初这个数据乱了

xxfvba 2016-06-03

打赏
举报

select id,TermId,CustomerId, BeginMoney=(select sum(BeginMoney) from Base_CustomerMoneyDetial where id<=a.id)+ (select sum(ReceivableMoney) from Base_CustomerMoneyDetial where id<a.id), EndMoney=(select sum(BeginMoney)+sum(ReceivableMoney) from Base_CustomerMoneyDetial where id<=a.id) from Base_CustomerMoneyDetial a WHERE TermID IN ('201606') AND CustomerID='010279'

xxfvba 2016-06-03

打赏
举报

--假设只有第一行有BeginMoney,不知道理解的对不对 select id,TermId,CustomerId, BeginMoney=(select sum(BeginMoney) from Base_CustomerMoneyDetial where id<=a.id)+ (select sum(ReceivableMoney) from Base_CustomerMoneyDetial where id<a.id), EndMoney=(select sum(BeginMoney)+sum(ReceivableMoney) from Base_CustomerMoneyDetial where id<=a.id) from Base_CustomerMoneyDetial a

Kongdom12138 2016-06-03

打赏
举报

你的customerID貌似都是一样的吧

meiam5 2016-06-03

打赏
举报

引用 1 楼 neal114 的回复:

用游标？上一行和下一行之前有关系么？比如序号？

ID 没有关系. 不是顺序往下。是根据 CustomerID 我用了游标但是发现没有更新


GO
DECLARE @EndMoney decimal,@id int,@row int
DECLARE contact_cursor CURSOR FOR
SELECT row_number() OVER(order by id) as row,id,EndMoney FROM Base_CustomerMoneyDetial WHERE TermID IN ('201606') AND CustomerID='010279'
FOR UPDATE OF [BeginMoney]


OPEN contact_cursor
FETCH NEXT FROM contact_cursor
INTO @row,@id,@EndMoney
PRINT @row
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE Base_CustomerMoneyDetial set BeginMoney =@EndMoney,EndMoney=@EndMoney+ReceivableMoney+ReceivedMoney  where @row=@row+1
FETCH NEXT FROM contact_cursor
INTO @row,@id,@EndMoney

END

CLOSE contact_cursor
DEALLOCATE contact_cursor
GO

足球不是方的 2016-06-03

打赏
举报

先给查询结果排序，row_numer()。后面用游标或者循环更新都可以了

Kongdom12138 2016-06-03

打赏
举报

用游标？上一行和下一行之前有关系么？比如序号？

linrufu 2016-06-03

打赏
举报

用字典就可以实现的

gw6328 2016-06-03

打赏
举报

引用 14 楼 zhuziqingqing0921 的回复:

我觉得id不一定连续
引用 12 楼 jinfengyiye 的回复:
排序差一行， left join a.id=b.id+1

我说的是排序id，不是他上面那个id.

竹子青青 2016-06-03

打赏
举报

引用 12 楼 jinfengyiye 的回复:

排序差一行， left join a.id=b.id+1

[quote=引用 13 楼 zhuziqingqing0921 的回复:] A1 最小的id 对应的BeginMoney BeginMoney ReceivableMoney EndMoney A1 B1 A1+B1 A1+B1 B2 A1+B1+B2 A1+B1+B2 B3 A1+B1+B2+B3 select id,TermID,CustomerID,(select A1+sum(ReceivableMoney) from table T where TermID=T.TermID and CustomerID=T.CustomerID and id<T.id) as BeginMoney,(select A1+sum(ReceivableMoney) from table T where TermID=T.TermID and CustomerID=T.CustomerID and id<=T.id) as EndMoney from table T

竹子青青 2016-06-03

打赏
举报

我觉得id不一定连续

引用 12 楼 jinfengyiye 的回复:

排序差一行， left join a.id=b.id+1

竹子青青 2016-06-03

打赏
举报

A1 最小的id 对应的BeginMoney BeginMoney ReceivableMoney EndMoney A1 B1 A1+B1 A1+B1 B2 A1+B1+B2 A1+B1+B2 B3 A1+B1+B2+B3 select id,TermID,CustomerID,(select A1+ReceivableMoney from table T where TermID=T.TermID and CustomerID=T.CustomerID and id<T.id) as BeginMoney,(select A1+ReceivableMoney from table T where TermID=T.TermID and CustomerID=T.CustomerID and id<=T.id) as EndMoney from table T

gw6328 2016-06-03

打赏
举报

排序差一行， left join a.id=b.id+1

空白桑 2016-06-03

打赏
举报

--先查询这张表的所有数据到一个临时表#table select id into #table from table order by id --定义变量记录当前这条数据 declare @beginmoney float,@endmoney float,@recmoney float,@upid int; --循环临时表数据 WHILE EXISTS(select id from #table ) begin declare @nowid int; --获取当前循环数据的id select @nowid=id from table where id in(select top 1 id from #table); --判断当上一个ID不为空的时候 upid 就是上一条数据的id 了 if @upid is not null begin --这时更新的数据就相当于是下一条的数据了 update beginmoney = ... from table where id=@nowid end --后面再把当前这条数据赋给上一条下一次循环就相当于是上一条数据了 select @upid=id,@beginmoney =beginmoney,@endmoney=endmoney,@recmoney=recmoney from table where id=@nowid; --删除临时表里的这条数据 delete from #table where id=@nowid; end drop table #table 大概意思是这样 ~

xxfvba 2016-06-03

打赏
举报

做个测试呗，只保留第一行的BeginMoney，清除其他的BeginMoney和EndMoney select id,TermId,CustomerId, BeginMoney=(select sum(BeginMoney) from Base_CustomerMoneyDetial where id<=a.id and TermId=a.TermId and CustomerID=a.CustomerId)+ (select sum(ReceivableMoney) from Base_CustomerMoneyDetial where id<a.id and TermId=a.TermId and CustomerID=a.CustomerId), EndMoney=(select sum(BeginMoney)+sum(ReceivableMoney) from Base_CustomerMoneyDetial where id<=a.id and TermId=a.TermId and CustomerID=a.CustomerId) from Base_CustomerMoneyDetial a WHERE TermID IN ('201606') AND CustomerID='010279'

xxfvba 2016-06-03

打赏
举报

--那这个应该差不多吧 select id,TermId,CustomerId, BeginMoney=(select sum(BeginMoney) from Base_CustomerMoneyDetial where id<=a.id and TermId=a.TermId and CustomerID=a.CustomerId)+ (select sum(ReceivableMoney) from Base_CustomerMoneyDetial where id<a.id and TermId=a.TermId and CustomerID=a.CustomerId), EndMoney=(select sum(BeginMoney)+sum(ReceivableMoney) from Base_CustomerMoneyDetial where id<=a.id and TermId=a.TermId and CustomerID=a.CustomerId) from Base_CustomerMoneyDetial a WHERE TermID IN ('201606') AND CustomerID='010279'