### 案列分享：T-SQL 分配計算

## 問題

GO

-- 分店訂購資料
CREATE TABLE StoreOrders
(
Store NVARCHAR(1) NOT NULL,
Product NVARCHAR(10) NOT NULL,
OrderQuantity INT
)

INSERT INTO StoreOrders
SELECT 'A', 'Apple', 7
UNION ALL SELECT 'B', 'Apple', 10
UNION ALL SELECT 'C', 'Apple', 8
UNION ALL SELECT 'A', 'Lemon', 12
UNION ALL SELECT 'B', 'Lemon', 10
UNION ALL SELECT 'C', 'Lemon', 24
UNION ALL SELECT 'A', 'Orange', 8
UNION ALL SELECT 'B', 'Orange', 32;
GO

-- 在庫商品存量
CREATE TABLE Products
(
Product NVARCHAR(10) NOT NULL,
QuantityInStock INT NOT NULL
)

INSERT INTO Products
SELECT 'Apple', 20
UNION ALL SELECT 'Lemon', 38
UNION ALL SELECT 'Orange', 32
GO

-- ## 迴圈計算法 ##
-- ================
USE tempdb;
GO

-- 依訂購量遞減排序
SELECT
[No] = ROW_NUMBER() OVER(PARTITION BY Product ORDER BY OrderQuantity DESC),
Store, Product, Orderquantity,
0 AS RealQuantity,
0 AS [Skip]
INTO #tmp
FROM StoreOrders;

DECLARE @Product NVARCHAR(100), @CurProduct NVARCHAR(10);

-- 取出所有產品
SET @Product = ''
SELECT @Product = @Product + Product + ',' FROM Products;

WHILE (LEN(@Product) > 0)
BEGIN
-- 擷取單一商品
SET @CurProduct = SUBSTRING(@Product, 1, CHARINDEX(',', @Product, 1) - 1);

DECLARE
@QuantityInStock INT,	-- 在庫數量
@No INT					-- 分配順位

SELECT @QuantityInStock = QuantityInStock FROM Products WHERE (Product = @CurProduct);
SET @No = 0;

WHILE (@QuantityInStock > 0)
BEGIN
SET @No = @No + 1;

-- 項次大於最高項則重設為 1
IF(@No > (SELECT MAX([No]) FROM #tmp WHERE (Product = @CurProduct))) SET @No = 1;

IF EXISTS(SELECT 1 FROM #tmp WHERE ([No] = @No) AND (Product = @CurProduct) AND (RealQuantity < OrderQuantity))
BEGIN
-- 配額小於訂購量逐次加 1
UPDATE #tmp SET RealQuantity = RealQuantity + 1 WHERE ([No] = @No) AND (Product = @CurProduct) AND ([Skip] = 0);
SET @QuantityInStock = @QuantityInStock - 1;
END
ELSE
-- 配額已達需求則標記略過
UPDATE #tmp SET [Skip] = 1 WHERE ([No] = @No) AND (Product = @CurProduct);
End

-- 剃除分配完成商品
SET @Product = RIGHT(@Product, LEN(@Product) - CHARINDEX(',', @Product, 1));
END

SELECT Store, Product, OrderQuantity, RealQuantity
FROM #tmp
ORDER BY Product, Store;

DROP TABLE #tmp;
GO

-- ## 遞迴式 CTE ##
-- ================
USE tempdb;
GO

;WITH ext AS (
SELECT so.Store, so.Product, so.OrderQuantity, 0 AS RealQuantity, p.QuantityInStock
FROM StoreOrders so INNER JOIN Products p ON so.Product = p.Product
UNION ALL
SELECT Store, Product, OrderQuantity, RealQuantity + 1, QuantityInStock
FROM ext
WHERE (RealQuantity < OrderQuantity)
), tmp AS (
SELECT Store, Product, OrderQuantity, RealQuantity,
QuantityInStock = QuantityInStock - ROW_NUMBER() OVER (PARTITION BY Product ORDER BY RealQuantity, OrderQuantity DESC)
FROM ext
WHERE (RealQuantity > 0)
)
SELECT Store, Product, OrderQuantity, RealQuantity = MAX(RealQuantity)
FROM tmp
WHERE (QuantityInStock >= 0)
GROUP BY Store, Product, OrderQuantity
ORDER BY Product, Store;
GO

《2010-08-02 補充》

-- ## 比例分配法 ##
-- ================
USE tempdb;
GO

-- 依訂量排順位
SELECT
[No] = ROW_NUMBER() OVER(PARTITION BY Product ORDER BY OrderQuantity DESC),
Store, Product, Orderquantity,
0 AS RealQuantity
INTO #tmp
FROM StoreOrders;

DECLARE @Product NVARCHAR(100), @CurProduct NVARCHAR(10);

-- 取出所有產品
SET @Product = ''
SELECT @Product = @Product + Product + ',' FROM Products;

WHILE (LEN(@Product) > 0)
BEGIN
-- 擷取單一商品
SET @CurProduct = SUBSTRING(@Product, 1, CHARINDEX(',', @Product, 1) - 1);

DECLARE
@QuantityInStock INT,	-- 在庫數量
@TotalOrders DECIMAL,	-- 總訂購量
@No INT					-- 分配順位

SELECT @QuantityInStock = QuantityInStock FROM Products WHERE (Product = @CurProduct);
SELECT @TotalOrders = SUM(OrderQuantity) FROM StoreOrders WHERE (Product = @CurProduct);
SET @No = 0;

WHILE (@QuantityInStock > 0)
BEGIN
SET @No = @No + 1;

DECLARE @CurRealQuantity INT, @CurOrderQuantity INT;

-- 按權重計算配額
SELECT
@CurRealQuantity = @QuantityInStock * (OrderQuantity / @TotalOrders),
@CurOrderQuantity = OrderQuantity
FROM #tmp WHERE (Product = @CurProduct) AND ([No] = @No);

UPDATE #tmp SET RealQuantity = @CurRealQuantity WHERE (Product = @CurProduct) AND ([No] = @No);

SET @QuantityInStock = @QuantityInStock - @CurRealQuantity;
SET @TotalOrders = @TotalOrders - @CurOrderQuantity;
END

-- 剃除分配完成商品
SET @Product = RIGHT(@Product, LEN(@Product) - CHARINDEX(',', @Product, 1));
END

SELECT Store, Product, OrderQuantity, RealQuantity
FROM #tmp
ORDER BY Product, Store;

DROP TABLE #tmp;
GO