sql 显示当日业绩和累计业绩

第一种方法：

declare @tb1 table (日期datetime,部门varchar(50),姓名varchar(50),当天业绩int)

insert into @tb1 values

('2008/03/01','Group1','ZhangSan' ,5000)

insert into @tb1 values

('2008/03/03','Group1','ZhangSan',3000)

insert into @tb1 values

('2008/03/05','Group1','ZhangSan',1000)

insert into @tb1 values

('2008/03/01' ,'Group2' ,'LiSi',2000 )

insert into @tb1 values

('2008/03/04' ,'Group2','LiSi',1500 )

insert into @tb1 values

('2008/03/05' , 'Group2' , 'LiSi' , 1200 )

insert into @tb1 values

('2008/03/02' , 'Group1' , 'WangWu' , 1000)

insert into @tb1 values

('2008/03/03' ,'Group1' , 'WangWu' , 1300 )

insert into @tb1 values

('2008/03/15', 'Group1' , 'WangWu' , 2800 )

insert into @tb1 values

('2008/04/02' , 'Group1' , 'ZhangSan' , 3000 )

insert into @tb1 values

('2008/04/10' , 'Group1' , 'ZhangSan' , 8000 )

insert into @tb1 values

('2008/04/20' , 'Group1' , 'ZhangSan' , 2000 )

insert into @tb1 values

('2008/04/01' , 'Group2' , 'LiSi' , 2500 )

insert into @tb1 values

('2008/04/05' , 'Group2' , 'LiSi' , 1800 )

insert into @tb1 values

('2008/04/22' , 'Group2' , 'LiSi' , 2200 )

insert into @tb1 values

('2008/04/04' , 'Group1' , 'WangWu' , 2400 )

insert into @tb1 values

('2008/04/07' , 'Group1' , 'WangWu' , 2600 )

insert into @tb1 values

('2008/04/18' , 'Group1' , 'WangWu' , 3300 )

declare @desTabel table(日期datetime,部门varchar(50),姓名varchar(50),当天业绩int,累计业绩int)

Declare @Sum int

declare @Date datetime

declare @Name varchar(50)

Declare @dep varchar(50)

declare @Dayperfor int

declare @tempName varchar(50)

set @Sum = 0

declare C cursor for

select *from @tb1 order by 姓名,日期asc

open C

fetch next from C into @Date,@dep,@Name,@Dayperfor

set @tempName = @Name -- 存储name

while(@@FETCH_STATUS = 0)

begin

if(@tempName = @Name)

begin

set @Sum += @Dayperfor; --加入该name相等

insert @desTabel values(@Date,@dep,@Name,@Dayperfor,@Sum)

end

else

begin

set @Sum = 0;

set @Sum += @Dayperfor;

insert @desTabel values(@Date,@dep,@Name,@Dayperfor,@Sum)

set @tempName = @Name

end

fetch next from C into @Date,@dep,@Name,@Dayperfor

end

select * from @desTabel order by 姓名,日期asc

Close C

Deallocate C

第二种方法

declare @tb table([日期] datetime,[部门] varchar(3),[姓名] varchar(4),[当日业绩] int)

insert into @tb select '2008-3-1','1','3',5000

insert into @tb select '2008-3-3','1','3',3000

insert into @tb select '2008-3-5','1','3',1000

insert into @tb select '2008-3-1','2','4',2000

insert into @tb select '2008-3-4','2','4',1500

insert into @tb select '2008-3-5','2','4',1200

insert into @tb select '2008-3-2','1','5',1000

insert into @tb select '2008-3-3','1','5',1300

insert into @tb select '2008-3-15','1','5',2800

insert into @tb select '2008-4-2','1','3',3000

insert into @tb select '2008-4-10','1','3',8000

insert into @tb select '2008-4-20','1','3',2000

insert into @tb select '2008-4-1','2','4',2500

insert into @tb select '2008-4-5','2','4',1800

insert into @tb select '2008-4-22','2','4',2200

insert into @tb select '2008-4-4','1','5',2400

insert into @tb select '2008-4-7','1','5',2600

insert into @tb select '2008-4-18','1','5',3300

select *,累计=0 into # from @tb order by convert(char(7),日期,120),姓名desc

declare @i int,@xm1 varchar(10),@xm2 varchar(10)

select @xm1 = '',@xm2=''

update # set 累计=@i,@xm2 = @xm1,@i=CASE when @xm2 = 姓名then @i+当日业绩 else 当日业绩end,@xm1=姓名

select * from #

drop table #

go