问上这个查询语句如何写

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.daimami.com/other/1391128.html