关于SQL Server的两个递归查询

参考文献：CSDN，《SQL 案例解析》

SQL Server 中其实也有类似其他高级语言中的递归查询，SQL Server2005之后，可以利用 CTE较方便的使用递归查询，以下是我从网站及书本上学习到的两个比较有意思的，利用CTE进行递归查询的案例。

案例一：合并连续号码

测试数据

SELECT *
INTO #tb
FROM
(	SELECT 1 AS id, 100 AS StartNum , 200 AS  EndNum , 10 AS Amount UNION ALL
	SELECT 2 AS id, 201 AS StartNum , 250 AS  EndNum , 20 UNION ALL
	SELECT 3 AS id, 251 AS StartNum , 300 AS  EndNum , 30 UNION ALL
	SELECT 4 AS id, 400 AS StartNum , 450 AS  EndNum , 40UNION ALL
	SELECT 5 AS id, 451 AS StartNum , 500 AS  EndNum , 50 UNION ALL
	SELECT 6 AS id, 500 AS StartNum , 600 AS  EndNum , 60 UNION ALL
	SELECT 7 AS id, 601 AS StartNum , 700 AS  EndNum , 70 UNION ALL
	SELECT 8 AS id, 701 AS StartNum , 800 AS  EndNum , 80 UNION ALL
	SELECT 9 AS id, 801 AS StartNum , 900 AS  EndNum , 90
)tt

目标：合并连续号码，并统计该区段中数量

StartNum EndNum Amount
100 300 60
400 500 90
500 900 300

解决方案：

WITH MyTb  AS (
	SELECT id,StartNum,EndNum,Amount
	FROM #tb  AS t1
	UNION ALL
	SELECT MyTb.id,MyTb.StartNum,#tb.EndNum,#tb.Amount+ MyTb.Amount AS Amount
	FROM MyTb
	Inner JOIN #tb
		ON #tb.StartNum - 1 = MyTb.EndNum
   )
SELECT MIN(t1.StartNum) AS StartNum,t1.EndNum AS EndNum,MAX(t1.Amount) AS SumAmount
FROM MyTb AS t1
LEFT JOIN Mytb AS t2
	ON t1.EndNum = t2.StartNum -1
WHERE t2.Amount IS NULL
GROUP BY t1.EndNum

案例二：最大装载问题 

测试数据

SELECT *
INTO #t
FROM
	(SELECT 1 AS GoodId, 10.0 AS Weigh	UNION ALL
	 SELECT 2 AS GoodId, 20.0 AS Weigh	UNION ALL
	 SELECT 3 AS GoodId, 30.0 AS Weigh	UNION ALL
	 SELECT 4 AS GoodId, 40.0 AS Weigh	UNION ALL
	 SELECT 5 AS GoodId, 50.0 AS Weigh	UNION ALL
	 SELECT 6 AS GoodId, 60.0 AS Weigh	UNION ALL
	 SELECT 7 AS GoodId, 70.0 AS Weigh	UNION ALL
	 SELECT 8 AS GoodId, 80.0 AS Weigh	
	 )AS tt

目标：给定商品Id与商品重量，给定箱子容量，求最大装载数量最终，商品数最多的2中方案。

商品ID组合    最大重量

12456 180
12357 180

解决方案：

WITH tb AS
	(SELECT 1 AS N, GoodId,
		CAST('1' AS  VARCHAR(20) )AS Result,CAST(Weigh AS Int) AS Weigh, 
		0 AS G1, 0 AS  G2, 0 AS G3,0 AS G4, 0 AS G5
	 FROM #t
	 UNION ALL
	 SELECT N+1 AS N,#t.GoodId,CAST((Result+CAST(#t.GoodId AS VARCHAR(2))) AS VARCHAR(20) ),
		CAST((tb.Weigh+#t.Weigh) AS Int) AS Weigh,
		G1,
		CASE WHEN N = 1 THEN #t.GoodId ELSE G2 END AS G2,
		CASE WHEN N = 2 THEN #t.GoodId ELSE G3 END AS G3,
		CASE WHEN N = 3 THEN #t.GoodId ELSE G4 END AS G4,
		CASE WHEN N = 4 THEN #t.GoodId ELSE G5 END AS G5
	 FROM tb
	 INNER JOIN #t
		ON #t.Weigh + tb.Weigh <= 180.0
		AND tb.GoodId < #t.GoodId
	)
SELECT TOP 2 *
FROM tb
ORDER BY Weigh DESC,LEN(Result) DESC