IF OBJECT_ID('tempdb..#Cell') IS NOT NULL DROP TABLE #Cell;
IF OBJECT_ID('tempdb..#OrderDetail') IS NOT NULL DROP TABLE #OrderDetail;

CREATE TABLE #Cell(CellId int, GoodsId int, Qty int)

INSERT INTO #Cell VALUES (1, 1, 72)
INSERT INTO #Cell VALUES (2, 1, 72)
INSERT INTO #Cell VALUES (3, 1, 40)
INSERT INTO #Cell VALUES (11, 2, 240)
INSERT INTO #Cell VALUES (12, 2, 240)
INSERT INTO #Cell VALUES (13, 2, 80)
INSERT INTO #Cell VALUES (14, 2, 10)
INSERT INTO #Cell VALUES (21, 3, 30)
INSERT INTO #Cell VALUES (31, 4, 100)


CREATE TABLE #OrderDetail (OrderDetailId int, GoodsId int, Qty int)

INSERT INTO #OrderDetail VALUES (101, 1, 120)
INSERT INTO #OrderDetail VALUES (102, 2, 100)
INSERT INTO #OrderDetail VALUES (103, 3, 70)
INSERT INTO #OrderDetail VALUES (104, 4, 55)

GO

WITH cte(OrderDetailId,GoodsId,Qty,CellId,min_CellId,lvl,max_qty,cell_qty,used) AS (
	SELECT OrderDetailId,GoodsId,Qty,0 AS CellId,0 AS min_CellId,0 AS lvl,0 AS max_qty,0 AS cell_qty,
		  CAST(',' AS VARCHAR(MAX)) AS used
	FROM  #OrderDetail
	UNION ALL
	SELECT * FROM 
	(SELECT 
			OrderDetailId,
			cte.GoodsId,
			cte.Qty - cell.Qty AS Qty,
			cell.CellId,
			MIN(cell.CellId) OVER(PARTITION BY cte.GoodsID) AS min_CellId,
			cte.lvl + 1 AS lvl,
			MAX(cell.Qty) OVER(PARTITION BY cte.GoodsID) AS max_qty,
			cell.Qty AS cell_qty,
			cte.used + CAST(cell.CellId AS VARCHAR(MAX)) + ',' AS used
	 FROM cte
	 INNER JOIN #Cell AS cell
	 ON (cte.GoodsId = cell.GoodsId AND cte.Qty >= cell.Qty
		 AND cte.used NOT LIKE '%,' + CAST(cell.CellId AS VARCHAR(MAX)) + ',%')) AS a
	WHERE cell_qty = max_qty AND min_CellId = CellId)
SELECT OrderDetailId,CellId
FROM cte
WHERE CellId <> 0
ORDER BY cte.OrderDetailId,CellId