Create Table #a(LotNum varchar(2),SerialNum int,I int)Insert #a(LotNum,SerialNum,I)
Select 'a',1,2
Union
Select 'a',2,7
Union
Select 'a',3,19
Union
Select 'a',4,48
Union
Select 'a',5,100
Union
Select 'b',1,1
Union
Select 'b',2,10
Union
Select 'b',3,3我想得到摆每个LotNum和SerialNum对小于SerialNum所有SerialNum做累计加总,就是得到以下结果,序号一定是连续的a,1,176
a,2,174
a,3,167
a,4,148
a,5,100
b,1,14
b,2,13
b,3,3我记的用UPDATE有个技巧可以实现,想不起来了,哪位大侠帮忙看下,最好别用CURSOR实际资料很多.谢谢啦
Select 'a',1,2
Union
Select 'a',2,7
Union
Select 'a',3,19
Union
Select 'a',4,48
Union
Select 'a',5,100
Union
Select 'b',1,1
Union
Select 'b',2,10
Union
Select 'b',3,3我想得到摆每个LotNum和SerialNum对小于SerialNum所有SerialNum做累计加总,就是得到以下结果,序号一定是连续的a,1,176
a,2,174
a,3,167
a,4,148
a,5,100
b,1,14
b,2,13
b,3,3我记的用UPDATE有个技巧可以实现,想不起来了,哪位大侠帮忙看下,最好别用CURSOR实际资料很多.谢谢啦
SELECT * FROM #a T1 WHERE NOT EXISTS(SELECT 1 FROM #a T2 WHERE T2.LotNum=T1.LotNum AND T2.SerialNum>T1.SerialNum )
UNION ALL
SELECT T1.LotNum,T1.SerialNum,T1.I+T2.I
FROM #a T1
INNER JOIN MU T2 ON T1.LotNum=T2.LotNum AND T1.SerialNum=T2.SerialNum-1
)
SELECT * FROM MU ORDER BY LotNum,SerialNum
/*
LotNum SerialNum I
------ ----------- -----------
a 1 176
a 2 174
a 3 167
a 4 148
a 5 100
b 1 14
b 2 13
b 3 3
*/
select lotnum,serialnum,(select SUM(i) from #a where lotnum=A.lotnum
and serialnum>=A.serialnum) from #a A group by lotnum,serialnum lotnum serialnum
------ ----------- -----------
a 1 176
a 2 174
a 3 167
a 4 148
a 5 100
b 1 14
b 2 13
b 3 3(8 行受影响)
Create Table #a(LotNum varchar(2),SerialNum int,I int)Insert #a(LotNum,SerialNum,I)
Select 'a',1,2
Union
Select 'a',2,7
Union
Select 'a',3,19
Union
Select 'a',4,48
Union
Select 'a',5,100
Union
Select 'b',1,1
Union
Select 'b',2,10
Union
Select 'b',3,3
--select * from #a
select LotNum,SerialNum,i=(select sum(I) from #a where LotNum=a.LotNum and SerialNum>=a.SerialNum) from #a a
/*
LotNum SerialNum i
------ ----------- -----------
a 1 176
a 2 174
a 3 167
a 4 148
a 5 100
b 1 14
b 2 13
b 3 3(8 行受影响)
*/