1 CREATE TABLE #t1( 2        PlantNo    varchar(4), 3        vendorCode varchar(10), 4        amount decimal(18,2)) 5         6 insert #t1 values(‘P112‘,‘1‘,10) 7 insert #t1 values(‘P112‘,‘2‘,20) 8 insert #t1 values(‘P112‘,‘3‘,30) 9 insert #t1 values(‘P112‘,‘4‘,40)10 insert #t1 values(‘P115‘,‘1‘,10)11 12 select #t1.plantno,vendorCode,amount,amount as amountTotal,amount  as amountTotal2,amount as rate13   into #t214 from #t1 order by plantNo,amount desc15 16 update #t2 set amountTotal=(select SUM(amount) from #t1 where #t1.PlantNo=#t2.PlantNo)17 update #t2 set amountTotal2=(select SUM(amount) from #t1 where #t1.PlantNo=#t2.PlantNo and amount>=#t2.amount)18 update #t2 set rate=amountTotal2/amountTotal19 select * from #t220 drop table #t121 drop table #t222 23 --結果24 plantno    vendorCode    amount    amountTotal    amountTotal2    rate25 P112    4    40.00    100.00    40.00    0.4026 P112    3    30.00    100.00    70.00    0.7027 P112    2    20.00    100.00    90.00    0.9028 P112    1    10.00    100.00    100.00    1.0029 P115    1    10.00    10.00    10.00    1.00