SQL加权平均计算

[ilovelose]

现有ABC三张表，
A表为采购合同表，B表为A表的子表，A表的ID列和B表的parid列如果相同，那么代表该记录为A的子记录信息。C为出库单。C表与B表用batchno来连接。
其中A表有供应商ID(GYSID)，合同号（CGNO），B表有商品编号(itemno)，批号(batchno),采购数量（CGQTY）,采购单价（CGPRICE），采购金额（CGAMT），C表有商品编号（itemno），批次号（batchno），出库数量（outqty）。
一个批次号，会分散到多个采购合同中去，造成一个批次号的商品有多个采购价格。出库数量不等于采购数量，一个采购合同会多次出库。
现在的需求，计算加权平均采购单价，并计算出库成本。多个供应商名称合并成“供应商A,供应商B,供应商C”的格式。
即
供应商 采购单价 出库数量 出库金额
（供应商A,供应商B,供应商C） （加权平均价） （实际出库数量） （加权平均价*实际出库数量得出的金额）


这种SQL语句如何写？

[繁花尽流年]

感觉你没描述清楚是按产品汇总处理，还是即看产品还看批次号统计 按你的结果集，只要全部汇总就能处理，描述一堆的批次号逻辑都没用到。 顺便最好自己准备点样张数据，这样别人更好回复。 参考：https://bbs.csdn.net/topics/391996442

[ilovelose]

表ABC，A表为采购主表，B为采购明细表，C为出库明细表。AB两表中的ID和PARID来判定父子关系，如果PARID=A表ID，那么代表这个记录是A表的子记录。C表靠batchno和ItemNo来相关联。

现在的需求，C表batchno为track18001，itemno为SP1801001的商品出库500，现在想计算平均出库金额为多少。即作加权平均，即等于（20000+9000）*500/1300，并将供应商所有的提供本商品的供应商合并，形式为(GYS1,GYS2)

[ilovelose]

引用 1 楼 zengertao 的回复:

感觉你没描述清楚是按产品汇总处理，还是即看产品还看批次号统计 按你的结果集，只要全部汇总就能处理，描述一堆的批次号逻辑都没用到。 顺便最好自己准备点样张数据，这样别人更好回复。 参考：https://bbs.csdn.net/topics/391996442

已在楼下重新描述，并加入数据。格式还是 供应商 采购单价 出库数量 出库金额 （供应商A,供应商B,供应商C） （加权平均价） （实际出库数量） （加权平均价*实际出库数量得出的金额）

[繁花尽流年]

引用 3 楼 ilovelose 的回复:

[quote=引用 1 楼 zengertao 的回复:] 感觉你没描述清楚是按产品汇总处理，还是即看产品还看批次号统计 按你的结果集，只要全部汇总就能处理，描述一堆的批次号逻辑都没用到。 顺便最好自己准备点样张数据，这样别人更好回复。 参考：https://bbs.csdn.net/topics/391996442

已在楼下重新描述，并加入数据。格式还是 供应商 采购单价 出库数量 出库金额 （供应商A,供应商B,供应商C） （加权平均价） （实际出库数量） （加权平均价*实际出库数量得出的金额） [/quote]

IF OBJECT_ID('tempdb..#tmpA') IS NOT NULL DROP TABLE #tmpA
IF OBJECT_ID('tempdb..#tmpB') IS NOT NULL DROP TABLE #tmpB
IF OBJECT_ID('tempdb..#tmpC') IS NOT NULL DROP TABLE #tmpC

CREATE TABLE #tmpA (ID INT,CGNO VARCHAR(20),GYSID VARCHAR(20),CREATEDATE DATE)
INSERT INTO #tmpA (ID,CGNO,GYSID,CREATEDATE)
VALUES (1,'CG18001','GYS1','2018-02-04'),(2,'CG18002','GYS2','2018-03-07')

CREATE TABLE #tmpB (PARID INT,ITEMNO VARCHAR(20),CGQTY INT, CGPRICE DECIMAL(18,2),CGAMT DECIMAL(18,2),BATCHNO VARCHAR(20))
INSERT INTO #tmpB (PARID,	ITEMNO,	CGQTY,	CGPRICE,	CGAMT,	BATCHNO)
VALUES (1,'SP1801001',1000,20,20000,'Track18001'),(2,'SP1801001',300,30,9000,'Track18001')

CREATE TABLE #tmpC (BATCHNO VARCHAR(20),ITEMNO VARCHAR(20),OUTQTY INT)
INSERT INTO #tmpC (BATCHNO,ITEMNO,OUTQTY)
VALUES ('Track18001','SP1801001',500)

IF OBJECT_ID('tempdb..#tmpD') IS NOT NULL DROP TABLE #tmpD
SELECT b.BATCHNO,b.ITEMNO,SUM(b.CGAMT)*1.0/SUM(b.CGQTY) AS avg_price,SUM(b.CGAMT) AS CGAMT,SUM(b.CGQTY) AS CGQTY
INTO #tmpD
FROM #tmpB b
GROUP BY b.BATCHNO,b.ITEMNO

;WITH a AS (
	SELECT b.BATCHNO,b.ITEMNO,a.GYSID 
	FROM #tmpB b
	INNER JOIN #tmpA a ON b.PARID=a.ID
)
, b AS (
	SELECT BATCHNO,ITEMNO
	,(
		SELECT GYSID+',' FROM a
		WHERE BATCHNO=t.BATCHNO AND ITEMNO=t.ITEMNO
		FOR XML PATH('')
	) AS GYSID
	FROM a AS t
	GROUP BY BATCHNO,ITEMNO
)

SELECT b.GYSID,d.BATCHNO,d.ITEMNO,d.avg_price,c.OUTQTY,d.CGAMT*1.0/d.CGQTY*c.OUTQTY AS OUTAMT
FROM #tmpD d
LEFT JOIN #tmpC c ON c.BATCHNO=d.BATCHNO AND c.ITEMNO=d.ITEMNO
INNER JOIN b ON d.BATCHNO=b.BATCHNO AND d.ITEMNO=b.ITEMNO

拿去试试，下次最好有样张数据直接用文本贴出来，省事很多