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

Question

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

Answer 1

--> 测试数据: [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

Answer 2

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