日期:2014-05-18 浏览次数:20663 次
create table #tab (personid varchar(10),name varchar(8),js int,bl numeric(2,1),dw money,gr money ,ym varchar(10)) insert into #tab values('001','王小光',6600,0.1,660,660,'200704') insert into #tab values('001','王小光',6600,0.1,660,660,'200705') insert into #tab values('001','王小光',5500,0.1,550,550,'200706') insert into #tab values('001','王小光',5500,0.2,1100,1100,'200707') insert into #tab values('002','肖光',6879,0.1,687.9,687.9,'200705') select #tab.*,flag=0 into #t from #tab order by personid,ym declare @personid varchar(10),@js int,@bl numeric(2,1),@flag int set @flag=0 update #t set @flag=case when @personid=personid and @js=js and @bl=bl then @flag else @flag+1 end, @personid=case when @personid=personid and @js=js and @bl=bl then @personid else personid end , @js=case when @personid=personid and @js=js and @bl=bl then @js else js end, @bl=case when @personid=personid and @js=js and @bl=bl then @bl else bl end, flag=@flag select 人员编号=max(personid),姓名=max(name),保险基数=max(js),比率=max(bl),单位缴费=sum(dw),个人缴费=sum(gr),保险补缴年月=case when (select count(1) from #t where flag=a.flag)>1 then min(ym)+'-'+max(ym) else max(ym) end from #t a group by flag drop table #t ----结果 人员编号 姓名 保险基数 比率 单位缴费 个人缴费 保险补缴年月 ---- ------ ----------- --------------------------------------- --------------------- --------------------- --------------------- 001 王小光 6600 0.1 1320.00 1320.00 200704-200705 001 王小光 5500 0.1 550.00 550.00 200706 001 王小光 5500 0.2 1100.00 1100.00 200707 002 肖光 6879 0.1 687.90 687.90 200705 (4 行受影响)