SQL中单价分摊有关问题

SQL中单价分摊问题
销售单明细档
订单号   行号   料号        发运日期        数量    单价
123            10         A                     14-2-12                  19          323.10
123             20        B                     14-3-12                  25          423.10
123             30        C                     14-4-12                  41          123.10
123             40        D                     14-5-12                  73          223.10
123             50        Discount        14-5-12                  1            -1223.10

第五十行其实是这笔订单的折让金额
现在要把 行号50  平均分摊到每一行上，尾数放在最后一行！
给力的神们，求知道！！
------解决思路----------------------
要的结果发下
------解决思路----------------------

------------------------------------------------------------------------------

--Author:霜寒月冷

--VERSION:  Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2) 

--Creation Date:  01/08/14

--Last Modified:  [09:17:28]

----------------------------建表------------------------------------------

if object_id('[tb]') is not null drop table [tb]

go

create table tb (订单号 int ,  行号 int,  料号 varchar(10),       发运日期 varchar(10),        数量 int,   单价 decimal(7,2) )

insert into tb

select '123','10','A','14-2-12','19','323.10'union all

select '123','20','B','14-3-12','25','423.10'union all

select '123','30','C','14-4-12','41','123.10'union all

select '123','40','D','14-5-12','73','223.10'union all

select '123','50','Discount','14-5-12','1','-1223.10'


go



;with cte as

(

select * ,row_number() over (order by getdate())as rn from tb 


)

,

cte1 as

(

select  top 1  rn as [number],cte.单价*cte.数量/rn-1 as 补充价 from cte where cte.行号=50

)


select    a.订单号,a.行号,a.料号,a.发运日期,a.数量,b.补充价%a.数量+a.单价 as 价格   from cte a  ,cte1  b where a.rn<b.number



----------------------------结果------------------------------------------

/* 

订单号         行号          料号         发运日期       数量          价格

----------- ----------- ---------- ---------- ----------- ---------------------------------------

123         10          A          14-2-12    19          305.4800000000000000000000

123         20          B          14-3-12    25          402.4800000000000000000000

123         30          C          14-4-12    41          82.4800000000000000000000

123         40          D          14-5-12    73          196.4800000000000000000000