if object_id('[工资表]') is not null drop table [工资表]
go
create table [工资表] (name nvarchar(8),col nvarchar(6),moy numeric(6,2))
insert into [工资表]
select 'jack','工资',2000.00 union all
select 'jack','津贴',300.00 union all
select 'jack','加班费',600.00 union all
select 'mark','工资',2200.00 union all
select 'mark','津贴',500.00 union all
select 'mark','加班费',700.00

select * from [工资表]

WITH TT
AS(
SELECT ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY GETDATE()) AS NO ,* FROM [工资表])

SELECT TT.NAME,tt.col,(SELECT SUM(moy) FROM TT A WHERE A.NAME = TT.NAME AND A.no<=TT.no) AS moy FROM TT 

/*
NAME    col        moy
jack    工资    2000.00
jack    津贴    2300.00
jack    加班费    2900.00
mark    工资    2200.00
mark    津贴    2700.00
mark    加班费    3400.00*/

------解决方案--------------------
SQL code

create table 工资表
(name varchar(10),ctype varchar(10),qty decimal(7,2))

insert into 工资表
select 'jack', '工资', 2000.00 union all
select 'jack', '津贴', 300.00 union all
select 'jack', '加班费', 600.00 union all
select 'mark', '工资', 2200.00 union all
select 'mark', '津贴', 500.00 union all
select 'mark', '加班费', 700.00


with t as
(select name,ctype,qty,
 row_number() over(partition by name order by getdate()) rn
 from 工资表
)
select t1.name,t1.ctype,
(select sum(t2.qty) from t t2 where t2.name=t1.name and t2.rn<=t1.rn) 'qty'
from t t1

/*
name       ctype      qty
---------- ---------- ---------------
jack       工资         2000.00
jack       津贴         2300.00
jack       加班费        2900.00
mark       工资         2200.00
mark       津贴         2700.00
mark       加班费        3400.00

(6 row(s) affected)
*/

------解决方案--------------------
我就发个用递归的吧
SQL code

select * into 工资表 from(
select 'jack' c1, '工资' c2, 2000 c3 union all
select 'jack', '津贴' ,300
union all select 'jack' ,'加班费', 600
union all select 'mark' ,'工资', 2200
union all select 'mark', '津贴', 500
union all select 'mark', '加班费', 700
)a

with cte as(
select *,rid =case c2 when '工资' then 1 when '津贴' then 2 when '加班费' then 3 end 
from 工资表 a1
),cte2 as(
    select rid,c1,c2,c3 from cte where rid=1
    union all 
    select cte.rid,cte.c1,cte.c2,cte2.c3+cte.c3 
    from cte inner join cte2 on cte.c1=cte2.c1 and cte.rid=cte2.rid+1
)
select * from cte2
order by c1,rid
我的异常网推荐解决方案：软件开发者薪资,http://www.aiyiweb.com/other/1391128.html