WITH common_table_expression (Transact-SQL)

1.定义

       指定一个临时命名结果集，称为公用表表达式 (CTE)。这源自一个简单的查询，并在单个 SELECT、INSERT、UPDATE 或 DELETE 语句的执行范围内定义。该子句也可以在 CREATE VIEW 语句中用作其定义的 SELECT 语句的一部分。公共表表达式可以包含对自身的引用。这称为递归公用表表达式。

2.语法

with expression_name (column_name [ ,...n ])  

as

(

     CTE_query_definition

)

expression_name:是公用表表达式的有效标识符。expression_name 必须不同于在同一 WITH <common_table_expression> 子句中定义的任何其他公用表表达式的名称，但 expression_name 可以与基表或视图的名称相同。查询中对 expression_name 的任何引用都使用公用表表达式，而不是基础对象。简单来说，expression_name就相当于as后面语句的一个临时存储集

column_name：指定公用表表达式中的列名。不允许在单个 CTE 定义中出现重复名称。指定的列名数必须与 CTE_query_definition 的结果集中的列数匹配。仅当查询定义中提供了所有结果列的不同名称时，列名列表才是可选的。列名列表可以不写

CTE_query_definition：指定其结果集填充公用表表达式的 SELECT 语句。CTE_query_definition 的 SELECT 语句必须满足与创建视图相同的要求，但 CTE 不能定义另一个 CTE。如果定义了多个 CTE_query_definition，则查询定义必须由以下集合运算符之一连接：UNION ALL、UNION、EXCEPT 或 INTERSECT。

3.基本使用案例

建表和添加数据

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [SalesOrderHeader](
    [SalesOrderID] [int] IDENTITY(1,1) NOT NULL,
    [SalesPersonID] [int] NOT NULL,
    [OrderDate] [date] NULL,
    [TotalDue] [decimal](9, 2) NULL,
PRIMARY KEY CLUSTERED 
(
    [SalesOrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [SalesPersonQuotaHistory](
    [BusinessEntityID] [int] NOT NULL,
    [SalesQuota] [decimal](11, 2) NULL,
    [QuotaDate] [date] NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [SalesOrderHeader] ON 

INSERT [SalesOrderHeader] ([SalesOrderID], [SalesPersonID], [OrderDate], [TotalDue]) VALUES (1, 2, CAST(N'2020-01-10' AS Date), CAST(500.00 AS Decimal(9, 2)))
INSERT [SalesOrderHeader] ([SalesOrderID], [SalesPersonID], [OrderDate], [TotalDue]) VALUES (2, 3, CAST(N'2020-01-15' AS Date), CAST(300.00 AS Decimal(9, 2)))
INSERT [SalesOrderHeader] ([SalesOrderID], [SalesPersonID], [OrderDate], [TotalDue]) VALUES (3, 1, CAST(N'2020-02-01' AS Date), CAST(800.00 AS Decimal(9, 2)))
INSERT [SalesOrderHeader] ([SalesOrderID], [SalesPersonID], [OrderDate], [TotalDue]) VALUES (4, 4, CAST(N'2020-02-20' AS Date), CAST(1000.00 AS Decimal(9, 2)))
INSERT [SalesOrderHeader] ([SalesOrderID], [SalesPersonID], [OrderDate], [TotalDue]) VALUES (5, 2, CAST(N'2020-03-01' AS Date), CAST(500.00 AS Decimal(9, 2)))
INSERT [SalesOrderHeader] ([SalesOrderID], [SalesPersonID], [OrderDate], [TotalDue]) VALUES (6, 1, CAST(N'2020-03-10' AS Date), CAST(600.00 AS Decimal(9, 2)))
INSERT [SalesOrderHeader] ([SalesOrderID], [SalesPersonID], [OrderDate], [TotalDue]) VALUES (7, 3, CAST(N'2020-04-01' AS Date), CAST(700.00 AS Decimal(9, 2)))
INSERT [SalesOrderHeader] ([SalesOrderID], [SalesPersonID], [OrderDate], [TotalDue]) VALUES (8, 5, CAST(N'2020-04-12' AS Date), CAST(900.00 AS Decimal(9, 2)))
INSERT [SalesOrderHeader] ([SalesOrderID], [SalesPersonID], [OrderDate], [TotalDue]) VALUES (9, 3, CAST(N'2020-05-03' AS Date), CAST(800.00 AS Decimal(9, 2)))
INSERT [SalesOrderHeader] ([SalesOrderID], [SalesPersonID], [OrderDate], [TotalDue]) VALUES (10, 1, CAST(N'2020-05-10' AS Date), CAST(1000.00 AS Decimal(9, 2)))
INSERT [SalesOrderHeader] ([SalesOrderID], [SalesPersonID], [OrderDate], [TotalDue]) VALUES (11, 3, CAST(N'2020-06-10' AS Date), CAST(800.00 AS Decimal(9, 2)))
INSERT [SalesOrderHeader] ([SalesOrderID], [SalesPersonID], [OrderDate], [TotalDue]) VALUES (12, 3, CAST(N'2021-01-20' AS Date), CAST(900.00 AS Decimal(9, 2)))
INSERT [SalesOrderHeader] ([SalesOrderID], [SalesPersonID], [OrderDate], [TotalDue]) VALUES (13, 2, CAST(N'2021-02-01' AS Date), CAST(1000.00 AS Decimal(9, 2)))
INSERT [SalesOrderHeader] ([SalesOrderID], [SalesPersonID], [OrderDate], [TotalDue]) VALUES (14, 5, CAST(N'2021-03-10' AS Date), CAST(300.00 AS Decimal(9, 2)))
INSERT [SalesOrderHeader] ([SalesOrderID], [SalesPersonID], [OrderDate], [TotalDue]) VALUES (15, 3, CAST(N'2021-04-20' AS Date), CAST(2000.00 AS Decimal(9, 2)))
INSERT [SalesOrderHeader] ([SalesOrderID], [SalesPersonID], [OrderDate], [TotalDue]) VALUES (16, 1, CAST(N'2021-04-05' AS Date), CAST(900.00 AS Decimal(9, 2)))
INSERT [SalesOrderHeader] ([SalesOrderID], [SalesPersonID], [OrderDate], [TotalDue]) VALUES (17, 3, CAST(N'2021-05-15' AS Date), CAST(10000.00 AS Decimal(9, 2)))
INSERT [SalesOrderHeader] ([SalesOrderID], [SalesPersonID], [OrderDate], [TotalDue]) VALUES (18, 4, CAST(N'2021-06-03' AS Date), CAST(3000.00 AS Decimal(9, 2)))
INSERT [SalesOrderHeader] ([SalesOrderID], [SalesPersonID], [OrderDate], [TotalDue]) VALUES (19, 3, CAST(N'2021-07-10' AS Date), CAST(4000.00 AS Decimal(9, 2)))
INSERT [SalesOrderHeader] ([SalesOrderID], [SalesPersonID], [OrderDate], [TotalDue]) VALUES (20, 2, CAST(N'2021-08-05' AS Date), CAST(5000.00 AS Decimal(9, 2)))
SET IDENTITY_INSERT [SalesOrderHeader] OFF
INSERT [SalesPersonQuotaHistory] ([BusinessEntityID], [SalesQuota], [QuotaDate]) VALUES (1, CAST(2000.00 AS Decimal(11, 2)), CAST(N'2020-03-02' AS Date))
INSERT [SalesPersonQuotaHistory] ([BusinessEntityID], [SalesQuota], [QuotaDate]) VALUES (2, CAST(3000.00 AS Decimal(11, 2)), CAST(N'2020-04-10' AS Date))
INSERT [SalesPersonQuotaHistory] ([BusinessEntityID], [SalesQuota], [QuotaDate]) VALUES (3, CAST(2000.00 AS Decimal(11, 2)), CAST(N'2020-05-10' AS Date))
INSERT [SalesPersonQuotaHistory] ([BusinessEntityID], [SalesQuota], [QuotaDate]) VALUES (3, CAST(500.00 AS Decimal(11, 2)), CAST(N'2020-06-10' AS Date))
INSERT [SalesPersonQuotaHistory] ([BusinessEntityID], [SalesQuota], [QuotaDate]) VALUES (1, CAST(1000.00 AS Decimal(11, 2)), CAST(N'2020-06-20' AS Date))
INSERT [SalesPersonQuotaHistory] ([BusinessEntityID], [SalesQuota], [QuotaDate]) VALUES (5, CAST(3000.00 AS Decimal(11, 2)), CAST(N'2020-07-10' AS Date))
INSERT [SalesPersonQuotaHistory] ([BusinessEntityID], [SalesQuota], [QuotaDate]) VALUES (4, CAST(2000.00 AS Decimal(11, 2)), CAST(N'2020-07-12' AS Date))
INSERT [SalesPersonQuotaHistory] ([BusinessEntityID], [SalesQuota], [QuotaDate]) VALUES (3, CAST(500.00 AS Decimal(11, 2)), CAST(N'2020-08-10' AS Date))
INSERT [SalesPersonQuotaHistory] ([BusinessEntityID], [SalesQuota], [QuotaDate]) VALUES (2, CAST(1000.00 AS Decimal(11, 2)), CAST(N'2020-09-01' AS Date))
INSERT [SalesPersonQuotaHistory] ([BusinessEntityID], [SalesQuota], [QuotaDate]) VALUES (1, CAST(800.00 AS Decimal(11, 2)), CAST(N'2020-10-15' AS Date))
INSERT [SalesPersonQuotaHistory] ([BusinessEntityID], [SalesQuota], [QuotaDate]) VALUES (2, CAST(1000.00 AS Decimal(11, 2)), CAST(N'2021-01-10' AS Date))
INSERT [SalesPersonQuotaHistory] ([BusinessEntityID], [SalesQuota], [QuotaDate]) VALUES (4, CAST(500.00 AS Decimal(11, 2)), CAST(N'2021-01-15' AS Date))
INSERT [SalesPersonQuotaHistory] ([BusinessEntityID], [SalesQuota], [QuotaDate]) VALUES (3, CAST(2000.00 AS Decimal(11, 2)), CAST(N'2021-01-20' AS Date))
INSERT [SalesPersonQuotaHistory] ([BusinessEntityID], [SalesQuota], [QuotaDate]) VALUES (1, CAST(1000.00 AS Decimal(11, 2)), CAST(N'2021-01-30' AS Date))
INSERT [SalesPersonQuotaHistory] ([BusinessEntityID], [SalesQuota], [QuotaDate]) VALUES (3, CAST(2000.00 AS Decimal(11, 2)), CAST(N'2021-02-10' AS Date))
INSERT [SalesPersonQuotaHistory] ([BusinessEntityID], [SalesQuota], [QuotaDate]) VALUES (5, CAST(3000.00 AS Decimal(11, 2)), CAST(N'2021-03-10' AS Date))

A. 创建一个简单的公用表表达式

以下示例显示 Adventure Works Cycles 的每个销售代表每年的销售订单总数。

WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM  SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;

B. 使用公用表表达式来限制计数并报告平均值

WITH Sales_CTE (SalesPersonID, NumberOfOrders)
AS
(
    SELECT SalesPersonID, COUNT(*)
    FROM SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
    GROUP BY SalesPersonID
)
SELECT AVG(NumberOfOrders) AS "Average Sales Per Person"
FROM Sales_CTE;

C. 在单个查询中使用多个 CTE 定义

WITH Sales_CTE (SalesPersonID, TotalSales, SalesYear)
AS
-- Define the first CTE query.
(
    SELECT SalesPersonID
              , SUM(TotalDue) AS TotalSales
              , YEAR(OrderDate) AS SalesYear
    FROM SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
    GROUP BY SalesPersonID, YEAR(OrderDate)

)
,   -- Use a comma to separate multiple CTE definitions.

-- Define the second CTE query, which returns sales quota data by year for each sales person.
Sales_Quota_CTE (BusinessEntityID, SalesQuota, SalesQuotaYear)
AS
(
       SELECT BusinessEntityID
                 , SUM(SalesQuota)AS SalesQuota
                 , YEAR(QuotaDate) AS SalesQuotaYear
       FROM SalesPersonQuotaHistory
       GROUP BY BusinessEntityID, YEAR(QuotaDate)
)

-- Define the outer query by referencing columns from both CTEs.
SELECT SalesPersonID
          , SalesYear
          , FORMAT(TotalSales,'C','en-us') AS TotalSales
          , SalesQuotaYear
          , FORMAT (SalesQuota,'C','en-us') AS SalesQuota
          , FORMAT (TotalSales -SalesQuota, 'C','en-us') AS Amt_Above_or_Below_Quota
FROM Sales_CTE  JOIN Sales_Quota_CTE 
ON Sales_Quota_CTE.BusinessEntityID = Sales_CTE.SalesPersonID
AND Sales_CTE.SalesYear = Sales_Quota_CTE.SalesQuotaYear
ORDER BY SalesPersonID, SalesYear;

4 创建和使用公用表表达式的准则（注意事项）

• CTE 后面必须跟一个引用部分或全部 CTE 列的 SELECT、INSERT、UPDATE 或 DELETE 语句。CTE 也可以在 CREATE VIEW 语句中指定为视图的定义 SELECT 语句的一部分。

• 可以在非递归 CTE 中定义多个 CTE 查询定义。定义必须由以下集合运算符之一组合：UNION ALL、UNION、INTERSECT 或 EXCEPT。

• CTE 可以在同一个 WITH 子句中引用自身和先前定义的 CTE。不允许前向引用。

• 不允许在 CTE 中指定多个 WITH 子句。例如，如果 CTE_query_definition 包含子查询，则该子查询不能包含定义另一个 CTE 的嵌套 WITH 子句。

• CTE_query_definition 中不能使用以下子句：

  • ORDER BY（指定 TOP 子句时除外）

  • 进入

  • 带有查询提示的 OPTION 子句

  • 浏览

• 当 CTE 用于作为批处理一部分的语句中时，它之前的语句必须跟一个分号。

• 引用 CTE 的查询可用于定义游标。

• 远程服务器上的表可以在 CTE 中引用。

• 执行 CTE 时，引用 CTE 的任何提示都可能与 CTE 访问其基础表时发现的其他提示发生冲突，其方式与在查询中引用视图的提示相同。发生这种情况时，查询会返回错误。

以上内容参考：https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/ms175972%28v=sql.110%29?redirectedfrom=MSDN