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

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

激动人心的微软定义 摘自Books Online:

CTE可用于:

创建递归查询。有关更多信息，请参见使用公共表表达式的递归查询。 当不需要一般使用视图时，替换为视图;也就是说，您不必将定义存储在元数据中。 根据从标量子选择派生的列或非确定性或具有外部访问权限的函数启用分组。 在同一语句中多次引用结果表。

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

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

激动人心的微软定义 摘自Books Online:

CTE可用于:

创建递归查询。有关更多信息，请参见使用公共表表达式的递归查询。 当不需要一般使用视图时，替换为视图;也就是说，您不必将定义存储在元数据中。 根据从标量子选择派生的列或非确定性或具有外部访问权限的函数启用分组。 在同一语句中多次引用结果表。

今天我们将学习通用表表达式，这是SQL server 2005中引入的新特性，在后续版本中也可以使用。

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

CTE(Common table expression)声明语法:-

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上执行delete和update语句，这将对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”中删除数据。

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;

试试这个

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

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