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
这里的做法是,先平分金额,只舍不入,再通过误差补偿的方式消除误差。