blocks|key|284946|text|我使用它们来分解复杂的查询，特别是复杂的连接和子查询。我发现我越来越多地使用它们作为“伪视图”来帮助我理解查询的意图。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|284947|我对它们唯一的抱怨是它们不能被重用。例如，我可能有一个存储的proc，其中包含两个可以使用相同CTE的update语句。但是CTE的“作用域”只是第一个查询。|284948|问题是，“简单的例子”可能并不真正需要CTE！|284949|不过，还是很方便的。|284950|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

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.

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

Still, very handy.

blocks|key|2996739|text|例如，如果您需要多次引用/联接相同的数据集，可以通过定义CTE来实现。因此，它可以是代码重用的一种形式。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2996740|自引用的一个例子是递归：Recursive+Queries+Using+CTE|offset|length|2996741|有关激动人心的Microsoft定义Taken+from+Books+Online:|2996742|CTE可用于：|2996743|2996744|创建递归查询。有关更多信息，请参见不需要常规使用视图时视图的Recursive+Queries+Using+Common+Table+Expressions.|unordered-list-item|2996745|Substitute；也就是说，您不必按从标量子选择派生的列或不确定的函数或在同一语句中多次对结果表进行外部分组的函数将定义存储在metadata.|2996746|Enable分组中。|2996747|2996748|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms186243.aspx|1|http://msdn.microsoft.com/en-us/library/ms190766.aspx|2^0|0|C|R|0|0|I|O|1|0|0|0|U|1D|2|0|0|0|0^^$0|@$1|2|3|4|5|6|7|13|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|14|8|@]|9|@$D|15|E|16|1|17]]|A|$]]|$1|F|3|G|5|6|7|18|8|@]|9|@$D|19|E|1A|1|1B]]|A|$]]|$1|H|3|I|5|6|7|1C|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|1D|8|@]|9|@]|A|$]]|$1|K|3|L|5|M|7|1E|8|@]|9|@$D|1F|E|1G|1|1H]]|A|$]]|$1|N|3|O|5|M|7|1I|8|@]|9|@]|A|$]]|$1|P|3|Q|5|M|7|1J|8|@]|9|@]|A|$]]|$1|R|3|-4|5|6|7|1K|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|1L|8|@]|9|@]|A|$]]]|T|$U|$5|V|W|X|A|$Y|Z]]|10|$5|V|W|X|A|$Y|11]]|12|$5|V|W|X|A|$Y|Z]]]]

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.

An example of self referencing is recursion: <a href="http://msdn.microsoft.com/en-us/library/ms186243.aspx" rel="noreferrer">Recursive Queries Using CTE</a>

For exciting Microsoft definitions
<a href="http://msdn.microsoft.com/en-us/library/ms190766.aspx" rel="noreferrer">Taken from Books Online:</a>

A CTE can be used to:

<ul>
<li>Create a recursive query. For more information, see <a href="http://msdn.microsoft.com/en-us/library/ms186243.aspx" rel="noreferrer">Recursive Queries Using Common Table Expressions.</a></li>
<li>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.</li>
<li>Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.</li>
<li>Reference the resulting table multiple times in the same statement.</li>
</ul>

blocks|key|3210017|text|我认为使用cte有两个原因。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3210018|在where子句中使用计算值。在我看来，这比派生表要干净一些。|3210019|假设有两个表-通过Questions.ID+=+Answers.Question_Id+(和测验id)连接在一起的问题和答案|3210020|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|code-block|syntax|javascript|3210021|这是我想要获取问题和答案列表的另一个示例。我希望答案与结果中的问题分组。|3210022|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|3210023|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|S|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|T|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|H|7|V|8|@]|9|@]|A|$I|J]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

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

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

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

<pre><code>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 &gt; 0
</code></pre>

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. 

<pre><code>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
</code></pre>

blocks|key|281492|text|今天，我们将学习Common+table+expression，这是SQL+server2005中引入的一个新功能，在以后的版本中也可以使用。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|281493|公用表表达式:-公用表表达式可以定义为临时结果集，或者换句话说，它是SQL+Server中视图的替代品。公用表表达式仅在定义它的语句批处理中有效，不能在其他会话中使用。|281494|声明CTE(公用表表达式)的语法:-|281495|with+[Name+of+CTE]
as
(
Body+of+common+table+expression
)|code-block|syntax|javascript|281496|让我们举个例子：|281497|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')|281498|我已经创建了两个表employee和Dept，并在每个表中插入了5行。现在，我想连接这些表，并创建一个临时结果集以进一步使用它。|281499|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|281500|让我们将语句中的每一行逐一阅读并理解。|281501|为了定义CTE，我们写下"with“子句，然后我们给表表达式一个名字，这里我给出的名字是"CTE_Example”。|281502|然后我们写"As“并将代码放在两个括号(-)中，我们可以在括号中连接多个表。|281503|在最后一行中，我使用了"Select+*+from+CTE_Example“，我们在最后一行代码中引用了常用表表达式，所以我们可以说它就像一个视图，我们在单个批处理中定义和使用视图，而不是将其作为永久对象存储在数据库中。但它的行为就像一个视图。我们可以在CTE上执行delete和update语句，这将直接影响CTE中正在使用的引用表。让我们举一个例子来理解这个事实。|281504|With+CTE_Example(EID,DeptName)
as
(
select+EID,DeptName+from+DEPT+
)
delete+from+CTE_Example+where+EID=10+and+DeptName+='Payroll'|281505|在上面的语句中，我们从CTE_Example中删除一行，它将从被引用的表"DEPT“中删除正在CTE中使用的数据。|281506|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|16|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|17|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|18|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|19|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|1A|8|@]|9|@]|A|$]]|$1|M|3|N|5|H|7|1B|8|@]|9|@]|A|$I|J]]|$1|O|3|P|5|6|7|1C|8|@]|9|@]|A|$]]|$1|Q|3|R|5|H|7|1D|8|@]|9|@]|A|$I|J]]|$1|S|3|T|5|6|7|1E|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|1F|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1G|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|1H|8|@]|9|@]|A|$]]|$1|10|3|11|5|H|7|1I|8|@]|9|@]|A|$I|J]]|$1|12|3|13|5|6|7|1J|8|@]|9|@]|A|$]]|$1|14|3|-4|5|6|7|1K|8|@]|9|@]|A|$]]]|15|$]]

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.

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.

Syntax of declaring CTE(Common table expression) :-

<pre><code>with [Name of CTE]
as
(
Body of common table expression
)
</code></pre>

Lets take an example :-

<pre><code>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')
</code></pre>

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.

<pre><code>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
</code></pre>

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"

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

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.

<pre><code>With CTE_Example(EID,DeptName)
as
(
select EID,DeptName from DEPT 
)
delete from CTE_Example where EID=10 and DeptName ='Payroll'
</code></pre>

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.

blocks|key|287344|text|我发现使用CTE很有用的场景之一是，您希望根据一个或多个列获得不同的数据行，但返回表中的所有列。对于标准查询，您可能首先必须将不同的值转储到临时表中，然后尝试将它们连接回原始表，以检索其余的列，或者您可能编写一个极其复杂的分区查询，该查询可以在一次运行中返回结果，但在大多数情况下，它将是不可读的，并导致性能问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|287345|而是通过使用CTE+(由Tim+Schmelter在Select+the+first+instance+of+a+record上回答)|offset|length|287346|WITH+CTE+AS(
++++SELECT+myTable.*
++++,+RN+=+ROW_NUMBER()OVER(PARTITION+BY+patientID+ORDER+BY+ID)
++++FROM+myTable+
)
SELECT+*+FROM+CTE
WHERE+RN+=+1|code-block|syntax|javascript|287347|正如您所看到的，这更易于阅读和维护。并且与其他查询相比，在性能上要好得多。|287348|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/11064473/select-the-first-instance-of-a-record^0|0|Q|11|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@$D|W|E|X|1|Y]]|A|$]]|$1|F|3|G|5|H|7|Z|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|10|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

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.

But by using CTE (as answered by Tim Schmelter on <a href="https://stackoverflow.com/questions/11064473/select-the-first-instance-of-a-record">Select the first instance of a record</a>)

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

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

blocks|key|285008|text|+;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;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|285009|尝尝这个|unstyled|285010|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code> ;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;
</code></pre>

try this

blocks|key|3210083|text|也许更有意义的是，将CTE视为用于单个查询的视图的替代品。但不需要正式视图的开销、元数据或持久性。当您需要执行以下操作时非常有用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3210084|3210085|创建递归查询。|unordered-list-item|3210086|在查询中多次使用CTE的结果集。|3210087|通过减少按从CTE的resultset|3210088|中派生的列进行的相同subqueries.|3210089|Enable分组的大块来提高查询的清晰度|3210090|3210091|下面是一个剪切粘贴的例子：|3210092|WITH+[cte_example]+AS+(
SELECT+1+AS+[myNum],+'a+num'+as+[label]
UNION+ALL
SELECT+[myNum]%2B1,[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]+%25+2+=+1
UNION
SELECT+SUM([myNum]),+'sum_even'+FROM+[cte_example]+WHERE+[myNum]+%25+2+=+0;|code-block|syntax|javascript|3210093|尽情享受|3210094|entityMap^0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Z|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|10|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|11|8|@]|9|@]|A|$]]|$1|F|3|G|5|E|7|12|8|@]|9|@]|A|$]]|$1|H|3|I|5|E|7|13|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|14|8|@]|9|@]|A|$]]|$1|L|3|M|5|E|7|15|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|16|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|17|8|@]|9|@]|A|$]]|$1|Q|3|R|5|S|7|18|8|@]|9|@]|A|$T|U]]|$1|V|3|W|5|6|7|19|8|@]|9|@]|A|$]]|$1|X|3|-4|5|6|7|1A|8|@]|9|@]|A|$]]]|Y|$]]

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:

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

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

<pre><code>WITH [cte_example] AS (
SELECT 1 AS [myNum], 'a num' as [label]
UNION ALL
SELECT [myNum]+1,[label]
FROM [cte_example]
WHERE [myNum] &lt;= 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;
</code></pre>

Enjoy

blocks|key|2996954|text|当您想要执行“有序更新”时，它非常有用。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2996955|MS+SQL不允许您使用ORDER+BY+with+UPDATE，但在CTE的帮助下，您可以这样做：|2996956|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)|code-block|syntax|javascript|2996957|点击这里获取更多信息：How+to+update+and+order+by+using+ms+sql|offset|length|2996958|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/655010/how-to-update-and-order-by-using-ms-sql^0|0|0|0|B|13|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|W|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|X|8|@]|9|@$K|Y|L|Z|1|10]]|A|$]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

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:

<pre><code>WITH cte AS
(
 SELECT TOP(5000) message_compressed, message, exception_compressed, exception
 FROM logs
 WHERE Id &gt;= 5519694 
 ORDER BY Id
)
UPDATE cte
SET message_compressed = COMPRESS(message), exception_compressed = COMPRESS(exception)
</code></pre>

Look here for more info: <a href="https://stackoverflow.com/questions/655010/how-to-update-and-order-by-using-ms-sql">How to update and order by using ms sql</a>

blocks|key|281639|text|有一点还没有指出，那就是速度。我知道这是一个老生常谈的问题，但我认为这值得直接评论/回答：|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|281640|281641|它们似乎是多余的，因为派生表也可以做到这一点|blockquote|281642|281643|281644|当我第一次使用CTE时，我绝对被它的速度震惊了。这是一个教科书中的案例，非常适合CTE，但在我使用过的所有情况下，CTE都有显著的速度提升。我的第一个查询是使用派生表的复杂查询，需要很长时间才能执行。对于CTE，它只花了几秒钟的时间，让我震惊，这甚至是可能的。|281645|entityMap^0|C|2|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@$9|Q|A|R|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|S|8|@]|D|@]|E|$]]|$1|G|3|H|5|I|7|T|8|@]|D|@]|E|$]]|$1|J|3|-4|5|6|7|U|8|@]|D|@]|E|$]]|$1|K|3|-4|5|6|7|V|8|@]|D|@]|E|$]]|$1|L|3|M|5|6|7|W|8|@]|D|@]|E|$]]|$1|N|3|-4|5|6|7|X|8|@]|D|@]|E|$]]]|O|$]]

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:

<blockquote>
 They would seem to be redundant as the same can be done with derived tables
</blockquote>

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 all ocurences 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.

I have begun reading about Common Table Expression and 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.

When to use Common Table Expression (CTE)

对象存储

SQL Server

数据库

我已经开始阅读有关常用表表达式的内容，但我想不出有哪种用例需要使用它们。它们似乎是多余的，因为派生表也可以做同样的事情。我是不是遗漏了什么或者理解得不够透彻？谁能给我举一个简单的例子，说明使用常规的select、派生或临时表查询来实现CTE的限制？任何简单的例子都会受到高度赞赏。

问何时使用通用表表达式(CTE)
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问何时使用通用表表达式(CTE)EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问何时使用通用表表达式(CTE)
EN