我已经开始阅读关于公共表表达式的文章，但我想不出有什么用例需要使用它们。它们似乎是多余的，因为派生表也可以这样做。是我忽略了什么还是没有理解好?谁能给我一个简单的例子，限制与常规选择，衍生或临时表查询，使CTE的情况?任何简单的例子将高度赞赏。
当前回答
当您希望执行“有序更新”时，它非常有用。
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)
看这里更多的信息:如何更新和使用ms sql
其他回答
我使用它们来分解复杂的查询，特别是复杂的连接和子查询。我发现我越来越多地使用它们作为“伪视图”来帮助我理解查询的意图。
我唯一抱怨的是它们不能重复使用。例如，我可能有一个带有两个更新语句的存储过程，它们可以使用相同的CTE。但是CTE的“范围”只是第一个查询。
问题是，“简单的例子”可能真的不需要CTE !
不过，还是很方便。
当您希望执行“有序更新”时，它非常有用。
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)
看这里更多的信息:如何更新和使用ms sql
还有一点没有指出，就是速度。我知道这是一个老问题，但我认为这值得直接评论/回答:
它们似乎是多余的，因为派生表也可以这样做
当我第一次使用CTE时，我完全被它的速度惊呆了。这是教科书上的案例，非常适合CTE，但在我使用CTE的所有情况下，都有显著的速度增益。我的第一个查询很复杂，涉及派生表，执行时间很长。对于CTE，它只需要几秒钟，让我震惊，这甚至是可能的。
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(由Tim Schmelter在选择记录的第一个实例中回答)
WITH CTE AS(
SELECT myTable.*
, RN = ROW_NUMBER()OVER(PARTITION BY patientID ORDER BY ID)
FROM myTable
)
SELECT * FROM CTE
WHERE RN = 1
如您所见，这更容易阅读和维护。与其他查询相比，它的性能要好得多。
;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;
试试这个
