分类求和与分摊

[张三儿T]

有一个数据表，存放所有出库物料的合同号和金额。
我想把其中某个指定的合同号单独拿出来，按照比例去分摊金额，不知道这个代码的思路是什么，麻烦各位大神指点下。

数据库很大，就不上真实数据了。
用excel做出示范例子，请大神见谅

这是数据库


这是执行后的代码

积分不多，请多多见谅

[繁花尽流年]

最后一列A合同分摊是怎么算的？

[繁花尽流年]

IF OBJECT_ID('tempdb..#tmp_1') IS NOT NULL DROP TABLE #tmp_1
CREATE TABLE #tmp_1 (contractNo VARCHAR(10),amount DECIMAL(18,2))

INSERT INTO #tmp_1 (contractNo,amount)
VALUES('A','5'),('B','6'),('C','7'),('D','2'),('E','3'),('F','6'),('A','4'),('B','65'),('C','2'),('E','32'),('D','21'),('A','321'),('Z','365'),('A','32')

SELECT	a.contractNo,a.amount
		,CAST(CAST(a.amount*1.0/b.total_amount*100 AS DECIMAL(18,2)) AS VARCHAR(10))+'%' AS amount_pct
		,CAST(a.amount*1.0/b.total_amount*total_amount_A AS DECIMAL(18,2)) AS amount_A
FROM (
	SELECT contractNo,SUM(ISNULL(amount,0)) AS amount 
	FROM #tmp_1
	WHERE contractNo <>'A'
	GROUP BY contractNo
) a
CROSS APPLY (	
	SELECT SUM(ISNULL(amount,0)) AS total_amount 
	FROM #tmp_1
	WHERE contractNo <>'A'
) b 
CROSS APPLY (	
	SELECT SUM(ISNULL(amount,0)) AS total_amount_A 
	FROM #tmp_1
	WHERE contractNo ='A'
) c 
UNION
SELECT '合计'
,(	SELECT SUM(ISNULL(amount,0)) AS total_amount 
	FROM #tmp_1
	WHERE contractNo <>'A'
)
,''
,(	
	SELECT SUM(ISNULL(amount,0)) AS total_amount_A 
	FROM #tmp_1
	WHERE contractNo ='A'
)

contractNo amount amount_pct amount_A ---------- --------------------------------------- ----------- --------------------------------------- B 71.00 13.95% 50.50 C 9.00 1.77% 6.40 D 23.00 4.52% 16.36 E 35.00 6.88% 24.89 F 6.00 1.18% 4.27 Z 365.00 71.71% 259.59 合计 509.00 362.00