[SQL] 產品組合

在 論壇 上看見的問題，需求是要組合每個 menu 內產品，該 menu 內假如有三個產品的話，產品組合就只要三個產品的組合，兩個產品的組合就過濾掉不需要出現，資料來源和期待結果如下圖

透過 CTE 遞迴來處理產品組合

CREATE TABLE Temp (
	menu smallint , 
	option_type char(1) , 
	option_val char(1));

INSERT INTO Temp VALUES
(1, 'A', '1') , (1, 'A', '2'),
(2, 'A', '1') , (2, 'A', '2'), (2, 'B', '1'), (2, 'B', '2'),
(3, 'A', '1') , (3, 'A', '2') , (3, 'B', '1') , (3, 'B', '2') , (3, 'C', '1') , (3, 'C', '2');

; 
WITH CTE AS
(
	SELECT 
		menu , 
		option_type , 
		option_val , 
		CAST(option_type + option_val AS varchar(8000)) AS Memo ,
		CAST(1 as int) AS Stat
	FROM Temp
	UNION ALL
	SELECT 
		T2.menu , 
		T2.option_type , 
		T2.option_val ,
		T1.Memo + '_' + T2.option_type + T2.option_val ,
		CAST(Stat + 1 as int) AS Stat
	FROM Temp AS T2
		JOIN CTE AS T1 ON T2.menu = T1.menu 
						AND T2.option_type > T1.option_type
)
SELECT 
	F1.* 
FROM CTE AS F1
	JOIN
		(
			SELECT 
				menu , 
				COUNT(DISTINCT option_type) AS Stat
			FROM Temp
			GROUP BY menu 
		) AS F2 ON F1.menu = F2.menu 
					AND F1.stat = F2.stat
ORDER BY menu , memo

• 延伸閱讀
• [Challenge] 計算每項產品最低價的折扣組合
• [Challenge] 計算顏色組合