现有如下表结构和数据
years months sales
2010 1 2000
2010 2 2000
2010 3 2000
2010 4 2000
如何实现如下查询结果:
years months sum(sales)
2010 1 2000
2010 2 4000
2010 3 6000
2010 4 8000就是本期值等于本期实际加上历史
years months sales
2010 1 2000
2010 2 2000
2010 3 2000
2010 4 2000
如何实现如下查询结果:
years months sum(sales)
2010 1 2000
2010 2 4000
2010 3 6000
2010 4 8000就是本期值等于本期实际加上历史
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO---->建表
create table [TB]([years] int,[months] int,[sales] int)
insert [TB]
select 2010,1,2000 union all
select 2010,2,2000 union all
select 2010,3,2000 union all
select 2010,4,2000
GO--> 查询结果
SELECT *, sumsales=(select SUM(sales) from [TB] where years = a.years and months<=a.months )
FROM [TB] a
--> 删除表格
--DROP TABLE [TB]
years months sales sumsales
2010 1 2000 2000
2010 2 2000 4000
2010 3 2000 6000
2010 4 2000 8000
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO---->建表
create table [TB]([years] int,[months] int,[sales] int,sumsales int )
insert [TB]
select 2010,1,2000,null union all
select 2010,2,2000,null union all
select 2010,3,2000,null union all
select 2010,4,2000,null
GO--> 查询语句结果
SELECT *, S=(select SUM(sales) from [TB] where years = a.years and months<=a.months )
FROM [TB] a
--> 删除表格
--DROP TABLE [TB]
--更新用下面语句
update [TB]
SET sumsales=(select SUM(sales) from [TB] where years = a.years and months<=a.months )
FROM [TB] a
SELECT * FROM [TB] a
--------------
years months sales sumsales
2010 1 2000 2000
2010 2 2000 4000
2010 3 2000 6000
2010 4 2000 8000
begin
drop table [TB]
endcreate table [TB]([years] int,[months] int,[sales] int,[sum(sales)] int )
insert [TB]
select 2010,1,2000,null union all
select 2010,2,2000,null union all
select 2010,3,2000,null union all
select 2010,4,2000,null
GOupdate [TB]
SET [sum(sales)]=(select SUM(sales) from [TB] where years = a.years and months<=a.months )
FROM [TB] a
SELECT years, months, [sum(sales)] FROM [TB] a