本文介绍了SQL Server中递归查询的应用，通过公用表表达式（CTE）处理层次结构数据，实现如创建多行、生成排列组合、提取字段中的多个值、分割日期范围和检索组织结构等复杂操作。
sql server 递归
介绍从SQL Server 2005开始，您可以使用公用表表达式（CTE）创建递归查询。 它们是非常强大的工具，可用于查询层次结构数据，而您事先不知道必须将多少次联接回到同一张表。 这可能是最常见的用法。 但是，它们也可以用于执行各种操作，包括但不限于：基于数量字段创建n行，从字段中提取多个匹配的子字符串，从集合中创建排列/组合，或者取日期范围从一行开始，然后将其分成多个较小范围的行。
递归CTE的基本结构这是CTE的基本结构：
WITH cte AS (
Base Query
UNION ALL
Recursive Query call back to cte
WHERE termination check
)
SELECT * FROM cte- 一个基本查询，该查询返回要在递归中使用的初始行。
- 可以回覆CTE本身的查询。
- 最终导致结果集为空的子句，因此递归可以终止。
＃3是绝对关键。 在某个时候，递归需要结束，以便可以返回结果。 否则，当达到SQL Server中设置的最大递归时，它将出错，或者如果最大递归设置为0，它将无限期运行直到终止。您可以使用以下命令设置最大递归选项：
OPTION (MAXRECURSION #) ，其中＃是0到32767之间的数字。如果未指定该选项，则默认值为100。
下面的所有示例均在SQL Server 2008 R2上创建和测试。
例如 1：从数量字段创建其他行此示例排成一行，并按数量重复数据多次。
样本数据：
item quantity
a 1
b 2
c 3
d 4
e 5DECLARE @t TABLE (item char(1), quantity int)
INSERT INTO @t VALUES ('a', 1), ('b', 2), ('c', 3), ('d', 4), ('e', 5)
; WITH cte AS (
SELECT item,
quantity - 1 AS quantityLeft
FROM @t
UNION ALL
SELECT item,
quantityLeft - 1 AS quantityLeft
FROM
cte
-- termination clause
WHERE quantityLeft > 0
)
SELECT * FROM cte ORDER BY item;item quantityLeft
a 0
b 1
b 0
c 1
c 0
c 2
d 3
d 2
d 1
d 0
e 4
e 3
e 2
e 1
e 0此示例获取数据集，并创建行的所有可能组合和排列。 请谨慎对待这一可能性，因为可能性的数量呈指数增长。
样本数据：
item
a
b
c
d
eDECLARE @t TABLE (item CHAR(1))
INSERT INTO @t VALUES ('a'), ('b'), ('c'), ('d'), ('e')
DECLARE @maxLen INT
SET @maxLen = (SELECT COUNT(*) FROM @t)
-- Combination, order does not matter
; WITH cte AS (
SELECT item,
CONVERT(VARCHAR(255), item) AS combined
FROM @t
UNION ALL
SELECT t.item,
CONVERT(VARCHAR(255), cte.combined + t.item) AS combined
FROM
cte
INNER JOIN @t t
ON cte.item < t.item
WHERE LEN(cte.combined + t.item) <= @maxLen
)
SELECT combined FROM cte ORDER BY LEN(combined), combined;
-- Permutation, order matters
; WITH cte AS (
SELECT item,
CONVERT(VARCHAR(255), item) AS combined
FROM @t
UNION ALL
SELECT t.item,
CONVERT(VARCHAR(255), cte.combined + t.item) AS combined
FROM
cte
INNER JOIN @t t
ON cte.combined NOT LIKE '%' + t.item + '%'
WHERE LEN(cte.combined + t.item) <= @maxLen
)
SELECT combined FROM cte ORDER BY LEN(combined), combined;combined
a
b
c
d
e
ab
ac
ad
ae
bc
bd
be
cd
ce
de
abc
abd
abe
acd
ace
ade
bcd
bce
bde
cde
abcd
abce
abde
acde
bcde
abcde本示例从字段中每个较大的PDF文件名以结尾的较大字符串中分出未知数量的PDF文件名
.pdf ，并开始在第一>符号前右.pdf和其他一切都是多余的。
样本数据：
IDField fieldName
1 >>>>>>1.pdf test> >b>c>xyz.pdf bob >hello world.pdf foo >womp womp.pdf>
2 >2.pdf other unnecssary stuff > bar.pdf注释掉的行是查找和子字符串化PDF文件名的基础。
DECLARE @f TABLE (fieldName VARCHAR(255), IDField int)
INSERT INTO @f VALUES('>>>>>>1.pdf test> >b>c>xyz.pdf bob >hello world.pdf foo >womp womp.pdf>', 1)
INSERT INTO @f VALUES('>2.pdf other unnecssary stuff > bar.pdf', 2)
; WITH cte2 AS (
SELECT
IDField,
--PATINDEX('%.pdf%', fieldName) + 3 AS PDFLocation,
--SUBSTRING(fieldName, 1, (PATINDEX('%.pdf%', fieldName) + 3)) AS PDFSubstring,
--REVERSE(SUBSTRING(fieldName, 1, (PATINDEX('%.pdf%', fieldName) + 3))) AS PDFSubstringReverse,
--PATINDEX('%>%', REVERSE(SUBSTRING(fieldName, 1, (PATINDEX('%.pdf%', fieldName) + 3)))) AS ReverseSymbolLocationBeforePDF,
--LEN(SUBSTRING(fieldName, 1, (PATINDEX('%.pdf%', fieldName) + 3))) - PATINDEX('%>%', REVERSE(SUBSTRING(fieldName, 1, (PATINDEX('%.pdf%', fieldName) + 3)))) + 2 AS SymbolLocationBeforePDF,
CONVERT(VARCHAR(255), SUBSTRING(fieldName,
LEN(SUBSTRING(fieldName, 1, (PATINDEX('%.pdf%', fieldName) + 3))) - PATINDEX('%>%', REVERSE(SUBSTRING(fieldName, 1, (PATINDEX('%.pdf%', fieldName) + 3)))) + 2,
PATINDEX('%.pdf%', fieldName) + 3 - (LEN(SUBSTRING(fieldName, 1, (PATINDEX('%.pdf%', fieldName) + 3))) - PATINDEX('%>%', REVERSE(SUBSTRING(fieldName, 1, (PATINDEX('%.pdf%', fieldName) + 3)))) + 2) + 1
)) AS PDFName,
CONVERT(VARCHAR(255), STUFF(fieldName, 1, PATINDEX('%.pdf%', fieldName) + 3, '')) AS strWhatsLeft
FROM @f
UNION ALL
SELECT
IDField,
--PATINDEX('%.pdf%', strWhatsLeft) + 3 AS PDFLocation,
--SUBSTRING(strWhatsLeft, 1, (PATINDEX('%.pdf%', strWhatsLeft) + 3)) AS PDFSubstring,
--REVERSE(SUBSTRING(strWhatsLeft, 1, (PATINDEX('%.pdf%', strWhatsLeft) + 3))) AS PDFSubstringReverse,
--PATINDEX('%>%', REVERSE(SUBSTRING(strWhatsLeft, 1, (PATINDEX('%.pdf%', strWhatsLeft) + 3)))) AS ReverseSymbolLocationBeforePDF,
--LEN(SUBSTRING(strWhatsLeft, 1, (PATINDEX('%.pdf%', strWhatsLeft) + 3))) - PATINDEX('%>%', REVERSE(SUBSTRING(strWhatsLeft, 1, (PATINDEX('%.pdf%', strWhatsLeft) + 3)))) + 2 AS SymbolLocationBeforePDF,
CONVERT(VARCHAR(255), SUBSTRING(strWhatsLeft,
LEN(SUBSTRING(strWhatsLeft, 1, (PATINDEX('%.pdf%', strWhatsLeft) + 3))) - PATINDEX('%>%', REVERSE(SUBSTRING(strWhatsLeft, 1, (PATINDEX('%.pdf%', strWhatsLeft) + 3)))) + 2,
PATINDEX('%.pdf%', strWhatsLeft) + 3 - (LEN(SUBSTRING(strWhatsLeft, 1, (PATINDEX('%.pdf%', strWhatsLeft) + 3))) - PATINDEX('%>%', REVERSE(SUBSTRING(strWhatsLeft, 1, (PATINDEX('%.pdf%', strWhatsLeft) + 3)))) + 2) + 1
)) AS PDFName,
CONVERT(VARCHAR(255), STUFF(strWhatsLeft, 1, PATINDEX('%.pdf%', strWhatsLeft) + 3, '')) AS strWhatsLeft
FROM cte2
WHERE strWhatsLeft LIKE '%.pdf%'
)
SELECT * FROM cte2 ORDER BY IDFieldIDField PDFName strWhatsLeft
1 1.pdf test> >b>c>xyz.pdf bob >hello world.pdf foo >womp womp.pdf>
1 xyz.pdf bob >hello world.pdf foo >womp womp.pdf>
1 hello world.pdf foo >womp womp.pdf>
1 womp womp.pdf >
2 2.pdf other unnecssary stuff > bar.pdf
2 bar.pdf 本示例使用定义开始日期和结束日期的行，并创建最大间隔为3天的多行。
样本数据：
TimespanID StartDate EndDate
1 2015-01-01 2015-01-02
2 2015-01-05 2015-02-11DECLARE @t TABLE (TimespanID int, StartDate DATE, EndDate DATE)
INSERT INTO @t VALUES (1,'1/1/2015','1/2/2015'), (2, '1/5/2015', '2/11/2015')
; WITH cte AS (
SELECT
TimespanID,
StartDate,
CASE WHEN DATEADD(DAY, 2, StartDate) > EndDate
THEN EndDate
ELSE DATEADD(DAY, 2, StartDate)
END AS EndDate,
EndDate AS OriginalEndDate
FROM @t
UNION ALL
SELECT
TimespanID,
DATEADD(DAY, 1, EndDate) AS StartDate,
CASE WHEN DATEADD(DAY, 3, EndDate) > OriginalEndDate
THEN OriginalEndDate
ELSE DATEADD(DAY, 3, EndDate)
END AS EndDate,
OriginalEndDate AS OriginalEndDate
FROM cte
WHERE DATEADD(DAY, 1, EndDate) <= OriginalEndDate
)
SELECT * FROM cte ORDER BY TimespanIDTimespanID StartDate EndDate OriginalEndDate
1 2015-01-01 2015-01-02 2015-01-02
2 2015-01-05 2015-01-07 2015-02-11
2 2015-01-08 2015-01-10 2015-02-11
2 2015-01-11 2015-01-13 2015-02-11
2 2015-01-14 2015-01-16 2015-02-11
2 2015-01-17 2015-01-19 2015-02-11
2 2015-01-20 2015-01-22 2015-02-11
2 2015-01-23 2015-01-25 2015-02-11
2 2015-01-26 2015-01-28 2015-02-11
2 2015-01-29 2015-01-31 2015-02-11
2 2015-02-01 2015-02-03 2015-02-11
2 2015-02-04 2015-02-06 2015-02-11
2 2015-02-07 2015-02-09 2015-02-11
2 2015-02-10 2015-02-11 2015-02-11本示例采用存储在表中的层次结构，其中ParentID是可用于建立层次结构级别的唯一链接。 通常，这是一个问题，因为您永远不知道员工处于什么级别，以及必须联接多少次才能检索表以检索整个层次结构。
但是，这对于递归CTE而言是微不足道的，因为它将持续不断地自我联接，直到建立完整的层次结构为止。
请注意，此示例与其他示例的不同之处在于，没有WHERE子句终止检查。 而是在达到最低层级时终止，这意味着没有低层员工可以加入，因为没有人向他们报告。 这也意味着，如果数据不正确，则层次结构中可能存在无限循环。
样本数据：
PK EmployeeName ParentID
1 Mr. CEO 0
2 Andy 1
3 Billy Jean 1
4 Charles 2
5 Danni 2
6 Eden 2
7 Frank 3
8 Geri 5DECLARE @t TABLE (PK INT, EmployeeName VARCHAR(10), ParentID INT);
INSERT INTO @t VALUES
(1, 'Mr. CEO', 0),
(2, 'Andy',1 ),
(3, 'Billy Jean', 1),
(4, 'Charles', 2),
(5, 'Danni', 2),
(6, 'Eden', 2),
(7, 'Frank', 3),
(8, 'Geri', 5)
;
; WITH cte AS (
SELECT
PK,
EmployeeName,
1 AS HierarchyLevel,
CONVERT(VARCHAR(255), PK) AS HierarchySortString
FROM @t
WHERE ParentID = 0
UNION ALL
SELECT
t.PK,
t.EmployeeName,
cte.HierarchyLevel + 1 AS HierarchyLevel,
CONVERT(VARCHAR(255), HierarchySortString + ',' + CONVERT(VARCHAR(255), t.PK)) AS HierarchySortString
FROM @t AS t
INNER JOIN cte
ON t.ParentID = cte.PK
)
SELECT
PK,
REPLICATE('+', HierarchyLevel - 1) + EmployeeName AS EmployeeName,
HierarchyLevel
FROM cte
ORDER BY
HierarchySortStringPK EmployeeName HierarchyLevel
1 Mr. CEO 1
2 +Andy 2
4 ++Charles 3
5 ++Danni 3
8 +++Geri 4
6 ++Eden 3
3 +Billy Jean 2
7 ++Frank 3翻译自: https://bytes.com/topic/sql-server/insights/965097-recursive-queries-sql-server
sql server 递归
扫一扫
9512
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
