利用update计算分配剩余金额填充金额补齐金额余额

利用update计算分配剩余金额/填充金额/补齐金额/余额 (2019-01-10 11:28:19)转载▼
分类： 数据库
—利用update 计算分配剩余金额/填充金额/补齐金额
DECLARE @t table(id INT IDENTITY(1,1),n VARCHAR(100), j MONEY,jf money,l MONEY,y money)
INSERT INTO @t
SELECT ‘aaa’,100,100,0,0
UNION ALL
SELECT ‘aaa’,200,200,0,0
UNION ALL
SELECT ‘aaa’,300,300,0,0
UNION ALL
SELECT ‘aaa’,400,400,0,0
DECLARE @m FLOAT
DECLARE @y FLOAT
—SET @m = 3000
set @m = 550
select ‘原金额’ + CONVERT(VARCHAR, @m)
UPDATE @t SET y = @y,l=@m, jf=CASE when @y >= jf THEN 0 else jf-@y END,@y= @m,@m = CASE WHEN @y >= jf then @y - jf ELSE 0 END WHERE n = ‘aaa’
SELECT FROM @t
select ‘原金额剩余金额’ + CONVERT(VARCHAR, @m)
SELECT ,’剩余未勾兑业务金额’ FROM @t WHERE jf >0
—也可以计算合计，达到子查询sum(a)-sum(b) 计算的效果