Dean's blog

DECLARE @Mixingboxs TABLE(
	mixing_code varchar(10),  
	product_code varchar(10),
	item_count int						--拆分的依据
)
DECLARE @sales TABLE(
	mixing_code varchar(10),
	amount decimal(18, 2)
)

INSERT INTO @sales VALUES('123456', 3443.5)
INSERT INTO @Mixingboxs VALUES('123456', 'product1', 1)
INSERT INTO @Mixingboxs VALUES('123456', 'product2', 3)
INSERT INTO @Mixingboxs VALUES('123456', 'product3', 3)


SELECT *
	, itemAmount + CASE 
			WHEN amount >= SUM(itemAmount) OVER(PARTITION BY mixing_code) + ROW_NUMBER() OVER(PARTITION BY mixing_code ORDER BY itemAmount ) / 100.0 THEN 0.01
			ELSE 0
		END AS 调整后金额
FROM(
	SELECT s.amount
		, m.*
		, SUM(item_count) OVER(PARTITION BY m.mixing_code) As SumCount
		, CONVERT(INT, 100 * item_count * amount / SUM(item_count) OVER(PARTITION BY m.mixing_code), 120) / 100.0 AS itemAmount
	FROM @sales s
		INNER JOIN @Mixingboxs m ON s.mixing_code = m.mixing_code
)T

这里的做法是，先平分金额，只舍不入，再通过误差补偿的方式消除误差。