日期:2014-05-17 浏览次数:20718 次
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*/
------解决方案--------------------
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) */
------解决方案--------------------
我就发个用递归的吧
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