日期:2014-05-17 浏览次数:20606 次
CREATE TABLE tb(dt DATETIME,num int)
INSERT INTO [dbo].[tb] ([dt],[num]) VALUES ('2013-10-1',1)
INSERT INTO [dbo].[tb] ([dt],[num]) VALUES ('2013-10-2',2)
INSERT INTO [dbo].[tb] ([dt],[num]) VALUES ('2013-10-3',3)
INSERT INTO [dbo].[tb] ([dt],[num]) VALUES ('2013-10-4',2)
GO
SELECT * FROM [tb] t
CROSS APPLY (
SELECT SUM(t2.num) ct FROM tb t2 WHERE t2.dt<t.dt
) r
GO
--result
dt num ct
----------------------- ----------- -----------
2013-10-01 00:00:00.000 1 NULL
2013-10-02 00:00:00.000 2 1
2013-10-03 00:00:00.000 3 3
2013-10-04 00:00:00.000 2 6
(4 行受影响)
WITH a1 ([时间],[数量]) AS
(
SELECT '10/1', 1 UNION ALL
SELECT '10/2', 2 UNION ALL
SELECT '10/3', 3 UNION ALL
SELECT '10/4', 2
)
SELECT [时间],[数量],ISNULL((SELECT SUM([数量]) FROM a1 WHERE [时间]<a.[时间]),0) [加总]
FROM a1 a
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-12-10 17:31:28
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec&nb