在SQL Server中，使用公共表达式的好处是什么？

Question

我们像下面这样编写CTE SQL：

WITH yourCTE AS 
(
 SELECT .... FROM :... WHERE.....
) SELECT * FROM yourCTE

在with块中放置SQL语句有什么优势？我认为，如果我们将复杂的SQL放在with块中，那么我们就可以像访问视图一样编写SQL，如SELECT * FROM yourCTE。

请讨论使用公共表表达式（CTE）在性能方面的附加优势。谢谢。

- Thomas

Answer 1

有许多情况下可以使用CTE:

• 递归查询，例如遍历层次树 - 如果没有CTE，则非常棘手和麻烦（请参见此处的递归CTE示例）

• 任何时候都想使用排名函数，如 ROW_NUMBER()， RANK()， NTILE()等 （请参见此处关于排名函数的信息）

• 通常情况下，需要先基于某些条件选择几行/列，然后对这些行/列执行某些操作，例如更新表、删除重复项等。

我经常使用CTE来删除给定数据集中除最近一行之外的所有行。例如，如果您有客户和与其订单相关的1:n关系，并且要删除每个客户的除最新订单以外的所有订单（基于OrderDate），则在SQL中执行此操作会变得相当麻烦。

有了CTE和排名函数，就很容易了:

;WITH CustomerOrders AS
(
    SELECT  
       c.CustomerID, o.OrderID,
       ROW_NUMBER() OVER(PARTITION BY c.CustomerID ORDER BY o.OrderDate DESC) AS 'RowN'
    FROM
       dbo.Customer c
    INNER JOIN
       dbo.Orders o ON o.CustomerID = c.CustomerID
)
DELETE FROM 
    dbo.Orders
FROM 
    CustomerOrders co
WHERE 
  dbo.Orders.OrderID = co.OrderID
  AND co.RowN > 1

使用这种方法，你创建了一个“内联视图”，并按照CustomerID进行分区(例如，每个客户都会得到以1开始的行号)，按照OrderDate DESC(最新订单在前)排序。对于每个客户，最新的、最近的订单具有RowN = 1，因此您可以轻松地删除所有其他行，这就完成了您想要的操作——使用CTE非常简单——没有它就变得混乱的代码....

- marc_s

---

当你简单地说“delete from CTE”，例如在你的例子中，关系型数据库管理系统如何知道它应该从哪个表中删除？ - Andriy M

@Andriy M：我更新了我的查询 - 这应该会更明显。 - marc_s

Answer 2

这篇MSDN文章讲得最好。简而言之，如果你已经从视图中选择数据，就不必将其包装在CTE中，然后再从CTE中选择。在我的经验中（我最近一直在使用一些复杂的数据库结构存储大量记录），CTE和视图在性能上没有太大区别。但是，CTE非常适合递归查询。

另一件事是，如果您需要多次查询相同的联接数据子集，并且没有为其定义视图，则CTE可能会有益处。如果您只是为单个查询连接数据，然后将其包装在CTE中，那么我认为这是过度的。即使您没有使用CTE，查询路径仍将被缓存...

- FarligOpptreden

---

你说CTE对于递归查询是理想的。你在说什么是递归查询？请用SQL让我明白CTE中的递归查询。 - Thomas

MSDN再次提供了一篇关于使用CTE进行递归查询的详细文章：http://msdn.microsoft.com/en-us/library/ms186243.aspx。此外，还可以查看马克的答案，其中包含一个非常简短（但易于理解）的递归查询解释。 - FarligOpptreden

Answer 3

1. 进行递归查询。
2. 将查询输出暂存在给定定义的临时区域中。
3. 无需保存元数据。
4. 在需要对某些查询输出执行更多操作时非常有用。
5. 查询输出保留，直到查询运行结束。
6. 将临时数据保留以便进行进一步处理的最佳用法。
7. 允许比单个查询更多的分组选项。
8. 允许从复杂的查询中获取标量数据。

- Raj Kumar

Answer 4

晚上好朋友们。今天我们将学习关于常用表达式的内容，这是SQL Server 2005中引入的新功能，并在后续版本中提供。

常用表达式：常用表达式可以被定义为临时结果集，或者换句话说，它是SQL Server中视图的替代品。常用表达式仅在定义它的语句批处理中有效，不能在其他会话中使用。

声明CTE（常用表达式）的语法：

with [Name of CTE]
as
(
Body of common table expression
)

让我们举个例子：

CREATE TABLE Employee([EID] [int] IDENTITY(10,5) NOT NULL,[Name] [varchar](50) NULL)

insert into Employee(Name) values('Neeraj')
insert into Employee(Name) values('dheeraj')
insert into Employee(Name) values('shayam')
insert into Employee(Name) values('vikas')
insert into Employee(Name) values('raj')

CREATE TABLE DEPT(EID INT,DEPTNAME VARCHAR(100))
insert into dept values(10,'IT')
insert into dept values(15,'Finance')
insert into dept values(20,'Admin')
insert into dept values(25,'HR')
insert into dept values(10,'Payroll')

我创建了两个表employee和Dept，并在每个表中插入了5行。现在我想要连接这些表并创建一个临时结果集以便进一步使用。

With CTE_Example(EID,Name,DeptName)
as
(
select Employee.EID,Name,DeptName from Employee 
inner join DEPT on Employee.EID =DEPT.EID
)
select * from CTE_Example

让我们逐行理解这个语句。

为了定义CTE，我们使用“with”子句，然后给表达式命名，这里我将其命名为“CTE_Example”。

然后我们写上“As”，并在两个括号（---）中包含我们的代码，我们可以在括号中连接多个表。

在最后一行，我使用了“Select * from CTE_Example”，我们在代码的最后一行引用了公共表达式，因此我们可以说它类似于视图，在单个批处理中定义和使用视图，而CTE不会作为永久对象存储在数据库中。但它的行为就像一个视图。我们可以对CTE执行删除和更新语句，并且这将直接影响在CTE中使用的引用表。让我们通过一个例子来理解这个事实。

With CTE_Example(EID,DeptName)
as
(
select EID,DeptName from DEPT 
)
delete from CTE_Example where EID=10 and DeptName ='Payroll'

在上述语句中，我们正在从CTE_Example中删除一行，并且将会删除在CTE中使用的引用表“DEPT”的数据。

我希望本文对您有所帮助，能让您在适当时候使用CTE。

- Neeraj Kumar Yadav

---

我知道这篇文章有点老了，但由于人们已经对这个答案进行了负评，所以我想加上一个原因。OP正在询问CTE会提供什么优势。这篇文章向我们展示了如何使用CTE，而不是为什么应该使用它。 - Semperfi89