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