(急)SQL累加计算问题

Question

select aaa.日期,aaa.部门,aaa.姓名,bbb.累计业绩,aaa.当天业绩
from table aaa inner join 
(select 姓名,sum(当天业绩) as 累计业绩 from table group by 姓名) bbb on a.姓名=b.姓名

Answer 1

修改一下，更直观一些
select aaa.日期,aaa.部门,aaa.姓名,bbb.累计业绩,aaa.当天业绩
from yourtable as aaa inner join 
(select 姓名,sum(当天业绩) as 累计业绩 from yourtable group by 姓名) as bbb on a.姓名=b.姓名

Answer 2

先贴第一种情况
-- 生成测试数据
declare @table table(日期 datetime,部门 nvarchar(50),姓名 nvarchar(50),当天业绩 int)
insert into @table
select '2008-3-1',N'组1',N'张三' ,'5000'
union
select '2008-3-1'  ,   N'组1' ,    N'张三' ,  ' 5000 '
union
select '2008-3-3'   , N'组1'  ,  N'张三'  , ' 3000 '
union
select '2008-3-5'  ,  N'组1'   , N'张三'  ,  '1000' 
union
select '2008-4-1'   ,  N'组2'  ,   N'李四'  ,  '2500' 
union 
select '2008-4-5'   ,  N'组2'   ,  N'李四'  ,  '1800' 
union
 select '2008-4-22' ,  N'组2'   ,  N'李四' ,  ' 2200' --得出结果
select   tb1.日期
,tb1.部门
,tb1.姓名
,tb1.当天业绩
,(select 
sum(tb2.当天业绩) 
  from 
@table tb2 
  where
tb2.日期<=tb1.日期

  ) as 累计业绩
from 
@table tb1
--结果
-- 2008-03-01 00:00:00.000 组1 张三 5000 5000
-- 2008-03-03 00:00:00.000 组1 张三 3000 8000
-- 2008-03-05 00:00:00.000 组1 张三 1000 9000
-- 2008-04-01 00:00:00.000 组2 李四 2500 11500
-- 2008-04-05 00:00:00.000 组2 李四 1800 13300
-- 2008-04-22 00:00:00.000 组2 李四 2200 15500

Answer 3

--借数据declare @table table(日期 datetime,部门 nvarchar(50),姓名 nvarchar(50),当天业绩 int)
insert into @table
select '2008-3-1',N'组1',N'张三' ,'5000'
union
select '2008-3-2'  ,   N'组1' ,    N'张三' ,  ' 5000 '
union
select '2008-3-3'   , N'组1'  ,  N'张三'  , ' 3000 '
union
select '2008-3-5'  ,  N'组1'   , N'张三'  ,  '1000' 
union
select '2008-4-1'   ,  N'组2'  ,   N'李四'  ,  '2500' 
union 
select '2008-4-5'   ,  N'组2'   ,  N'李四'  ,  '1800' 
union
 select '2008-4-22' ,  N'组2'   ,  N'李四' ,  ' 2200' 
select *,累计业绩=(select sum(当天业绩) from @table b where a.部门=b.部门 and a.姓名=b.姓名 and b.日期<=a.日期) from @table a

Answer 4

declare @table table(日期 datetime,部门 nvarchar(50),姓名 nvarchar(50),当天业绩 int)
insert into @table
select '2008-3-1',N'组1',N'张三' ,'5000'
union
select '2008-3-2'  ,   N'组1' ,    N'张三' ,  ' 5000 '
union
select '2008-3-3'   , N'组1'  ,  N'张三'  , ' 3000 '
union
select '2008-3-5'  ,  N'组1'   , N'张三'  ,  '1000' 
union
select '2008-4-1'   ,  N'组2'  ,   N'李四'  ,  '2500' 
union 
select '2008-4-5'   ,  N'组2'   ,  N'李四'  ,  '1800' 
union
 select '2008-4-22' ,  N'组2'   ,  N'李四' ,  ' 2200' 
select a.日期,a.部门,a.姓名,累计业绩=(select sum(当天业绩) from @table b where a.部门=b.部门 and a.姓名=b.姓名 and b.日期<=a.日期)  from @table a--第二种情况，生成一个临时表#
select a.日期,a.部门,a.姓名,累计业绩=(select sum(当天业绩) from @table b where a.部门=b.部门 and a.姓名=b.姓名 and b.日期<=a.日期) into # from @table a
select *,当天业绩=isnull(累计业绩-(select top 1 累计业绩 from # b where a.部门=b.部门 and a.姓名=b.姓名 and b.日期<a.日期 ),累计业绩) from # a

Answer 5

第二种情况
declare @table table(日期 datetime,部门 nvarchar(50),姓名 nvarchar(50),累计业绩 int)
insert into @table 
select '2008-02-20' ,N'组1' ,N'张三' ,'5000'
union
select 
'2008-03-01'  ,  N'组1' ,N'张三' ,'5000'
union
select'2008-03-03'  ,N'组1' ,N'张三' ,'8000'
union
select
'2008-03-05'  ,  N'组1' ,N'张三' ,'9000'
union
select
'2008-04-01' ,N'组2' ,N'李四' ,'11500'
union
select
'2008-04-05'  ,N'组2' ,N'李四' ,'13300'
union
select
'2008-04-08'  ,N'组2' ,N'李四' ,'13300'
union
select
'2008-04-22'  ,N'组2' ,N'李四' ,'15500'select  
 tb1.日期
,tb1.部门
,tb1.姓名
,(case tb1.日期 
when 
(select 
min(tb3.日期) 
from 
@table tb3 
where 
tb3. 姓名=tb1.姓名 
) 
then 
(
select 
min(累计业绩) 
from
 @table tb4
 where 
tb4. 姓名=tb1.姓名
)

else (select 
tb1.累计业绩-max(tb2.累计业绩) 
from
 @table tb2 
where 
tb2.日期<tb1.日期
 and 
tb2.姓名=tb1.姓名
)
end )
,tb1.累计业绩
from
@table  tb1

Answer 6

1.
 
/******************************************************************************/
/*回复：20080519008总：00028                                                   */
/*主题：求累计                                                                           */
/*作者：二等草                                                                           */
/******************************************************************************/set nocount on--数据--------------------------------------------------------------------------
declare @tb table([日期] datetime,[部门] varchar(3),[姓名] varchar(4),[当日业绩] int)
 insert into @tb select '2008-3-1','组1','张三',5000
 insert into @tb select '2008-3-3','组1','张三',3000
 insert into @tb select '2008-3-5','组1','张三',1000
 insert into @tb select '2008-3-1','组2','李四',2000
 insert into @tb select '2008-3-4','组2','李四',1500
 insert into @tb select '2008-3-5','组2','李四',1200
 insert into @tb select '2008-3-2','组1','王五',1000
 insert into @tb select '2008-3-3','组1','王五',1300
 insert into @tb select '2008-3-15','组1','王五',2800
 insert into @tb select '2008-4-2','组1','张三',3000
 insert into @tb select '2008-4-10','组1','张三',8000
 insert into @tb select '2008-4-20','组1','张三',2000
 insert into @tb select '2008-4-1','组2','李四',2500
 insert into @tb select '2008-4-5','组2','李四',1800
 insert into @tb select '2008-4-22','组2','李四',2200
 insert into @tb select '2008-4-4','组1','王五',2400
 insert into @tb select '2008-4-7','组1','王五',2600
 insert into @tb select '2008-4-18','组1','王五',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/*结果--------------------------------------------------------------------------
日期                                                     部门   姓名   当日业绩        累计          
------------------------------------------------------ ---- ---- ----------- ----------- 
2008-03-01 00:00:00.000                                组1   张三   5000        5000
2008-03-03 00:00:00.000                                组1   张三   3000        8000
2008-03-05 00:00:00.000                                组1   张三   1000        9000
2008-03-02 00:00:00.000                                组1   王五   1000        1000
2008-03-03 00:00:00.000                                组1   王五   1300        2300
2008-03-15 00:00:00.000                                组1   王五   2800        5100
2008-03-04 00:00:00.000                                组2   李四   1500        1500
2008-03-05 00:00:00.000                                组2   李四   1200        2700
2008-03-01 00:00:00.000                                组2   李四   2000        4700
2008-04-02 00:00:00.000                                组1   张三   3000        3000
2008-04-10 00:00:00.000                                组1   张三   8000        11000
2008-04-20 00:00:00.000                                组1   张三   2000        13000
2008-04-04 00:00:00.000                                组1   王五   2400        2400
2008-04-07 00:00:00.000                                组1   王五   2600        5000
2008-04-18 00:00:00.000                                组1   王五   3300        8300
2008-04-22 00:00:00.000                                组2   李四   2200        2200
2008-04-01 00:00:00.000                                组2   李四   2500        4700
2008-04-05 00:00:00.000                                组2   李四   1800        6500--清除------------------------------------------------------------------------*/

Answer 7

2.
 
/******************************************************************************/
/*回复：20080519009总：00029                                                   */
/*主题：求累计                                                                           */
/*作者：二等草                                                                           */
/******************************************************************************/set nocount on--数据--------------------------------------------------------------------------
 
declare  @tb table ([日期] datetime,[部门] varchar(3),[姓名] varchar(4),[累计业绩] int)
 insert into @tb select '2008-3-1','组1','张三',5000
 insert into @tb select '2008-3-3','组1','张三',5000
 insert into @tb select '2008-3-5','组1','张三',5000
 insert into @tb select '2008-3-1','组2','李四',2000
 insert into @tb select '2008-3-4','组2','李四',3500
 insert into @tb select '2008-3-5','组2','李四',4700
 insert into @tb select '2008-3-2','组1','王五',1000
 insert into @tb select '2008-3-3','组1','王五',2300
 insert into @tb select '2008-3-15','组1','王五',2300
 insert into @tb select '2008-4-2','组1','张三',3000
 insert into @tb select '2008-4-10','组1','张三',3000
 insert into @tb select '2008-4-20','组1','张三',5000
 insert into @tb select '2008-4-1','组2','李四',2500
 insert into @tb select '2008-4-5','组2','李四',4300
 insert into @tb select '2008-4-22','组2','李四',6500
 insert into @tb select '2008-4-4','组1','王五',2400
 insert into @tb select '2008-4-7','组1','王五',5000
 insert into @tb select '2008-4-18','组1','王五',5000--代码--------------------------------------------------------------------------
select *,当日业绩=0 into # from @tb order by convert(char(7),日期,120),姓名 desc,日期
declare @i int,@xm1 varchar(10),@xm2 varchar(10),@j int
select @xm1 = '',@xm2='',@j = 0,@i=0
update # set 当日业绩=累计业绩 - @j,@xm2 = @xm1,@j = case when @xm2 = 姓名 then @i else 0 end,@i = 累计业绩,@xm1 = 姓名
select * from #
drop table #
go/*结果--------------------------------------------------------------------------
日期                                                     部门   姓名   累计业绩        当日业绩        
------------------------------------------------------ ---- ---- ----------- ----------- 
2008-03-01 00:00:00.000                                组1   张三   5000        5000
2008-03-03 00:00:00.000                                组1   张三   5000        0
2008-03-05 00:00:00.000                                组1   张三   5000        0
2008-03-02 00:00:00.000                                组1   王五   1000        1000
2008-03-03 00:00:00.000                                组1   王五   2300        1300
2008-03-15 00:00:00.000                                组1   王五   2300        0
2008-03-01 00:00:00.000                                组2   李四   2000        2000
2008-03-04 00:00:00.000                                组2   李四   3500        1500
2008-03-05 00:00:00.000                                组2   李四   4700        1200
2008-04-02 00:00:00.000                                组1   张三   3000        3000
2008-04-10 00:00:00.000                                组1   张三   3000        0
2008-04-20 00:00:00.000                                组1   张三   5000        2000
2008-04-04 00:00:00.000                                组1   王五   2400        2400
2008-04-07 00:00:00.000                                组1   王五   5000        2600
2008-04-18 00:00:00.000                                组1   王五   5000        0
2008-04-01 00:00:00.000                                组2   李四   2500        2500
2008-04-05 00:00:00.000                                组2   李四   4300        1800
2008-04-22 00:00:00.000                                组2   李四   6500        2200
--清除------------------------------------------------------------------------*/