求一SQL 指令（主表金额比例分配问题）

[microd]

主表
site_id order_sn money_paid
1 4485408943 42.30

从表
site_id order_sn goods_sn_cid qty cid_price
1 4485408943 31404002 2 2.80
1 4485408943 31404001 1 4.80
1 4485408943 31407005 1 12.90
想把money_paid金额42.3 按从表的qty*cid_price 汇总比例分配这个金额，用子查询一个个的更新太麻烦。那个大侠帮帮看如何有高效一定的写法。想得到的结果，
site_id order_sn goods_sn_cid qty cid_price per amnout
1 4485408943 31404002 2 2.80 0.24 10.17
1 4485408943 31404001 1 4.80 0.206 8.71
1 4485408943 31407005 1 12.90 0.554 23.41

谢谢

[还在加载中灬]

SELECT
	T1.*
	,CAST(qty*cid_price/(SUM(qty*cid_price)OVER(PARTITION BY T1.site_id,T1.order_sn))AS DECIMAL(19,3))per
	,CAST(qty*cid_price/(SUM(qty*cid_price)OVER(PARTITION BY T1.site_id,T1.order_sn))*T2.money_paid AS DECIMAL(19,2)) amnout
FROM
	从表 T1
	JOIN 主表 T2 ON T1.site_id = T2.site_id AND T1.order_sn = T2.order_sn

[pt1314917]

--> 测试数据: [A]
if object_id('[A]') is not null drop table [A]
create table [A] (site_id int,order_sn bigint,money_paid numeric(4,2))
insert into [A]
select 1,4485408943,42.30
--> 测试数据: [B]
if object_id('[B]') is not null drop table [B]
create table [B] (site_id int,order_sn bigint,goods_sn_cid int,qty int,cid_price numeric(4,2))
insert into [B]
select 1,4485408943,31404002,2,2.80 union all
select 1,4485408943,31404001,1,4.80 union all
select 1,4485408943,31407005,1,12.90
go

--语句
;with wsp
as
(
	select *,per=qty*cid_price/(select SUM(qty*cid_price) from b) from b
)
select b.site_id,b.order_sn,b.goods_sn_cid,b.qty,b.cid_price,per=cast(b.per as decimal(5,2)),
amnout=cast(a.money_paid*b.per as decimal(8,2)) from wsp b,a where a.order_sn=b.order_sn


--结果：
site_id     order_sn             goods_sn_cid qty         cid_price                               per                                     amnout
----------- -------------------- ------------ ----------- --------------------------------------- --------------------------------------- ---------------------------------------
1           4485408943           31404002     2           2.80                                    0.24                                    10.17
1           4485408943           31404001     1           4.80                                    0.21                                    8.71
1           4485408943           31407005     1           12.90                                   0.55                                    23.42

[microd]

谢谢ky_min，OVER 函数满好用的。