表tb(Id varchar(20),Amt money)数据
Id Amt
0001 22
0001 33
0001 12.2
0002 55
0002 66
0002 10
0003 3
0003 80需要结果
Id Amt SumAmt
0001 22 22
0001 33 55
0001 12.2 67.2
0002 55 55
0002 66 121
0002 10 131
0003 3 3
0003 80 83
Id Amt
0001 22
0001 33
0001 12.2
0002 55
0002 66
0002 10
0003 3
0003 80需要结果
Id Amt SumAmt
0001 22 22
0001 33 55
0001 12.2 67.2
0002 55 55
0002 66 121
0002 10 131
0003 3 3
0003 80 83
select id,sum(amt) as amt from tb group by id
)b on a.id=b.id
sumAmt=(SELECT SUM(Amt)
FROM #
WHERE Id=t.Id
AND ROW_ID<=t.ROW_ID)
FROM # AS t;
from tb t
if not object_id('Tempdb..#tb') is null
drop table #tb
Go
Create table #tb([Id] nvarchar(20),[Amt] money)
Insert #tb
select N'0001',22 union all
select N'0001',33 union all
select N'0001',12.2 union all
select N'0002',55 union all
select N'0002',66 union all
select N'0002',10 union all
select N'0003',3 union all
select N'0003',80
Go
;with C as
(Select *,row=row_number()over(order by ID) from #tb)
select [Id],[Amt],(select sum([Amt]) from C where ID=a.ID and row<=a.row)SumAmt
from C a(8 個資料列受到影響)
Id Amt SumAmt
-------------------- --------------------- ---------------------
0001 22.00 22.00
0001 33.00 55.00
0001 12.20 67.20
0002 55.00 55.00
0002 66.00 121.00
0002 10.00 131.00
0003 3.00 3.00
0003 80.00 83.00(8 個資料列受到影響)
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (Id VARCHAR(4),Amt NUMERIC(3,1))
INSERT INTO #T
SELECT '0001',22 UNION ALL
SELECT '0001',33 UNION ALL
SELECT '0001',12.2 UNION ALL
SELECT '0002',55 UNION ALL
SELECT '0002',66 UNION ALL
SELECT '0002',10 UNION ALL
SELECT '0003',3 UNION ALL
SELECT '0003',80--SQL查询如下:SELECT ROW_ID=IDENTITY(int,1,1),* INTO # FROM #T;SELECT Id,Amt,
sumAmt=(SELECT SUM(Amt)
FROM #
WHERE Id=t.Id
AND ROW_ID<=t.ROW_ID)
FROM # AS t;/*
Id Amt sumAmt
---- --------------------------------------- ---------------------------------------
0001 22.0 22.0
0001 33.0 55.0
0001 12.2 67.2
0002 55.0 55.0
0002 66.0 121.0
0002 10.0 131.0
0003 3.0 3.0
0003 80.0 83.0(8 行受影响)*/
px Id Amt
1 0001 22
2 0001 33
3 0001 12.2
1 0002 55
2 0002 66
3 0002 10
1 0003 3
2 0003 80