ID getpartno serviceno partcode amount returnamount
106646 LL20100910-0005 WX20100910-0001 056.129.620-T -1.0000000000 .0000000000
106648 LL20100910-0006 WX20100910-0001 058.133.843YH 1.0000000000 .0000000000
106979 LL20100906-0008 WX20100906-0007 058.133.843YH 1.0000000000 .0000000000
107258 LL20100911-0022 WX20100911-0014 058.133.843YH 1.0000000000 .0000000000
108036 LL20100927-0030 WX20100927-0011 058.133.843YH -1.0000000000 .0000000000
107002 LL20100906-0031 WX20100906-0017 06430-S0A-J00 1.0000000000 .0000000000
107035 LL20100907-0010 WX20100906-0014 06-AGL855B 1.0000000000 .0000000000
108178 LL20100929-0014 WX20100929-0006 06-AGL855B 1.0000000000 .0000000000
108323 LL20100930-0027 WX20100930-0003 06B.115.611B -1.0000000000 .0000000000
103520 LL20100903-0019 WX20100607-0005 0711.9902.267 4.0000000000 .0000000000
103521 LL20100903-0019 WX20100607-0005 0712.9901.655 3.0000000000 .0000000000
102973 LL20100918-0006 WX20100918-0003 078.115.561D 1.0000000000 .0000000000
107236 LL20100911-0008 WX20100911-0006 078.115.561D -1.0000000000 .0000000000
107325 LL20100913-0002 WX20100911-0006 078.115.561D 1.0000000000 .0000000000
102819 LL20100917-0023 WX20100907-0011 078.905.101A 1.0000000000 .0000000000
103122 LL20100928-0008 WX20100928-0002 08215-99954 1.0000000000 .0000000000
106910 LL20100905-0004 WX20100905-0004 08215-99954 1.0000000000 .0000000000
107602 LL20100917-0004 WX20100916-0018 089-4015-PF 2.0000000000 .0000000000
在serviceno ,partcode 相同的情况下 根据amount如果是负数,就打到正数对应的将returnamount更新为正数的amount
106646 LL20100910-0005 WX20100910-0001 056.129.620-T -1.0000000000 .0000000000
106648 LL20100910-0006 WX20100910-0001 058.133.843YH 1.0000000000 .0000000000
106979 LL20100906-0008 WX20100906-0007 058.133.843YH 1.0000000000 .0000000000
107258 LL20100911-0022 WX20100911-0014 058.133.843YH 1.0000000000 .0000000000
108036 LL20100927-0030 WX20100927-0011 058.133.843YH -1.0000000000 .0000000000
107002 LL20100906-0031 WX20100906-0017 06430-S0A-J00 1.0000000000 .0000000000
107035 LL20100907-0010 WX20100906-0014 06-AGL855B 1.0000000000 .0000000000
108178 LL20100929-0014 WX20100929-0006 06-AGL855B 1.0000000000 .0000000000
108323 LL20100930-0027 WX20100930-0003 06B.115.611B -1.0000000000 .0000000000
103520 LL20100903-0019 WX20100607-0005 0711.9902.267 4.0000000000 .0000000000
103521 LL20100903-0019 WX20100607-0005 0712.9901.655 3.0000000000 .0000000000
102973 LL20100918-0006 WX20100918-0003 078.115.561D 1.0000000000 .0000000000
107236 LL20100911-0008 WX20100911-0006 078.115.561D -1.0000000000 .0000000000
107325 LL20100913-0002 WX20100911-0006 078.115.561D 1.0000000000 .0000000000
102819 LL20100917-0023 WX20100907-0011 078.905.101A 1.0000000000 .0000000000
103122 LL20100928-0008 WX20100928-0002 08215-99954 1.0000000000 .0000000000
106910 LL20100905-0004 WX20100905-0004 08215-99954 1.0000000000 .0000000000
107602 LL20100917-0004 WX20100916-0018 089-4015-PF 2.0000000000 .0000000000
在serviceno ,partcode 相同的情况下 根据amount如果是负数,就打到正数对应的将returnamount更新为正数的amount
=====
what's mean?
amount如果是负数,则更新returnamount 此amount的绝对值,正数
那如果是amount是正数,怎么处理?
107325 LL20100913-0002 WX20100911-0006 078.115.561D 1.0000000000 .0000000000
找到这两列,是不是更新ID=107236行,的returnccout为第二行的account
--serviceno ,partcode
;with cte as
(
select * from tb
where exists(select 1 from tb t where tb.serviceno=t.serviceno and tb.partcode=t.partcode and sign(tb.account)!=sign(t.account))
)
update tb set retureaccount=cte.account from tb inner join cte where tb.serviceno=cte.serviceno and tb.partcode=cte.partcode and sign(tb.account)=-1 and sign(cte.account)=1