我开始阅读关于通用表达式的内容，但是想不到我需要使用它们的用例。由于可以使用派生表执行相同的操作，因此它们似乎是多余的。我是否遗漏了什么或者没有理解好？有人可以举一个简单的例子来说明使用常规选择、派生表或临时表查询的限制，以证明使用通用表达式的必要性吗？非常感谢任何简单的例子。
9个回答
219
举个例子，如果你需要多次引用/连接同一数据集，可以通过定义CTE来实现。因此，它可以是代码重用的一种形式。
自引用的一个例子是递归: 使用CTE进行递归查询
有关激动人心的Microsoft定义 摘自图书在线:
CTE可用于：
创建递归查询。有关详细信息，请参见使用公共表达式的递归查询。
当不需要通常使用视图时，替换视图；也就是说，您不必将定义存储在元数据中。
使从标量子选择或具有外部访问或非确定性函数派生的列可以分组。
在同一语句中多次引用结果表。
- John Sansom
5
60
我使用它们来拆分复杂的查询，尤其是复杂的连接和子查询。我发现自己越来越多地将它们用作“伪视图”，以帮助我理解查询的意图。
我唯一的抱怨是它们不能被重复使用。例如，我可能有一个存储过程，其中包含两个更新语句，可以使用相同的CTE。但是，CTE的“范围”仅限于第一个查询。
问题是，“简单的示例”可能并不真正需要CTE！
尽管如此，非常方便。
- n8wrl
4
可以举一个相对复杂的例子来帮助我理解这个概念吗？ - imak
35我唯一的抱怨是它们无法重复使用。如果您想要重复使用一个CTE，应该考虑将其作为“VIEW”的候选项 :) - onedaywhen
7我理解了，但这意味着涉及到一个全局范围，而我并不总是感到舒适。有时在存储过程的范围内，我想定义一个公共表达式，然后在查询和更新中使用它，或者从不同的表中选择类似数据的查询。 - n8wrl
6当我需要重复使用同一个CTE时，我会将其输入到一个临时表中，然后随意多次使用这个临时表。 - Fandango68
49
我看到使用CTE的两个原因。
第一个原因是在where子句中使用计算值。对我来说，这似乎比派生表更加简洁。
假设有两个表 - 问题和答案通过Questions.ID = Answers.Question_Id (和quiz 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
这里是另一个例子，我想获取一组问题和答案的列表。我希望在结果中将答案与问题分组。
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
- BrianK
4
10第一个示例是否可以简化为只使用嵌套查询而不是CTE？ - Sam
2两个例子都可以。 - Manachi
3你本应该先添加第一个没有CTE的版本，这样就能立即看出后者的用处所在。 - Ufos
HAVING is another way to do a late-stage filter which can be similar to using a sub-SELECT - William Entriken27
我发现使用CTE很有用的一个场景是，当你想基于一个或多个列获取不重复的行数据，但是还要返回表中的所有列。使用标准查询时，你可能需要先将不同的值转储到临时表中，然后尝试将它们加入回原始表中以检索其余的列，或者编写一个非常复杂的分区查询来在一次运行中返回结果。但是，通过使用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
正如您所看到的，这篇文章更易于阅读和维护。与其他查询相比，在性能方面更加出色。
- TheDanMan
23
或许更有意义的是将CTE视为单个查询使用的视图替代品，但不需要正式视图的开销、元数据或持久性。当您需要执行以下操作时非常有用：
- 创建递归查询。
- 在查询中多次使用CTE的结果集。
- 通过减少大量相同子查询来提高查询的清晰度。
- 启用以CTE的结果集派生的列进行分组。
这里是一个可供使用的复制和粘贴示例:
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;
享受
- Vic Colborn
8
当你想执行“有序更新”操作时，这非常实用。
MS SQL不允许在UPDATE语句中使用ORDER BY，但是通过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)
点击这里查看更多信息：如何使用MS SQL更新和排序
- bside
8
今天我们要学习的是通用表达式，这是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”的数据。
- Neeraj Kumar Yadav
2
我还是不明白重点在哪里。这样做和使用完全相同的条件从DEPT中删除有什么区别？它似乎并没有使任何事情变得更容易。 - Holger Jakobs
请纠正我如果我错了，但执行计划可能是不同的，我认为这是Neeraj的观点，即有许多方法可以实现相同的目标，但某些方法在特定情况下会比其他方法更具优势。例如，在某些情况下，阅读CTE可能比DELETE FROM语句更容易，而在其他情况下则可能相反。性能可能会提高或恶化等。 - WonderWorker
3
还有一点没有被指出，那就是速度。我知道这是一个早已被回答的问题，但我认为这值得直接评论/回答：
它们似乎是多余的，因为使用派生表可以完成同样的工作
当我第一次使用CTE时，我对其速度感到惊讶。这是一个非常适合使用CTE的案例，但在我所使用CTE的所有情况中，都有明显的速度提升。我的第一个查询非常复杂，使用派生表需要花费长时间才能执行。而使用CTE只需几分之一秒，让我感到震惊，甚至觉得这是不可能的。
- Oak_3260548
-3
;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;
试一下这个
- Sudhir Panda
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接
select top 100 * into #tmp from master..spt_values order by 1,2,3,4 select A.number, COUNT(*) from #tmp A inner join #tmp B ON A.number = B.number+1 group by A.numberwith CTE AS (select top 100 * from master..spt_values order by 1,2,3,4) select A.number, COUNT(*) from CTE A inner join CTE B ON A.number = B.number+1 group by A.number注意：本翻译只涉及技术术语和语言转换，不对原文内容进行解释或更改。 - RichardTheKiwi