一个表payrecord
[voucher] [nvarchar](7)not null default'',
[billdate] [smalldatetime]not null default getdate(),
[supplier] [nvarchar](5)not null default'',
[fileno] [nvarchar](11)not null default'',
[director] [nvarchar](13)not null default'',
[amount] [decimal](9,2)not null default 0,
[pay] [decimal](9,2)not null default 0,
[termday] [smalldatetime]not null default getdate(),
[exchange] [nvarchar](10)not null default''
0000001 2010-12-09 16:51:00 01030 20100808000 Salin 1880.00 .00 18.00 2010-12-11 09:57:00
0000001 2010-12-09 16:51:00 01030 20100808000 Salin 1880.00 18.00 62.00 2010-12-11 09:59:00
0000001 2010-12-09 16:51:00 01030 20100808000 Salin 1880.00 80.00 800.00 2010-12-11 11:38:00
0000001 2010-12-09 16:51:00 01030 20100808000 Salin 1880.00 880.00 100.00 2010-12-12 11:34:00 现在要实现如下查询
0000001 2010-12-09 16:51:00 01030 20100808000 Salin 1880.00 .00 18.00 2010-12-11 09:57:00
0000001 2010-12-09 16:51:00 01030 20100808000 Salin 1880.00 18.00 62.00 2010-12-11 09:59:00
0000001 2010-12-09 16:51:00 01030 20100808000 Salin 1880.00 80.00 800.00 2010-12-11 11:38:00
0000001 2010-12-09 16:51:00 01030 20100808000 Salin 1880.00 880.00 100.00 2010-12-12 11:34:00
即,增加了一栏以往PAY的汇总(假设是finish),第一笔记录的因为之前没有记录,所以finish为0,第二笔的finish实际是第一笔的pay,而第三笔是第一笔和第二笔pay的总和,第四笔的finish是前三笔pay的总和...以此类推(主键为voucher+termday)
[voucher] [nvarchar](7)not null default'',
[billdate] [smalldatetime]not null default getdate(),
[supplier] [nvarchar](5)not null default'',
[fileno] [nvarchar](11)not null default'',
[director] [nvarchar](13)not null default'',
[amount] [decimal](9,2)not null default 0,
[pay] [decimal](9,2)not null default 0,
[termday] [smalldatetime]not null default getdate(),
[exchange] [nvarchar](10)not null default''
0000001 2010-12-09 16:51:00 01030 20100808000 Salin 1880.00 .00 18.00 2010-12-11 09:57:00
0000001 2010-12-09 16:51:00 01030 20100808000 Salin 1880.00 18.00 62.00 2010-12-11 09:59:00
0000001 2010-12-09 16:51:00 01030 20100808000 Salin 1880.00 80.00 800.00 2010-12-11 11:38:00
0000001 2010-12-09 16:51:00 01030 20100808000 Salin 1880.00 880.00 100.00 2010-12-12 11:34:00 现在要实现如下查询
0000001 2010-12-09 16:51:00 01030 20100808000 Salin 1880.00 .00 18.00 2010-12-11 09:57:00
0000001 2010-12-09 16:51:00 01030 20100808000 Salin 1880.00 18.00 62.00 2010-12-11 09:59:00
0000001 2010-12-09 16:51:00 01030 20100808000 Salin 1880.00 80.00 800.00 2010-12-11 11:38:00
0000001 2010-12-09 16:51:00 01030 20100808000 Salin 1880.00 880.00 100.00 2010-12-12 11:34:00
即,增加了一栏以往PAY的汇总(假设是finish),第一笔记录的因为之前没有记录,所以finish为0,第二笔的finish实际是第一笔的pay,而第三笔是第一笔和第二笔pay的总和,第四笔的finish是前三笔pay的总和...以此类推(主键为voucher+termday)
[voucher] [nvarchar](7)not null default'',
[billdate] [smalldatetime]not null default getdate(),
[supplier] [nvarchar](5)not null default'',
[fileno] [nvarchar](11)not null default'',
[director] [nvarchar](13)not null default'',
[amount] [decimal](9,2)not null default 0,
[pay] [decimal](9,2)not null default 0,
[termday] [smalldatetime]not null default getdate(),
[exchange] [nvarchar](10)not null default''
)
insert into payrecord select '0000001','2010-12-09 16:51:00','01030','20100808000','Salin',1880.00,18.00,'2010-12-11 09:57:00',''
insert into payrecord select '0000001','2010-12-09 16:51:00','01030','20100808000','Salin',1880.00,62.00,'2010-12-11 09:59:00',''
insert into payrecord select '0000001','2010-12-09 16:51:00','01030','20100808000','Salin',1880.00,800.00,'2010-12-11 11:38:00',''
insert into payrecord select '0000001','2010-12-09 16:51:00','01030','20100808000','Salin',1880.00,100.00,'2010-12-12 11:34:00',''
go
select *,(select sum(pay) from payrecord where termday<a.termday)finish from payrecord a
go
drop table payrecord
/*
voucher billdate supplier fileno director amount pay termday exchange finish
------- ----------------------- -------- ----------- ------------- --------------------------------------- --------------------------------------- ----------------------- ---------- ---------------------------------------
0000001 2010-12-09 16:51:00 01030 20100808000 Salin 1880.00 18.00 2010-12-11 09:57:00 NULL
0000001 2010-12-09 16:51:00 01030 20100808000 Salin 1880.00 62.00 2010-12-11 09:59:00 18.00
0000001 2010-12-09 16:51:00 01030 20100808000 Salin 1880.00 800.00 2010-12-11 11:38:00 80.00
0000001 2010-12-09 16:51:00 01030 20100808000 Salin 1880.00 100.00 2010-12-12 11:34:00 880.00(4 行受影响)*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([pay] int,[date] int)
insert [tb]
select 100,1 union all
select 200,2 union all
select 300,3 union all
select 400,4 union all
select 100,5--类似这样的吗?
select [pay],[date],finish=isnull((select SUM([pay]) from tb where date<a.[date]),0)
from [tb] a
/*
pay date finish
----------- ----------- -----------
100 1 0
200 2 100
300 3 300
400 4 600
100 5 1000(5 行受影响)*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([pay] int,[date] int)
insert [tb]
select 100,1 union all
select 200,2 union all
select 300,3 union all
select 400,4 union all
select 100,5--类似这样的吗?
select [pay],[date],finish=isnull((select SUM([pay]) from tb where date<a.[date]),0)
from [tb] a
/*
pay date finish
----------- ----------- -----------
100 1 0
200 2 100
300 3 300
400 4 600
100 5 1000(5 行受影响)*/
--> 数据库版本:
--> 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')) --U 代表你查询的是表
DROP TABLE [TB]
GO---->建表
create table [TB]([voucher] varchar(7),[billdate] datetime,[supplier] varchar(5),[fileno] bigint,[director] varchar(5),[amount] numeric(6,2),[pay] numeric(5,2),[termday] numeric(5,2),[exchange] datetime)
insert [TB]
select '0000001','2010-12-09 16:51:00','01030',20100808000,'Salin',1880.00,null ,18.00,'2010-12-11 09:57:00' union all
select '0000001','2010-12-09 16:51:00','01030',20100808000,'Salin',1880.00,null ,62.00,'2010-12-11 09:59:00' union all
select '0000001','2010-12-09 16:51:00','01030',20100808000,'Salin',1880.00,null ,800.00,'2010-12-11 11:38:00' union all
select '0000001','2010-12-09 16:51:00','01030',20100808000,'Salin',1880.00,null,100.00,'2010-12-12 11:34:00'
GO--> 查询结果
SELECT * FROM [TB]declare @i int
set @i= 0
declare @p numeric(6,2)
set @p= 0
update [TB]
set pay = @p,@p=termday + case when @i = 0 then 0 else @p end ,@i=@i+1SELECT * FROM [TB]
--> 删除表格
--DROP TABLE [TB]
当pay有所更新时,各条记录的finish也自动更新
0000001 2010-12-09 16:51:00 01030 20100808000 Salin 1880.00 18.00 2010-12-11 09:57:00
0000001 2010-12-09 16:51:00 01030 20100808000 Salin 1880.00 62.00 2010-12-11 09:59:00
0000001 2010-12-09 16:51:00 01030 20100808000 Salin 1880.00 800.00 2010-12-11 11:38:00
0000001 2010-12-09 16:51:00 01030 20100808000 Salin 1880.00 100.00 2010-12-12 11:34:00
0000002 2010-12-09 16:51:00 01030 20100808001 Sunny 915.00 5.00 2010-12-13 11:30:00
0000002 2010-12-09 16:51:00 01030 20100808001 Sunny 915.00 10.00 2010-12-14 11:35:00
0000002 2010-12-09 16:51:00 01030 20100808001 Sunny 915.00 500.00 2010-12-14 11:39:00
要出这样的结果
0000001 2010-12-09 16:51:00 01030 20100808000 Salin 1880.00 18.00 2010-12-11 09:57:00 0
0000001 2010-12-09 16:51:00 01030 20100808000 Salin 1880.00 62.00 2010-12-11 09:59:00 18
0000001 2010-12-09 16:51:00 01030 20100808000 Salin 1880.00 800.00 2010-12-11 11:38:00 80
0000001 2010-12-09 16:51:00 01030 20100808000 Salin 1880.00 100.00 2010-12-12 11:34:00 880
0000002 2010-12-09 16:51:00 01030 20100808001 Sunny 915.00 5.00 2010-12-13 11:30:00 0
0000002 2010-12-09 16:51:00 01030 20100808001 Sunny 915.00 10.00 2010-12-14 11:35:00 5
0000002 2010-12-09 16:51:00 01030 20100808001 Sunny 915.00 500.00 2010-12-14 11:39:00 15
[voucher] [nvarchar](7)not null default'',
[billdate] [smalldatetime]not null default getdate(),
[supplier] [nvarchar](5)not null default'',
[fileno] [nvarchar](11)not null default'',
[director] [nvarchar](13)not null default'',
[amount] [decimal](9,2)not null default 0,
[pay] [decimal](9,2)not null default 0,
[termday] [smalldatetime]not null default getdate(),
[exchange] [nvarchar](10)not null default''
)
insert into payrecord select '0000001','2010-12-09 16:51:00','01030','20100808000','Salin',1880.00,18.00,'2010-12-11 09:57:00',''
insert into payrecord select '0000001','2010-12-09 16:51:00','01030','20100808000','Salin',1880.00,62.00,'2010-12-11 09:59:00',''
insert into payrecord select '0000001','2010-12-09 16:51:00','01030','20100808000','Salin',1880.00,800.00,'2010-12-11 11:38:00',''
insert into payrecord select '0000001','2010-12-09 16:51:00','01030','20100808000','Salin',1880.00,100.00,'2010-12-12 11:34:00',''
insert into payrecord select '0000002','2010-12-09 16:51:00','01030','20100808001','Sunny',915.00,5.00,'2010-12-13 11:30:00',''
insert into payrecord select '0000002','2010-12-09 16:51:00','01030','20100808001','Sunny',915.00,10.00,'2010-12-14 11:35:00',''
insert into payrecord select '0000002','2010-12-09 16:51:00','01030','20100808001','Sunny',915.00,500.00,'2010-12-14 11:39:00','' go
select *,(select sum(pay) from payrecord where voucher=a.voucher and termday<a.termday)finish from payrecord a
go
drop table payrecord
/*
voucher billdate supplier fileno director amount pay termday exchange finish
------- ----------------------- -------- ----------- ------------- --------------------------------------- --------------------------------------- ----------------------- ---------- ---------------------------------------
0000001 2010-12-09 16:51:00 01030 20100808000 Salin 1880.00 18.00 2010-12-11 09:57:00 NULL
0000001 2010-12-09 16:51:00 01030 20100808000 Salin 1880.00 62.00 2010-12-11 09:59:00 18.00
0000001 2010-12-09 16:51:00 01030 20100808000 Salin 1880.00 800.00 2010-12-11 11:38:00 80.00
0000001 2010-12-09 16:51:00 01030 20100808000 Salin 1880.00 100.00 2010-12-12 11:34:00 880.00
0000002 2010-12-09 16:51:00 01030 20100808001 Sunny 915.00 5.00 2010-12-13 11:30:00 NULL
0000002 2010-12-09 16:51:00 01030 20100808001 Sunny 915.00 10.00 2010-12-14 11:35:00 5.00
0000002 2010-12-09 16:51:00 01030 20100808001 Sunny 915.00 500.00 2010-12-14 11:39:00 15.00*/