先建表，插入测试数据
--正积分表CREATE table tb1 ( [memberId] [nvarchar](50) NOT NULL, [pointProduceTime] [nvarchar](50) NOT NULL, [type] [nvarchar](50) NULL, [point] [int] NULL)INSERT INTO tb1 VALUES ('1007307','2017-02-06 00:00:00','1',360)INSERT INTO tb1 VALUES ('1007307','2017-03-17 00:00:00','1',930)INSERT INTO tb1 VALUES ('1007307','2017-03-19 00:00:00','2',890)INSERT INTO tb1 VALUES ('1007307','2017-06-09 21:52:14','1',700)INSERT INTO tb1 VALUES ('1007307','2017-08-28 22:26:12','1',1090)INSERT INTO tb1 VALUES ('1007307','2017-10-23 21:16:29','1',1330)--负积分表CREATE table tb2( [memberId] [nvarchar](50) NOT NULL, [pointProduceTime] [nvarchar](50) NOT NULL, [type] [nvarchar](50) NULL, [point] [int] NULL)INSERT INTO tb2 VALUES ('1007307','2017-09-23 21:04:50','1',-1090)INSERT INTO tb2 VALUES ('1007307','2017-11-10 12:56:21','5',-2500) 表tb1：
表tb2：
在不知道每次需要扣减多少积分的情况下，需使用游标遍历数据
--正积分SELECT IDENTITY(INT,1,1) as id,memberId,pointProduceTime,type,point,point as lesspointINTO #tb1FROM tb1--负积分SELECT IDENTITY(INT,1,1) as id,memberId,pointProduceTime,type,pointINTO #tb2FROM tb2declare @inid intdeclare @innum intdeclare @indate datedeclare @outid intdeclare @outnum intdeclare @outdate dateDECLARE @lessnum int--负积分游标declare xrxc_cursorf cursor for select id,pointProduceTime,point from #tb2 order by pointProduceTimeopen xrxc_cursorf fetch next from xrxc_cursorf into @outid,@outdate,@outnum --遍历每行数据WHILE @@FETCH_STATUS=0BEGIN --正积分游标 declare xrxc_cursorz cursor for select id,pointProduceTime,point,lesspoint from #tb1 where lesspoint>0 order by pointProduceTime open xrxc_cursorz fetch next from xrxc_cursorz into @inid,@indate,@innum,@lessnum --遍历每行数据 WHILE @@FETCH_STATUS=0 BEGIN --方法参考一 IF @outnum<=0 begin IF @innum >= @outnum*-1 begin update #tb1 SET lesspoint=@outnum+@innum where id=@inid end else begin update #tb1 SET lesspoint=0 where id=@inid end SET @outnum = @outnum+@lessnum end else update #tb1 SET lesspoint=@lessnum where id=@inid fetch next from xrxc_cursorz into @inid,@indate,@innum,@lessnum /* --方法参考二 set @outnum=@outnum+@lessnum IF @outnum<=0 begin update #tb1 SET lesspoint=0 where id=@inid end else begin IF @outnum<@innum BEGIN update #tb1 SET lesspoint=@outnum where id=@inid end end fetch next from xrxc_cursorz into @inid,@indate,@innum,@lessnum */ END close xrxc_cursorz deallocate xrxc_cursorz fetch next from xrxc_cursorf into @outid,@outdate,@outnumENDclose xrxc_cursorfdeallocate xrxc_cursorfselect * from #tb1select * from #tb2 DROP TABLE #tb1DROP TABLE #tb2
结果表：
