现有数据表
代码 日期 金额1 累计金额2
001 200801 10.00 0
001 200802 11.00 0
002 200901 10.00 0
002 200908 20.00 0
现在我想更新累计金额2的数据,根据代码和日期以及金额1来更新。
累计是将相同代码的根据日期来累计
更新后应该是:
代码 日期 金额1 累计金额2
001 200801 10.00 10.00
001 200802 11.00 21.00
002 200901 10.00 10.00
002 200908 20.00 30.00
代码 日期 金额1 累计金额2
001 200801 10.00 0
001 200802 11.00 0
002 200901 10.00 0
002 200908 20.00 0
现在我想更新累计金额2的数据,根据代码和日期以及金额1来更新。
累计是将相同代码的根据日期来累计
更新后应该是:
代码 日期 金额1 累计金额2
001 200801 10.00 10.00
001 200802 11.00 21.00
002 200901 10.00 10.00
002 200908 20.00 30.00
(select sum(金额1) from tb where 代码=t.代码 and 日期<=t.日期)from tb t
-- Author : htl258(Tony)
-- Date : 2010-05-06 16:17:14
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
------------------------------------------------------------------------------------> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([代码] [nvarchar](10),[日期] [int],[金额1] [numeric](4,2),[累计金额2] [int])
INSERT INTO [tb]
SELECT '001','200801','10.00','0' UNION ALL
SELECT '001','200802','11.00','0' UNION ALL
SELECT '002','200901','10.00','0' UNION ALL
SELECT '002','200908','20.00','0'--SELECT * FROM [tb]-->SQL查询如下:
update tb set 累计金额2=(select sum(金额1) from tb t where 代码=tb.代码 and 日期<=tb.日期)select * from tb
/*
代码 日期 金额1 累计金额2
---------- ----------- --------------------------------------- -----------
001 200801 10.00 10
001 200802 11.00 21
002 200901 10.00 10
002 200908 20.00 30(4 行受影响)
*/
(select sum(金额1) from tb where 代码1=t.代码1 and 代码2=t.代码2 and 日期<=t.日期)from tb t楼主参照修改就行,再加一条件就行了而已
-- Author : htl258(Tony)
-- Date : 2010-05-06 16:29:25
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
------------------------------------------------------------------------------------> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([代码1] [nvarchar](10),[代码2] [nvarchar](10),[日期] [int],[金额1] [numeric](4,2),[累计金额2] [int])
INSERT INTO [tb]
SELECT '001','0011','200801','10.00','0' UNION ALL
SELECT '001','0011','200802','11.00','0' UNION ALL
SELECT '001','0012','200801','10.00','0' UNION ALL
SELECT '001','0012','200802','11.00','0' UNION ALL
SELECT '002','0021','200901','10.00','0' UNION ALL
SELECT '002','0021','200908','20.00','0' UNION ALL
SELECT '002','0022','200901','10.00','0' UNION ALL
SELECT '002','0022','200908','20.00','0'-->SQL查询如下:
update tb set 累计金额2=(select sum(金额1) from tb t where 代码2=tb.代码2 and 日期<=tb.日期)SELECT * FROM [tb]
/*
代码1 代码2 日期 金额1 累计金额2
---------- ---------- ----------- --------------------------------------- -----------
001 0011 200801 10.00 10
001 0011 200802 11.00 21
001 0012 200801 10.00 10
001 0012 200802 11.00 21
002 0021 200901 10.00 10
002 0021 200908 20.00 30
002 0022 200901 10.00 10
002 0022 200908 20.00 30(8 行受影响)
*/
update tb set 累计金额2=(select sum(金额1) from tb t where 代码1=tb.代码1 and 代码2=tb.代码2 and 日期<=tb.日期)SELECT * FROM [tb]
/*
代码1 代码2 日期 金额1 累计金额2
---------- ---------- ----------- --------------------------------------- -----------
001 0011 200801 10.00 10
001 0011 200802 11.00 21
001 0012 200801 10.00 10
001 0012 200802 11.00 21
002 0021 200901 10.00 10
002 0021 200908 20.00 30
002 0022 200901 10.00 10
002 0022 200908 20.00 30(8 行受影响)
*/MODIFY