27,582
社区成员




declare @tb table(mc varchar(10),fc int,sr int,fp int)
insert @tb select'A1','100','510','10'
insert @tb select 'A1','215','510','10'
insert @tb select 'A1','200','510','10'
insert @tb select 'A2','320','330','5'
insert @tb select 'A2','10','330','5'
insert @tb select 'A3','33','33','3';
;
with t as
(
select *
,ROW_NUMBER()over(PARTITION by mc order by fc desc) rn
,SUM(fc) over(PARTITION by mc) RealSr
,max(fc) over(PARTITION by mc) MaxFr
from @tb
)
update t set sr=case when rn>1 then fc else sr-(RealSr-MaxFr) end,fp=case when rn>1 then 0 else fp end
select * from @tb
;with t as
(
select *,rn=ROW_NUMBER()over(PARTITION by mc order by fc desc)
from @tb
)
update t set fp=0 where rn>1;