sql server中常用表表达式的优点是什么

What is the advantage of common table expression in sql server

提问by Thomas

we write CTE sql like below one

我们像下面这样写CTE sql

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

what would be advantage to put sql in with block. i think that if we put complicated sql in with block then we just can write sql like SELECT * FROM yourCTE. as if i am accessing view. what is added advantage of using CTE in terms of performance. please discuss. thanks

将 sql 放入块中有什么好处。我认为，如果我们将复杂的 sql 放在块中，那么我们就可以像SELECT * FROM yourCTE. 好像我正在访问视图。在性能方面使用 CTE 的额外优势是什么。请讨论。谢谢

回答by marc_s

There are a number of cases where a CTE can be really useful:

在许多情况下，CTE 真的很有用：

• recursive queries, like walking up a hierarchy tree - that's extremely tricky and cumbersome without a CTE (see here for a sample of a recursive CTE)

• anytime you want to use one of the ranking functionslike ROW_NUMBER(), RANK(), NTILE()and so forth (see here for info on ranking functions)

• in general any case where you need to select a few rows/columns first, based on some criteria, and then do something with these, e.g. update a table, delete duplicates etc.

• 递归查询，就像沿着层次树走一样 - 如果没有 CTE，这是非常棘手和麻烦的（请参阅此处的递归 CTE 示例）

• 任何时候你要使用的一个排名函数像ROW_NUMBER()，RANK()，NTILE()等等（见此处查看详情上排名函数）

• 在一般情况下，您需要首先根据某些条件选择几行/几列，然后对这些进行处理，例如更新表格、删除重复项等。

One case I often use a CTE for is deleting all but the most recent row of a given set of data, e.g. if you have customers and an 1:n relationship to their orders, and you want to delete all but the most recent order (based on an OrderDate), for each customer, it gets quite hairy to do this in SQL without a CTE.

我经常使用 CTE 的一种情况是删除给定数据集中除最新行之外的所有内容，例如，如果您有客户并且与他们的订单存在 1:n 关系，并且您想删除除最新订单以外的所有订单（基于OrderDate)，对于每个客户，在没有 CTE 的 SQL 中执行此操作会非常麻烦。

With a CTE and the ranking functions, it's a breeze:

使用 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

With this, you create an "inline view" that partitions by CustomerID(e.g. each customer gets rownumbers starting at 1), order by OrderDate DESC(newest order first). For each customer, the newest, most recent order has RowN = 1, so you can easily just delete all other rows and you've done what you wanted to do - piece of cake with a CTE - messy code without it....

有了这个，您可以创建一个“内联视图”，该视图按以下方式分区CustomerID（例如，每个客户从 1 开始获得行号），按顺序OrderDate DESC（最新的订单在前）。对于每个客户，最新的、最近的订单都有RowN = 1，因此您可以轻松地删除所有其他行，并且您已经完成了您想做的事情 - 带有 CTE 的小菜一碟 - 没有它的混乱代码......

回答by FarligOpptreden

This MSDN articledescribes it the best. The bottom line is that, if you are already selecting the data from a view, you don't have to wrap it in a CTE and THEN select from the CTE. I don't think there's much difference (performance wise) between a CTE and a view. At least not in my experience (and I've been working with some complex database structures housing tons of records recently). A CTE is, however, ideal for recursive selects.

这篇 MSDN 文章对其进行了最好的描述。最重要的是，如果您已经从视图中选择数据，则不必将其包装在 CTE 中然后从 CTE 中选择。我认为 CTE 和视图之间没有太大区别（性能方面）。至少在我的经验中不是（而且我最近一直在处理一些包含大量记录的复杂数据库结构）。然而，CTE 是递归选择的理想选择。

Another thing, though, is that a CTE can be beneficial if you'd be selecting the same subset of joined data multiple times in your query/ies and DON'T have a view defined for it. I think it's overkill if you'll be joining data just for a single query and then wrapping it up in a CTE. The query path will still get cached even though you're not using a CTE...

但是，另一件事是，如果您要在查询中多次选择相同的联接数据子集并且没有为其定义视图，则 CTE 可能会有所帮助。我认为，如果您只是为单个查询加入数据，然后将其包装在 CTE 中，那就有点矫枉过正了。即使您没有使用 CTE，查询路径仍将被缓存...

回答by Raj Kumar

1. Making recursive query.
2. Hold a query output virtually in a temporary area named as given while definition.
3. No need to save Meta data.
4. Useful when there is need to do more operation on some query output.
5. Query output retain while till then query is running
6. Best use of holding temporary data for further processing.
7. Allow more grouping option than a single query.
8. Allow to get scalar data from a complicated query

1. 进行递归查询。
2. 将查询输出虚拟地保存在定义时命名的临时区域中。
3. 无需保存元数据。
4. 当需要对某些查询输出执行更多操作时很有用。
5. 查询输出保留直到查询运行
6. 最好使用保存临时数据以供进一步处理。
7. 允许比单个查询更多的分组选项。
8. 允许从复杂查询中获取标量数据

回答by Neeraj Kumar Yadav

Good evening friends..Today we are going to learn about Common table expression that is a new feature which was introduced in SQL server 2005 and available in later versions as well.

朋友们晚上好..今天我们将学习公共表表达式，它是 SQL Server 2005 中引入的一项新功能，并且在更高版本中也可用。

Common table Expression :- Common table expression can be defined as a temporary result set or in other words its a substitute of views in SQL Server. Common table expression is only valid in the batch of statement where it was defined and cannot be used in other sessions.

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

Syntax of declaring CTE(Common table expression) :-

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

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

Lets take an example :-

让我们举个例子：-

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')

I have created two tables employee and Dept and inserted 5 rows in each table. Now I would like to join these tables and create a temporary result set to use it further.

我创建了两个表员工和部门，并在每个表中插入了 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

Lets take each line of the statement one by one and understand.

让我们逐行阅读语句的每一行并理解。

To define CTE we write "with" clause, then we give a name to the table expression, here I have given name as "CTE_Example"

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

Then we write "As" and enclose our code in two brackets (---), we can join multiple tables in the enclosed brackets.

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

In the last line, I have used "Select * from CTE_Example" , we are referring the Common table expression in the last line of code, So we can say that Its like a view, where we are defining and using the view in a single batch and CTE is not stored in the database as a permanent object. But it behaves like a view. we can perform delete and update statement on CTE and that will have direct impact on the referenced table those are being used in CTE. Lets take an example to understand this fact.

在最后一行，我使用了 "Select * from CTE_Example" ，我们在最后一行代码中引用了 Common table 表达式，所以我们可以说它就像一个视图，我们在单个视图中定义和使用视图批处理和 CTE 不作为永久对象存储在数据库中。但它的行为就像一个视图。我们可以对 CTE 执行删除和更新语句，这将对 CTE 中使用的引用表产生直接影响。让我们举一个例子来理解这个事实。

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

In the above statement we are deleting a row from CTE_Example and it will delete the data from the referenced table "DEPT" that is being used in the CTE.

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

I hope this article will be helpful to you and you will be able to use CTE whenever you find it suitable.

我希望这篇文章对您有所帮助，并且您可以在任何合适的时候使用 CTE。