SQL 何时使用公用表表达式 (CTE)

When to use Common Table Expression (CTE)

提问by imak

I have begun reading about Common Table Expressionand cannot think of a use case where I would need to use them. They would seem to be redundant as the same can be done with derived tables. Is there something I am missing or not understanding well? Can someone give me a simple example of limitations with regular select, derived or temp table queries to make the case of CTE? Any simple examples would be highly appreciated.

我已经开始阅读有关公共表表达式的内容，但想不出需要使用它们的用例。它们似乎是多余的，因为派生表也可以这样做。有什么我遗漏或不太理解的地方吗？有人能给我一个简单的例子来说明常规选择、派生或临时表查询的限制，以说明 CTE 的情况吗？任何简单的例子将不胜感激。

采纳答案by John Sansom

One example, if you need to reference/join the same data set multiple times you can do so by defining a CTE. Therefore, it can be a form of code re-use.

一个例子，如果你需要多次引用/加入同一个数据集，你可以通过定义一个 CTE 来实现。因此，它可以是一种代码重用的形式。

An example of self referencing is recursion: Recursive Queries Using CTE

自引用的一个例子是递归：使用 CTE 的递归查询

For exciting Microsoft definitions Taken from Books Online:

对于从在线书籍中获取的令人兴奋的 Microsoft 定义 ：

A CTE can be used to:

CTE 可用于：

• Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.

• Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.

• Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.

• Reference the resulting table multiple times in the same statement.

• 创建递归查询。有关更多信息，请参阅使用公用表表达式的递归查询。

• 当不需要视图的一般使用时替换视图；也就是说，您不必将定义存储在元数据中。

• 按源自标量子选择的列或不确定或具有外部访问权限的函数启用分组。

• 在同一语句中多次引用结果表。

回答by n8wrl

I use them to break up complex queries, especially complex joins and sub-queries. I find I'm using them more and more as 'pseudo-views' to help me get my head around the intent of the query.

我用它们来分解复杂的查询，尤其是复杂的连接和子查询。我发现我越来越多地将它们用作“伪视图”来帮助我了解查询的意图。

My only complaint about them is they cannot be re-used. For example, I may have a stored proc with two update statements that could use the same CTE. But the 'scope' of the CTE is the first query only.

我对它们唯一的抱怨是它们不能重复使用。例如，我可能有一个带有两个更新语句的存储过程，它们可以使用相同的 CTE。但是 CTE 的“范围”只是第一个查询。

Trouble is, 'simple examples' probably don't really need CTE's!

问题是，“简单示例”可能并不真正需要 CTE！

Still, very handy.

还是很方便的。

回答by BrianK

There are two reasons I see to use cte's.

我认为使用 cte 有两个原因。

To use a calculated value in the where clause. This seems a little cleaner to me than a derived table.

在 where 子句中使用计算值。这对我来说似乎比派生表更清晰。

Suppose there are two tables - Questions and Answers joined together by Questions.ID = Answers.Question_Id (and quiz id)

假设有两个表 - Questions 和 Answers 由 Questions.ID = Answers.Question_Id （和测验 ID）连接在一起

WITH CTE AS
(
    Select Question_Text,
           (SELECT Count(*) FROM Answers A WHERE A.Question_ID = Q.ID) AS Number_Of_Answers
    FROM Questions Q
)
SELECT * FROM CTE
WHERE Number_Of_Answers > 0

Here's another example where I want to get a list of questions and answers. I want the Answers to be grouped with the questions in the results.

这是我想获取问题和答案列表的另一个示例。我希望将答案与结果中的问题分组。

WITH cte AS
(
    SELECT [Quiz_ID] 
      ,[ID] AS Question_Id
      ,null AS Answer_Id
          ,[Question_Text]
          ,null AS Answer
          ,1 AS Is_Question
    FROM [Questions]

    UNION ALL

    SELECT Q.[Quiz_ID]
      ,[Question_ID]
      ,A.[ID] AS  Answer_Id
      ,Q.Question_Text
          ,[Answer]
          ,0 AS Is_Question
        FROM [Answers] A INNER JOIN [Questions] Q ON Q.Quiz_ID = A.Quiz_ID AND Q.Id = A.Question_Id
)
SELECT 
    Quiz_Id,
    Question_Id,
    Is_Question,
    (CASE WHEN Answer IS NULL THEN Question_Text ELSE Answer END) as Name
FROM cte    
GROUP BY Quiz_Id, Question_Id, Answer_id, Question_Text, Answer, Is_Question 
order by Quiz_Id, Question_Id, Is_Question Desc, Name

回答by TheDanMan

One of the scenarios I found useful to use CTE is when you want to get DISTINCT rows of data based on one or more columns but return all columns in the table. With a standard query you might first have to dump the distinct values into a temp table and then try to join them back to the original table to retrieve the rest of the columns or you might write an extremely complex partition query that can return the results in one run but in most likelihood, it will be unreadable and cause performance issue.

我发现使用 CTE 有用的场景之一是当您想要基于一列或多列获取 DISTINCT 数据行但返回表中的所有列时。对于标准查询，您可能首先必须将不同的值转储到临时表中，然后尝试将它们连接回原始表以检索其余列，或者您可能会编写一个极其复杂的分区查询，该查询可以在一次运行，但很可能是无法读取并导致性能问题。

But by using CTE (as answered by Tim Schmelter on Select the first instance of a record)

但是通过使用 CTE（正如 Tim Schmelter 在Select the first instance of a record上回答的那样）

WITH CTE AS(
    SELECT myTable.*
    , RN = ROW_NUMBER()OVER(PARTITION BY patientID ORDER BY ID)
    FROM myTable 
)
SELECT * FROM CTE
WHERE RN = 1

As you can see, this is much easier to read and maintain. And in comparison to other queries, is much better at performance.

如您所见，这更易于阅读和维护。与其他查询相比，性能要好得多。

回答by Vic

Perhaps its more meaningful to think of a CTE as a substitute for a view used for a single query. But doesn't require the overhead, metadata, or persistence of a formal view. Very useful when you need to:

也许将 CTE 视为用于单个查询的视图的替代更有意义。但不需要正式视图的开销、元数据或持久性。当您需要时非常有用：

• Create a recursive query.
• Use the CTE's resultset more than once in your query.
• Promote clarity in your query by reducing large chunks of identical subqueries.
• Enable grouping by a column derived in the CTE's resultset

• 创建递归查询。
• 在查询中多次使用 CTE 的结果集。
• 通过减少大量相同的子查询来提高查询的清晰度。
• 按 CTE 结果集中派生的列启用分组

Here's a cut-and-paste example to play with:

这是一个可以使用的剪切和粘贴示例：

WITH [cte_example] AS (
SELECT 1 AS [myNum], 'a num' as [label]
UNION ALL
SELECT [myNum]+1,[label]
FROM [cte_example]
WHERE [myNum] <=  10
)
SELECT * FROM [cte_example]
UNION
SELECT SUM([myNum]), 'sum_all' FROM [cte_example]
UNION
SELECT SUM([myNum]), 'sum_odd' FROM [cte_example] WHERE [myNum] % 2 = 1
UNION
SELECT SUM([myNum]), 'sum_even' FROM [cte_example] WHERE [myNum] % 2 = 0;

Enjoy

享受

回答by Neeraj Kumar Yadav

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 an 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”中的数据。

回答by bside

It is very useful when you want to perform an "ordered update".

当您想要执行“有序更新”时，它非常有用。

MS SQL does not allow you to use ORDER BY with UPDATE, but with help of CTE you can do it that way:

MS SQL 不允许您将 ORDER BY 与 UPDATE 一起使用，但在 CTE 的帮助下，您可以这样做：

WITH cte AS
(
    SELECT TOP(5000) message_compressed, message, exception_compressed, exception
    FROM logs
    WHERE Id >= 5519694 
    ORDER BY Id
)
UPDATE  cte
SET     message_compressed = COMPRESS(message), exception_compressed = COMPRESS(exception)

Look here for more info: How to update and order by using ms sql

在这里查看更多信息：How to update and order by using ms sql

回答by Oak_3260548

One point not pointed out yet, is the speed. I know it's an old answered question, but I think this deserves direct comment/answer:

还没有指出的一点是速度。我知道这是一个老问题，但我认为这值得直接评论/回答：

They would seem to be redundant as the same can be done with derived tables

它们似乎是多余的，因为派生表也可以这样做

When I used CTE the very first time I was absolutely stunned by it's speed. It was a case like from a textbook, very suitable for CTE, but in allocurences I ever used CTE, there was a significant speed gain. My first query was complex with derived tables, taking long minutes to execute. With CTE it took fractions of seconds and left me shocked, that it is even possible.

当我第一次使用 CTE 时，我完全被它的速度震惊了。这就像教科书上的案例，非常适合 CTE，但在我使用过 CTE 的所有场合中，速度都有显着的提升。我的第一个查询对于派生表很复杂，需要很长时间才能执行。使用 CTE 花了几秒钟的时间让我感到震惊，这甚至是可能的。

回答by Sudhir Panda

 ;with cte as
  (
  Select Department, Max(salary) as MaxSalary
  from test
  group by department
  )  
  select t.* from test t join cte c on c.department=t.department 
  where t.salary=c.MaxSalary;

try this

尝试这个