关于SQL语句中With的⽤法
创建和使⽤ CTE 的指南
下列指南应⽤于⾮递归 CTE。有关适⽤于递归 CTE 的指南，请参阅后⾯的“定义和使⽤递归 CTE 的指南”。
CTE 之后必须跟随引⽤部分或全部 CTE 列的 SELECT、INSERT、UPDATE 或 DELETE 语句。也可以在 CREATE VIEW 语句中将CTE 指定为视图中 SELECT 定义语句的⼀部分。
可以在⾮递归 CTE 中定义多个 CTE 查询定义。定义必须与以下集合运算符之⼀结合使⽤：UNION ALL、UNION、INTERSECT 或EXCEPT。
CTE 可以引⽤⾃⾝，也可以引⽤在同⼀  ⼦句中预先定义的 CTE。不允许前向引⽤。
不允许在⼀个 CTE 中指定多个  ⼦句。例如，如果 CTE_query_definition 包含⼀个⼦查询，则该⼦查询不能包括定义另⼀个CTE 的嵌套的  ⼦句。
不能在 CTE_query_definition 中使⽤以下⼦句：
COMPUTE 或 COMPUTE BY
ORDER BY（除⾮指定了 TOP ⼦句）
INTO
带有查询提⽰的 OPTION ⼦句
FOR XML
FOR BROWSE
如果将 CTE ⽤在属于批处理的⼀部分的语句中，那么在它之前的语句必须以分号结尾。
可以使⽤引⽤ CTE 的查询来定义游标。
可以在 CTE 中引⽤远程服务器中的表。
在执⾏ CTE 时，任何引⽤ CTE 的提⽰都可能与该 CTE 访问其基础表时发现的其他提⽰相冲突，这种冲突与引⽤查询中的视图的提⽰所发⽣的冲突相同。发⽣这种情况时，查询将返回错误。有关详细信息，请参阅视图解析。
定义和使⽤递归 CTE 指南
下列指南适⽤于定义递归 CTE 的情况：
递归 CTE 定义⾄少必须包含两个 CTE 查询定义，⼀个定位点成员和⼀个递归成员。可以定义多个定位点成员和递归成员；但必须将所有定位点成员查询定义置于第⼀个递归成员定义之前。所有 CTE 查询定义都是定位点成员，但它们引⽤ CTE 本⾝时除外。
定位点成员必须与以下集合运算符之⼀结合使⽤：UNION ALL、UNION、INTERSECT 或 EXCEPT。在最后⼀个定位点成员和第⼀个递归成员之间，以及组合多个递归成员时，只能使⽤ UNION ALL 集合运算符。
定位点成员和递归成员中的列数必须⼀致。
递归成员中列的数据类型必须与定位点成员中相应列的数据类型⼀致。
递归成员的 FROM ⼦句只能引⽤⼀次 CTE expression_name。
在递归成员的 CTE_query_definition 中不允许出现下列项：
SELECT DISTINCT
GROUP BY
HAVING
标量聚合
TOP
LEFT、RIGHT、OUTER JOIN（允许出现 INNER JOIN）
⼦查询
应⽤于对 CTE_query_definition 中的 CTE 的递归引⽤的提⽰。
下列指南适⽤于使⽤递归 CTE：
⽆论参与的 SELECT 语句返回的列的为空性如何，递归 CTE 返回的全部列都可以为空。
如果递归 CTE 组合不正确，可能会导致⽆限循环。例如，如果递归成员查询定义对⽗列和⼦列返回相同的值，则会造成⽆限循环。可以使⽤ MAXRECURSION 提⽰以及在 INSERT、UPDATE、DELETE 或 SELECT 语句的 OPTION ⼦句中的⼀个 0 到 32,767 之间的值，来限制特定语句所允许的递归级数，以防⽌出现⽆限循环。这样就能够在解决产⽣循环的代码问题之前控制语句的执⾏。服务器范围
内的默认值是 100。如果指定 0，则没有限制。每⼀个语句只能指定⼀个 MAXRECURSION 值。有关详细信息，请参阅。
不能使⽤包含递归公⽤表表达式的视图来更新数据。
可以使⽤ CTE 在查询上定义游标。递归 CTE 只允许使⽤快速只进游标和静态（快照）游标。如果在递归 CTE 中指定了其他游标类型，则该类型将转换为静态游标类型。
可以在 CTE 中引⽤远程服务器中的表。如果在 CTE 的递归成员中引⽤了远程服务器，那么将为每个远程表创建⼀个假脱机，这样就可以在本地反复访问这些表。
参数
expression_name
公⽤表表达式的有效标识符。 expression_name 必须与在同⼀  <common_table_expression> ⼦句中定义的任何其
他公⽤表表达式的名称不同，但 expression_name 可以与基表或基视图的名称相同。在查询中对 expression_name 的任何引⽤都会使⽤公⽤表表达式，⽽不使⽤基对象。
column_name
在公⽤表表达式中指定列名。在⼀个 CTE 定义中不允许出现重复的名称。指定的列名数必须与 CTE_query_definition 结果集中列数匹配。只有在查询定义中为所有结果列都提供了不同的名称时，列名称列表才是可选的。
CTE_query_definition
指定⼀个其结果集填充公⽤表表达式的 SELECT 语句。除了 CTE 不能定义另⼀个 CTE 以外，CTE_query_definition 的
SELECT 语句必须满⾜与创建视图时相同的要求。有关详细信息，请参阅“备注”部分和 。
如果定义了多个 CTE_query_definition，则这些查询定义必须⽤下列⼀个集合运算符联接起来：UNION ALL、UNION、
EXCEPT 或 INTERSECT。有关使⽤递归 CTE 查询定义的详细信息，请参阅下列“备注”部分和使⽤公⽤表表达式的递归查
询。
⽰例
A. 创建⼀个简单公⽤表表达式
以下⽰例显⽰直接向 Adventure Works Cycles 的每个经理报告的雇员的数⽬。
复制代码
USE AdventureWorks;
GO
DirReps(ManagerID, DirectReports) AS
(
SELECT ManagerID, COUNT(*)
FROM HumanResources.Employee AS e
WHERE ManagerID IS NOT NULL
GROUP BY ManagerID
)
SELECT ManagerID, DirectReports
FROM DirReps
ORDER BY ManagerID;
GO
B. 使⽤公⽤表表达式来限制次数和报告平均数
以下⽰例显⽰向经理报告的雇员的平均数。
复制代码
DirReps (Manager, DirectReports) AS
(
SELECT ManagerID, COUNT(*) AS DirectReports
FROM HumanResources.Employee
GROUP BY ManagerID
)
SELECT AVG(DirectReports) AS [Average Number of Direct Reports]
FROM DirReps
WHERE DirectReports>= 2 ;
GO
C. 多次引⽤同⼀个公⽤表表达式
以下⽰例显⽰ SalesOrderHeader 表中每个销售⼈员的销售订单的总数和最近的销售订单的⽇期。CTE 在运⾏的语句中被引⽤两次：⼀次返回为销售⼈员所选的列，另⼀次检索销售经理的类似详细信息。销售⼈员和销售经理的数据都返回在⼀⾏中。
复制代码
USE AdventureWorks;
GO
Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID
)
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
JOIN Sales_CTE AS OS
sql中delete用法ON E.EmployeeID = OS.SalesPersonID
LEFT OUTER JOIN Sales_CTE AS OM
ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID;
GO
使⽤递归公⽤表表达式显⽰递归的多个级别。
以下⽰例显⽰经理以及向经理报告的雇员的层次列表。
复制代码
USE AdventureWorks;
GO
DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS (
SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
FROM HumanResources.Employee e
INNER JOIN DirectReports d
ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, EmployeeLevel
FROM DirectReports ;
GO
E. 使⽤递归公⽤表表达式显⽰递归的两个级别。
以下⽰例显⽰经理以及向经理报告的雇员。将返回的级别数⽬被限制为两个。
复制代码
USE AdventureWorks;
GO
DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS (
SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
FROM HumanResources.Employee e
INNER JOIN DirectReports d
ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, EmployeeLevel
FROM DirectReports
WHERE EmployeeLevel <= 2 ;
GO
F. 使⽤递归公⽤表表达式显⽰层次列表
以下⽰例在⽰例 C 的基础上添加经理和雇员的名称，以及他们各⾃的头衔。通过缩进各个级别，突出显⽰经理和雇员的层次结构。
复制代码
USE AdventureWorks;
GO
DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)
AS (SELECT CONVERT(varchar(255), c.FirstName + ' ' + c.LastName),
e.Title,
e.EmployeeID,
1,
CONVERT(varchar(255), c.FirstName + ' ' + c.LastName)
FROM HumanResources.Employee AS e
JOIN Person.Contact AS c ON e.ContactID = c.ContactID
WHERE e.ManagerID IS NULL
UNION ALL
SELECT CONVERT(varchar(255), REPLICATE ('| ' , EmployeeLevel) +
c.FirstName + ' ' + c.LastName),
e.Title,
e.EmployeeID,
EmployeeLevel + 1,
CONVERT (varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' +
LastName)
FROM HumanResources.Employee as e
JOIN Person.Contact AS c ON e.ContactID = c.ContactID
JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM DirectReports
ORDER BY Sort;
GO
G. 使⽤ MAXRECURSION 取消⼀条语句
可以使⽤ MAXRECURSION 来防⽌不合理的递归 CTE 进⼊⽆限循环。以下⽰例有意创建了⼀个⽆限循环，然后使⽤ MAXRECURSION 提⽰来将递归级别限制为两个。
复制代码
USE AdventureWorks;
GO
--Creates an infinite loop
cte (EmployeeID, ManagerID, Title) as
(
SELECT EmployeeID, ManagerID, Title
FROM HumanResources.Employee
WHERE ManagerID IS NOT NULL
UNION ALL
SELECT cte.EmployeeID, cte.ManagerID, cte.Title
FROM cte
JOIN  HumanResources.Employee AS e
ON cte.ManagerID = e.EmployeeID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT EmployeeID, ManagerID, Title
FROM cte
OPTION (MAXRECURSION 2);
GO

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。