DECLARE @sum_num INT, @pid INT
SELECT @pid = 0, @sum_num=0
UPDATE #tableOrder
SET @sum_num=(CASE WHEN productID = @pid THEN @sum_num + productNum ELSE productNum END), sum_num=@sum_num,
@pid= productID
---------#tableOrder表中是按productID排序写入的数据!为什么执行的时候, 有时候会得到sum_num值不是连续的呢! 然后再执行一次就好了!#tableOrder 表中的数据将近100W条, 很多几万件产品
SELECT @pid = 0, @sum_num=0
UPDATE #tableOrder
SET @sum_num=(CASE WHEN productID = @pid THEN @sum_num + productNum ELSE productNum END), sum_num=@sum_num,
@pid= productID
---------#tableOrder表中是按productID排序写入的数据!为什么执行的时候, 有时候会得到sum_num值不是连续的呢! 然后再执行一次就好了!#tableOrder 表中的数据将近100W条, 很多几万件产品
/* DROP TABLE #tableOrder */
CREATE TABLE #tableOrder( ID INT IDENTITY(1,1), productID INT , productNum INT , sum_num INT )TRUNCATE TABLE #tableOrder
INSERT INTO #tableOrder(productID, productNum, sum_num)
SELECT 1,3,0
UNION ALL
SELECT 1,-3,0
UNION ALL
SELECT 1,5,0
UNION ALL
SELECT 1,-2,0
UNION ALL
SELECT 1,1,0
UNION ALL
SELECT 1,-1,0
UNION ALL
SELECT 1,-1,0
UNION ALL
SELECT 1,-1,0
UNION ALL
SELECT 1,4,0
/*UNION ALL
.... 还有其他产品的ID ---------#tableOrder表中是按productID排序写入的数据!*/ DECLARE @sum_num INT, @pid INT
SELECT @pid = 0, @sum_num=0
UPDATE #tableOrder
SET @sum_num=(CASE WHEN productID = @pid THEN @sum_num + productNum ELSE productNum END),
@pid= productID,
sum_num = @sum_num
SELECT * FROM #tableOrder
/* 正常结果是这样的 */
ID productID productNum sum_num
----------- ----------- ----------- -----------
1 1 3 3
2 1 -3 0
3 1 5 5
4 1 -2 3
5 1 1 4
6 1 -1 3
7 1 -1 2
8 1 -1 1
9 1 4 5
但是不知道为什么原因, 有时候会出现某一行的 sum_num 变成productNum的值了, 偶尔出现的情况!
比如: 从ID=5的行开始变, 然后再重新执行就又OK了!!!
ID productID productNum sum_num
----------- ----------- ----------- -----------
1 1 3 3
2 1 -3 0
3 1 5 5
4 1 -2 3
5 1 1 1
6 1 -1 0
7 1 -1 -1
8 1 -1 -2
9 1 4 2
INSERT INTO @tableOrder(productID, productNum, sum_num)
SELECT 1,3,0
UNION ALL
SELECT 1,-3,0
UNION ALL
SELECT 1,5,0
UNION ALL
SELECT 1,-2,0
UNION ALL
SELECT 1,1,0
UNION ALL
SELECT 1,-1,0
UNION ALL
SELECT 1,-1,0
UNION ALL
SELECT 1,-1,0
UNION ALL
SELECT 1,4,0
SELECT * FROM @tableOrder
DECLARE @sum_num INT, @pid INT
SELECT @pid = 0, @sum_num=0
UPDATE @tableOrder
SET @sum_num=(CASE WHEN productID = @pid THEN @sum_num + productNum ELSE productNum END),
@pid= productID,
sum_num = @sum_num
SELECT * FROM @tableOrder
/*
ID productID productNum sum_num
----------- ----------- ----------- -----------
1 1 3 3
2 1 -3 0
3 1 5 5
4 1 -2 3
5 1 1 4
6 1 -1 3
7 1 -1 2
8 1 -1 1
9 1 4 5(9 行受影响)
*/--没有出现类似情况,测试次数为5次
@pid= productID--〉SET @sum_num=(CASE WHEN productID = @pid THEN @sum_num + productNum ELSE productNum END),@pid= productID,
sum_num=@sum_num