27,580
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
id VARCHAR(10),
[name] NVARCHAR(10),
total INT,
[balance] INT
)
GO
SET NOCOUNT ON
INSERT INTO t VALUES (1001,'土豆',50,50)
INSERT INTO t VALUES (1002,'土豆',60,110)
INSERT INTO t VALUES (1003,'大土豆',70,70)
INSERT INTO t VALUES (1004,'大土豆',50,120)
INSERT INTO t VALUES (1005,'大土豆',30,150)
INSERT INTO t VALUES (1006,'大土豆',50,200)
--sqlserver2012+
SELECT *,SUM(total) OVER (PARTITION BY [name] ORDER BY id) AS [r] FROM t
ORDER BY [name]
--sqlserver2005+
;WITH cte AS (
SELECT *,ROW_NUMBER() OVER (PARTITION BY [name] ORDER BY id) AS rid FROM t
)
SELECT a.id
,a.[name]
,a.total
,a.balance
,a.total+isnull(b.total,0) AS r
FROM cte AS a LEFT JOIN cte AS b ON a.[name]=b.[name] AND a.rid=b.rid+1