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,ymdeclare @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=@flagselect 人员编号=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 flagdrop 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 行受影响)
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,ymdeclare @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=@flagselect 人员编号=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 flagdrop 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 行受影响)
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货