先建表，插入测试数据
--正积分表
CREATE table tb1
(
[memberId] [nvarchar](50) NOT NULL,
[pointProduceTime] [nvarchar](50) NOT NULL,
[type] [nvarchar](50) NULL,
[point] [int] NULL
)
INSERT INTO tb1 VALUES ('','2017-02-06 00:00:00','',360)
INSERT INTO tb1 VALUES ('','2017-03-17 00:00:00','',930)
INSERT INTO tb1 VALUES ('','2017-03-19 00:00:00','',890)
INSERT INTO tb1 VALUES ('','2017-06-09 21:52:14','',700)
INSERT INTO tb1 VALUES ('','2017-08-28 22:26:12','',1090)
INSERT INTO tb1 VALUES ('','2017-10-23 21:16:29','',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 ('','2017-09-23 21:04:50','',-1090)
INSERT INTO tb2 VALUES ('','2017-11-10 12:56:21','',-2500)
表tb1：
表tb2：
在不知道每次需要扣减多少积分的情况下，需使用游标遍历数据
--正积分
SELECT IDENTITY(INT,1,1) as id,memberId,pointProduceTime,type,point,point as lesspoint
INTO #tb1
FROM tb1 --负积分
SELECT IDENTITY(INT,1,1) as id,memberId,pointProduceTime,type,point
INTO #tb2
FROM tb2 declare @inid int
declare @innum int
declare @indate date
declare @outid int
declare @outnum int
declare @outdate date
DECLARE @lessnum int
--负积分游标
declare xrxc_cursorf cursor
for
select id,pointProduceTime,point from #tb2 order by pointProduceTime
open xrxc_cursorf
fetch next from xrxc_cursorf into @outid,@outdate,@outnum --遍历每行数据
WHILE @@FETCH_STATUS=0
BEGIN
--正积分游标
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,@outnum
END
close xrxc_cursorf
deallocate xrxc_cursorf
select * from #tb1
select * from #tb2 DROP TABLE #tb1
DROP TABLE #tb2
结果表：