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))
View Code
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 访问其基础表时发现的其他提示发生冲突,其方式与在查询中引用视图的提示相同。发生这种情况时,查询会返回错误。
原创文章,作者:kirin,如若转载,请注明出处:https://blog.ytso.com/tech/database/274734.html