参考文献:优快云,《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