统计当月累计

[csproj]

表：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

[csproj]

每次字段标题和数据都对不齐。。
麻烦大家了：）

[--小F--]

貌似没什么优化的了

到是楼主代码有点问题

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

[chuifengde]

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

[--小F--]

鸟的是嵌套查询 你的是连接查询 估计都差不多

[jiangshun]

--> 测试数据：[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','李四',20

select  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

[dawugui]

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

[水族杰纶]

--> 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 個資料列受到影響)
*/

[dawugui]

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 )
go

select t.*,
       (select Sum(Quantity) from tb where Employ = t.Employ and Date <= t.Date) As Total
from tb t

drop 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 行）
*/

[guguda2008]

看不到能优化的地方，条件字段和分组字段都加索引了吗？

[csproj]

明白了 谢谢大家

[happinessdepig]

Mark 学习！