不积跬步,无以至千里;不积小流,无以成江海。

Dean's blog

  • Join Us on Facebook!
  • Follow Us on Twitter!
  • LinkedIn
  • Subcribe to Our RSS Feed

订单金额按比例拆分到不同的商品

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

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

不允许评论
粤ICP备17049187号-1