问下这个查询语句怎么写啊
工资表
jack工资2000.00
jack津贴300.00
jack加班费600.00
mark工资2200.00
mark津贴500.00
mark加班费700.00


查询结果:

jack工资2000.00
jack津贴2300.00
jack加班费2900.00
mark工资2200.00
mark津贴2700.00
mark加班费3400.00


[解决办法]

SQL code

if object_id('[工资表]') is not null drop table [工资表]gocreate table [工资表] (name nvarchar(8),col nvarchar(6),moy numeric(6,2))insert into [工资表]select 'jack','工资',2000.00 union allselect 'jack','津贴',300.00 union allselect 'jack','加班费',600.00 union allselect 'mark','工资',2200.00 union allselect 'mark','津贴',500.00 union allselect 'mark','加班费',700.00select * from [工资表]WITH TTAS(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        moyjack    工资    2000.00jack    津贴    2300.00jack    加班费    2900.00mark    工资    2200.00mark    津贴    2700.00mark    加班费    3400.00*/
[解决办法]
SQL code
create table 工资表(name varchar(10),ctype varchar(10),qty decimal(7,2))insert into 工资表select 'jack', '工资', 2000.00 union allselect 'jack', '津贴', 300.00 union allselect 'jack', '加班费', 600.00 union allselect 'mark', '工资', 2200.00 union allselect 'mark', '津贴', 500.00 union allselect 'mark', '加班费', 700.00with 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.00jack       津贴         2300.00jack       加班费        2900.00mark       工资         2200.00mark       津贴         2700.00mark       加班费        3400.00(6 row(s) affected)*/
[解决办法]
我就发个用递归的吧
SQL code
select * into 工资表 from(select 'jack' c1, '工资' c2, 2000 c3 union allselect 'jack', '津贴' ,300union all select 'jack' ,'加班费', 600union all select 'mark' ,'工资', 2200union all select 'mark', '津贴', 500union all select 'mark', '加班费', 700)awith 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 cte2order by c1,rid
我的异常网推荐解决方案：软件开发者薪资,http://www.myexception.cn/other/1391128.html