求一个累加的sql语句

[pengpeng409]

求一个累加的sql语句

数据库中的表如：
id 主键 开始日期 结束日期 连续年限 累计年限 工种性质

a_id a0188 c95901 c95902 c95903 c95904 c95907
1 1413 1983-10-01 1989-09-30 6 0 汽车驾驶员
2 1413 1993-01-01 1999-12-31 7 0 汽车驾驶员
3 1413 2000-01-01 2001-12-31 2 0 汽车驾驶员
4 1413 2002-01-01 2002-06-30 2 0 汽车驾驶员

1 4157 1985-10-01 1986-07-31 1.58 0 汽车驾驶员
2 1413 1986-08-01 1987-06-30 7 0 轮胎工
3 1413 1987-07-01 1992-12-31 5.5 0 轮胎工
4 1413 1993-01-01 1999-12-31 7 0 汽车驾驶员
5 1413 2000-01-01 2001-12-31 2 0 汽车驾驶员

工作性质一样的，累计年限中累加，否则不累加。

我想要的效果如：


a_id a0188 c95901 c95902 c95903 c95904 c95907
1 1413 1983-10-01 1989-09-30 6 0 汽车驾驶员
2 1413 1993-01-01 1999-12-31 7 13 汽车驾驶员
3 1413 2000-01-01 2001-12-31 2 15 汽车驾驶员
4 1413 2002-01-01 2002-06-30 2 17 汽车驾驶员

1 4157 1985-10-01 1986-07-31 1.58 0 汽车驾驶员
2 1413 1986-08-01 1987-06-30 7 7 轮胎工
3 1413 1987-07-01 1992-12-31 5.5 12.5 轮胎工
4 1413 1993-01-01 1999-12-31 7 0 汽车驾驶员
5 1413 2000-01-01 2001-12-31 2 9 汽车驾驶员

[pengpeng409]

给位大哥。。。。。。怎么就没有人回答我的问题了。。。。。

[--小F--]

select
  a_id ,a0188, c95901, c95902 ,c95903,
  c95904=(select sum(c95903) from tb where c95907=t.c95907 and a_id<=t.a_id),
  c95907  
from
  tb t

[--小F--]

这个MM怎么像稻子姐？

[pengpeng409]

这就是稻子姐的图像。。。。。哈哈

[pengpeng409]

谢谢。。。。小f。。。。我自己也谢了一个。。没有你那个简单。。。
SQL codeselect
update c959 set c95904=case when isnull(a.c95904,0)>0 then c95903+a.c95904
else c95903+a.b end
from (select min(a_id) c,a0188 a,c95903 b,c95904,c95905,c95906,c95907 from c959
group by a0188,c95903,c95904,c95905,c95906,c95907)a
where a.a=c959.a0188
and c959.c95907=a.c95907
and c959.a_id=(a.c+1)

[--小F--]

你娃暗恋稻子姐？

[pengpeng409]

是的。。。有点。。。稻子姐。。长得太可爱了。。。所以我有点倾向。。。。。。

[叶子]

declare @T table 
(a_id int,a0188 int,c95901 datetime,c95902 datetime,c95903 numeric(3,2),c95904 numeric(6,2),c95907 varchar(10))
insert into @T
select 1,1413,'1983-10-01','1989-09-30',6,0,'汽车驾驶员' union all
select 2,1413,'1993-01-01','1999-12-31',7,0,'汽车驾驶员' union all
select 3,1413,'2000-01-01','2001-12-31',2,0,'汽车驾驶员' union all
select 4,1413,'2002-01-01','2002-06-30',2,0,'汽车驾驶员' union all
select 1,4157,'1985-10-01','1986-07-31',1.58,0,'汽车驾驶员' union all
select 2,1413,'1986-08-01','1987-06-30',7,0,'轮胎工' union all
select 3,1413,'1987-07-01','1992-12-31',5.5,0,'轮胎工' union all
select 4,1413,'1993-01-01','1999-12-31',7,0,'汽车驾驶员' union all
select 5,1413,'2000-01-01','2001-12-31',2,0,'汽车驾驶员'


declare @a0188 int 
declare @c95907 varchar(20)
declare @i decimal(18,2) set @i=0.00

update @T
set @i=case when a0188=@a0188 and c95907=@c95907 then 
@i+c95903 else c95903 end,
@a0188=a0188,@c95907=c95907,c95904=@i

select * from @T
/*
a_id        a0188       c95901                  c95902                  c95903                                  c95904                                  c95907
----------- ----------- ----------------------- ----------------------- --------------------------------------- --------------------------------------- ----------
1           1413        1983-10-01 00:00:00.000 1989-09-30 00:00:00.000 6.00                                    6.00                                    汽车驾驶员
2           1413        1993-01-01 00:00:00.000 1999-12-31 00:00:00.000 7.00                                    13.00                                   汽车驾驶员
3           1413        2000-01-01 00:00:00.000 2001-12-31 00:00:00.000 2.00                                    15.00                                   汽车驾驶员
4           1413        2002-01-01 00:00:00.000 2002-06-30 00:00:00.000 2.00                                    17.00                                   汽车驾驶员
1           4157        1985-10-01 00:00:00.000 1986-07-31 00:00:00.000 1.58                                    1.58                                    汽车驾驶员
2           1413        1986-08-01 00:00:00.000 1987-06-30 00:00:00.000 7.00                                    7.00                                    轮胎工
3           1413        1987-07-01 00:00:00.000 1992-12-31 00:00:00.000 5.50                                    12.50                                   轮胎工
4           1413        1993-01-01 00:00:00.000 1999-12-31 00:00:00.000 7.00                                    7.00                                    汽车驾驶员
5           1413        2000-01-01 00:00:00.000 2001-12-31 00:00:00.000 2.00                                    9.00                                    汽车驾驶员
*/

[叶子]

--不累加出零的话，就这样
declare @T table 
(a_id int,a0188 int,c95901 datetime,c95902 datetime,c95903 numeric(3,2),c95904 numeric(6,2),c95907 varchar(10))
insert into @T
select 1,1413,'1983-10-01','1989-09-30',6,0,'汽车驾驶员' union all
select 2,1413,'1993-01-01','1999-12-31',7,0,'汽车驾驶员' union all
select 3,1413,'2000-01-01','2001-12-31',2,0,'汽车驾驶员' union all
select 4,1413,'2002-01-01','2002-06-30',2,0,'汽车驾驶员' union all
select 1,4157,'1985-10-01','1986-07-31',1.58,0,'汽车驾驶员' union all
select 2,1413,'1986-08-01','1987-06-30',7,0,'轮胎工' union all
select 3,1413,'1987-07-01','1992-12-31',5.5,0,'轮胎工' union all
select 4,1413,'1993-01-01','1999-12-31',7,0,'汽车驾驶员' union all
select 5,1413,'2000-01-01','2001-12-31',2,0,'汽车驾驶员'

declare @a0188 int 
declare @c95907 varchar(20)
declare @i decimal(18,2) set @i=0.00

update @T
set @i=case when a0188=@a0188 and c95907=@c95907 then 
@i+c95903 else 0 end,
@a0188=a0188,@c95907=c95907,c95904=@i

select * from @T
/*
a_id        a0188       c95901                  c95902                  c95903                                  c95904                                  c95907
----------- ----------- ----------------------- ----------------------- --------------------------------------- --------------------------------------- ----------
1           1413        1983-10-01 00:00:00.000 1989-09-30 00:00:00.000 6.00                                    6.00                                    汽车驾驶员
2           1413        1993-01-01 00:00:00.000 1999-12-31 00:00:00.000 7.00                                    13.00                                   汽车驾驶员
3           1413        2000-01-01 00:00:00.000 2001-12-31 00:00:00.000 2.00                                    15.00                                   汽车驾驶员
4           1413        2002-01-01 00:00:00.000 2002-06-30 00:00:00.000 2.00                                    17.00                                   汽车驾驶员
1           4157        1985-10-01 00:00:00.000 1986-07-31 00:00:00.000 1.58                                    1.58                                    汽车驾驶员
2           1413        1986-08-01 00:00:00.000 1987-06-30 00:00:00.000 7.00                                    7.00                                    轮胎工
3           1413        1987-07-01 00:00:00.000 1992-12-31 00:00:00.000 5.50                                    12.50                                   轮胎工
4           1413        1993-01-01 00:00:00.000 1999-12-31 00:00:00.000 7.00                                    7.00                                    汽车驾驶员
5           1413        2000-01-01 00:00:00.000 2001-12-31 00:00:00.000 2.00                                    9.00                                    汽车驾驶员
*/

[pengpeng409]

谢谢。。。。叶子。。。。。

[prpei]

[Quote=引用 10 楼 maco_wang 的回复:]
SQL code


declare @T table
(a_id int,a0188 int,c95901 datetime,c95902 datetime,c95903 numeric(3,2),c95904 numeric(6,2),c95907 varchar(10))
insert into @T
select 1,1413,'1983-10-01','1989-09-30……
[/Quote]



姐，你大才啊，这下回复了，以后教我这菜鸟啊！

[pengpeng409]

求各位帮忙看看这个sql语句怎么写。。。。

[pengpeng409]

人了。。。。。。。。。。。。。