统计当月累计

Question

表：a
Date              Employ           quantity
2009/11/01        张三                           20
2009/11/01        李四                           5
2009/11/02        张三                           15
2009/11/02        李四                           10
2009/11/03        李四                           20想得到下面的结果
Date              Employ           quantity       Total
2009/11/01        张三                           20            20
2009/11/01        李四                           5             5
2009/11/02        张三                           15            35
2009/11/02        李四                           10            15
2009/11/03        李四                           20            35
Total字段是从月初到当天的累计数量
下面的语句能优化吗：
select a.BillDate,a.Employ,a.Quantity,Sum(b.Quantity) As Total
from a join a b
on a.BillDate >= b.BillDate And a.Employ = b.Employ
group by a.BillDate,a.Employ

Answer 1

貌似没什么优化的了到是楼主代码有点问题select
 a.BillDate,a.Employ,a.Quantity,Sum(b.Quantity) As Total
from
 a join a b
on
 a.BillDate >= b.BillDate And a.Employ = b.Employ
group by 
 a.BillDate,a.Employ,a.Quantity

Answer 2

SELECT *,Total=(SELECT isnull(SUM(quantity),0) FROM a WHERE Employ=b.Employ AND [Date]<=b.[Date])
FROM a b

Answer 3

--> 测试数据：[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([Date] datetime,[Employ] varchar(4),[quantity] int)
insert [TB]
select '2009/11/01','张三',20 union all
select '2009/11/01','李四',5 union all
select '2009/11/02','张三',15 union all
select '2009/11/02','李四',10 union all
select '2009/11/03','李四',20select  Date=convert(varchar(10),Date,120),
Employ,
quantity,
Total=(select sum(quantity) from TB where t.Employ=Employ and Date<=t.Date) 
from [TB] t/*
Date       Employ quantity    Total       
---------- ------ ----------- ----------- 
2009-11-01 张三     20          20
2009-11-01 李四     5           5
2009-11-02 张三     15          35
2009-11-02 李四     10          15
2009-11-03 李四     20          35（所影响的行数为 5 行）
*/
drop table TB

Answer 4

select BillDate,Employ,Quantity,
       (select Sum(b.Quantity) from a where Employ = t.Employ and Date <= t.Date) As Total
from a t

Answer 5

--> Title  : Generating test data [tb]
--> Author : 
--> Date   : 2009-12-03 11:19:05
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (Date datetime,Employ nvarchar(4),quantity int)
insert into [tb]
select '2009/11/01',N'张三',20 union all
select '2009/11/01','李四',5 union all
select '2009/11/02',N'张三',15 union all
select '2009/11/02','李四',10 union all
select '2009/11/03','李四',20
select *,
       (select sum(quantity) from tb where Employ=t.Employ and date<=t.date)
from tb t
/*
Date                    Employ quantity    
----------------------- ------ ----------- -----------
2009-11-01 00:00:00.000 张三     20          20
2009-11-01 00:00:00.000 李四     5           5
2009-11-02 00:00:00.000 张三     15          35
2009-11-02 00:00:00.000 李四     10          15
2009-11-03 00:00:00.000 李四     20          35(5 個資料列受到影響)
*/

Answer 6

create table tb(Date  datetime,            Employ  varchar(10),        quantity int)
insert into tb values('2009/11/01' ,       '张三' ,                         20 )
insert into tb values('2009/11/01' ,       '李四' ,                         5 )
insert into tb values('2009/11/02' ,       '张三' ,                         15 )
insert into tb values('2009/11/02' ,       '李四' ,                         10 )
insert into tb values('2009/11/03' ,       '李四' ,                         20 )
goselect t.*,
       (select Sum(Quantity) from tb where Employ = t.Employ and Date <= t.Date) As Total
from tb tdrop table tb/*
Date                                                   Employ     quantity    Total       
------------------------------------------------------ ---------- ----------- ----------- 
2009-11-01 00:00:00.000                                张三         20          20
2009-11-01 00:00:00.000                                李四         5           5
2009-11-02 00:00:00.000                                张三         15          35
2009-11-02 00:00:00.000                                李四         10          15
2009-11-03 00:00:00.000                                李四         20          35（所影响的行数为 5 行）
*/