主表
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
------解决思路----------------------
--> 测试数据: [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