求一个累加的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 汽车驾驶员
------解决方案--------------------
- SQL code
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
------解决方案--------------------
- 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 @Tselect 1,1413,'1983-10-01','1989-09-30',6,0,'汽车驾驶员' union allselect 2,1413,'1993-01-01','1999-12-31',7,0,'汽车驾驶员' union allselect 3,1413,'2000-01-01','2001-12-31',2,0,'汽车驾驶员' union allselect 4,1413,'2002-01-01','2002-06-30',2,0,'汽车驾驶员' union allselect 1,4157,'1985-10-01','1986-07-31',1.58,0,'汽车驾驶员' union allselect 2,1413,'1986-08-01','1987-06-30',7,0,'轮胎工' union allselect 3,1413,'1987-07-01','1992-12-31',5.5,0,'轮胎工' union allselect 4,1413,'1993-01-01','1999-12-31',7,0,'汽车驾驶员' union allselect 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.00update @Tset @i=case when [email protected] and [email protected] then @i+c95903 else c95903 end,@a0188=a0188,@c95907=c95907,[email protected]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 汽车驾驶员*/