cte公用表表达式_SQL Server公用表表达式（CTE）

最新推荐文章于 2024-07-24 16:20:20 发布

culuo4781

最新推荐文章于 2024-07-24 16:20:20 发布

阅读量803

点赞数

文章标签： mysql java python 数据库 sql

原文链接：https://www.sqlshack.com/sql-server-common-table-expressions-cte/

版权

cte公用表表达式

什么是通用表表达式 (What is a Common Table Expression )

A Common Table Expression, also called as CTE in short form, is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. The CTE can also be used in a View.

公用表表达式，也简称为CTE，是一个临时的命名结果集，您可以在SELECT，INSERT，UPDATE或DELETE语句中引用该结果集。 CTE也可以在视图中使用。

In this article, we will see in detail about how to create and use CTEs from our SQL Server.

在本文中，我们将详细介绍如何从SQL Server创建和使用CTE。

常用表表达式的语法和示例 (Syntax and Examples for Common Table Expressions)

The CTE query starts with a “With” and is followed by the Expression Name. We will be using this expression name in our select query to display the result of our CTE Query and be writing our CTE query definition.

CTE查询以“ With”开头，后跟“表达式名称”。我们将在选择查询中使用此表达式名称来显示CTE查询的结果并编写CTE查询定义。

 
WITH expression_name [ ( column_name [,...n] ) ] 
AS 
( CTE_query_definition )

To view the CTE result we use a Select query with the CTE expression name.

要查看CTE结果，我们使用带有CTE表达式名称的Select查询。

 
Select [Column1,Column2,Column3 …..] from expression_name

要么

 
Select * from expression_name

公用表表达式（CTE）类型 (Common Table Expression (CTE) Types)

There are two types of CTEs: Recursive and Non-Recursive

CTE有两种类型：递归和非递归

Non-Recursive CTEs

非递归CTE

Non-Recursive CTEs are simple where the CTE doesn’t use any recursion, or repeated processing in of a sub-routine. We will create a simple Non-Recursive CTE to display the row number from 1 to 10.

非递归CTE很简单，其中CTE不使用任何递归或子例程中的重复处理。我们将创建一个简单的非递归CTE，以显示从1到10的行号。

As per the CTE Syntax each CTE query will start with a “With” followed by the CTE Expression name with column list.

根据CTE语法，每个CTE查询都将以“ With”开头，然后是带有列列表的CTE表达式名称。

Here we have been using only one column as ROWNO. Next is the Query part, here we write our select query to be execute for our CTE. After creating our CTE query to run the CTE use the select statement with CTE Expression name.

在这里，我们仅使用一列作为ROWNO。接下来是查询部分，在这里我们编写要对CTE执行的选择查询。创建我们的CTE查询以运行CTE后，请使用带有CTE表达式名称的select语句。

 
;with ROWCTE(ROWNO) as  
   (  
     SELECT 
  ROW_NUMBER() OVER(ORDER BY name ASC) AS ROWNO
FROM sys.databases 
WHERE database_id <= 10
    )  
 
SELECT * FROM ROWCTE

Output: When we run the query, we can see the below output.

输出：运行查询时，我们可以看到以下输出。

Recursive CTE

递归CTE

Recursive CTEs are use repeated procedural loops aka recursion. The recursive query call themselves until the query satisfied the condition. In a recursive CTE we should provide a where condition to terminate the recursion.:

递归CTE使用重复的过程循环（也称为递归）。递归查询将自行调用，直到查询满足条件为止。在递归CTE中，我们应该提供一个where条件来终止递归。：

We will see how to create a simple Recursive query to display the Row Number from 1 to 10 using a CTE.

我们将看到如何创建一个简单的递归查询以使用CTE显示从1到10的行号。

Firstly we declare the Integer variable as “RowNo” and set the default value as 1 and we have created our first CTE query as an expression name, “ROWCTE”. In our CTE we’ll first display the default row number and next we’ll use a Union ALL to increment and display the row number 1 by one until the Row No reaches the incremented value to 10. To view the result, we will use a select query to display our CTE result.

首先，我们将Integer变量声明为“ RowNo”，并将默认值设置为1，并创建了第一个CTE查询作为表达式名称“ ROWCTE”。在CTE中，我们将首先显示默认行号，然后我们将使用Union ALL来递增并显示行号1，直到行号达到递增值到10。要查看结果，我们将使用选择查询以显示我们的CTE结果。

 
Declare @RowNo int =1;
;with ROWCTE as  
   (  
      SELECT @RowNo as ROWNO    
		UNION ALL  
      SELECT  ROWNO+1  
  FROM  ROWCTE  
  WHERE RowNo < 10
    )  
 
SELECT * FROM ROWCTE

Output: When we run the query, we can see the below output.

输出：运行查询时，我们可以看到以下输出。

CTE Query to display Date Range:

CTE查询以显示日期范围：

Let’s consider as there is a scenario to display the date from start date to end date all one by one as each row with details. In order to display the recursive data, we will be using the CTE Query.

让我们考虑一下，因为存在一种方案，将开始日期到结束日期的日期一一显示在每一行中，并带有详细信息。为了显示递归数据，我们将使用CTE查询。

Here we will write a CTE query to display the dates range with week number and day. For this we set the start and end date in parameter. Here in this example we have used the getdate() to set the start date as Todays date, and for end date we add 16 days from today.

在这里，我们将编写一个CTE查询以显示带有星期数和日期的日期范围。为此，我们在参数中设置开始和结束日期。在此示例中，我们已使用getdate（）将开始日期设置为“今天”日期，对于结束日期，我们要添加从今天开始的16天。

CTE without Union All

没有Union All的CTE

Here we can see we have create a simple CTE query to display the RowNo, start date and week number. When we run this we will get only one result with RowNo as “1” ,StartDate as current date and week number along with week day.

在这里，我们可以看到我们已经创建了一个简单的CTE查询，以显示RowNo，开始日期和星期数。当我们运行此命令时，将仅获得一个结果，其中RowNo为“ 1”，StartDate为当前日期和星期数以及星期几。

 
declare @startDate datetime,  
        @endDate datetime;  
  
select  @startDate = getdate(),  
        @endDate = getdate()+16;  
-- select @sDate StartDate,@eDate EndDate  
;with myCTE as  
   (  
      select 1 as ROWNO,@startDate StartDate,'W - '+convert(varchar(2),  
            DATEPART( wk, @startDate))+' / D ('+convert(varchar(2),@startDate,106)+')' as 'WeekNumber'  
    )  
select ROWNO,Convert(varchar(10),StartDate,105)  as StartDate ,WeekNumber from myCTE ;

Output: When we run the query, we can see the below output.

输出：运行查询时，我们可以看到以下输出。

CTE with Union All

CTE与Union All

In order to display the result from start date to end date one by one as recursive, we use a Union All to increment RowNo, to add the day one by one till the condition satisfied the date range, in order to stop the recursion we need set some condition. In this example, we repeat the recursion to display our records until the date is less than or equal to the end date.

为了将开始日期到结束日期的结果一一显示为递归，我们使用Union All来增加RowNo，一一添加一天直到条件满足日期范围，以停止递归设置一些条件。在此示例中，我们重复递归以显示我们的记录，直到日期小于或等于结束日期为止。

 
declare @startDate datetime,  
        @endDate datetime;  
  
select  @startDate = getdate(),  
        @endDate = getdate()+16;  
-- select @sDate StartDate,@eDate EndDate  
;with myCTE as  
   (  
      select 1 as ROWNO,@startDate StartDate,'W - '+convert(varchar(2),  
            DATEPART( wk, @startDate))+' / D ('+convert(varchar(2),@startDate,106)+')' as 'WeekNumber'       
  union all  
       select  ROWNO+1 ,dateadd(DAY, 1, StartDate) ,  
              'W - '+convert(varchar(2),DATEPART( wk, StartDate))+' / D ('+convert(varchar(2),  
               dateadd(DAY, 1, StartDate),106)+')' as 'WeekNumber'     
  FROM  myCTE  
  WHERE dateadd(DAY, 1, StartDate)<=  @endDate    
    )  
select ROWNO,Convert(varchar(10),StartDate,105)  as StartDate ,WeekNumber from myCTE

Output: When we run the query, we can see the below output.

输出：运行查询时，我们可以看到以下输出。

多个CTE (Multiple CTE)

In some scenarios, we need to create more than one CTE query and join them to display our result. In this case, we can use the Multiple CTEs. We can create a multiple CTE query and combine them into one single query by using the comma. Multiple CTE need to be separate by “,” comma fallowed by CTE name.

在某些情况下，我们需要创建多个CTE查询并将其联接以显示我们的结果。在这种情况下，我们可以使用多个CTE。我们可以创建多个CTE查询，并使用逗号将它们组合为一个查询。多个CTE必须以CTE名称分隔的逗号分隔。

We will be using above same date range example to use more than one CTE query, here we can see as we have created two CTE query as CTE1 and CTE 2 to display date range result for both CTE1 and for CTE2.

我们将使用上面相同的日期范围示例来使用多个CTE查询，在这里我们可以看到，因为我们创建了两个CTE查询，分别为CTE1和CTE 2，以显示CTE1和CTE2的日期范围结果。

Example :

范例：

 
Declare @startDate datetime,@endDate datetime;  
Declare @startDate1 datetime,@endDate1 datetime;  
Set  @startDate  = '2017-02-10'; Set  @endDate    = '2017-02-15';  
Set  @startDate1 = '2017-02-16'; Set  @endDate1   = '2017-02-28';          
 
 
 WITH    CTE1   
  AS (  
    SELECT 'CTE1' CTEType ,@startDate StartDate,'W'+convert(varchar(2),DATEPART( wk, @startDate))+'('+convert(varchar(2),@startDate,106)+')' as 'WeekNumber'  
	    UNION ALL
    SELECT CTEType, dateadd(DAY, 1, StartDate) ,'W'+convert(varchar(2),DATEPART( wk, StartDate))+'('+convert(varchar(2),dateadd(DAY, 1, StartDate),106)+')' as 'WeekNumber' 
	    FROM CTE1  
	    WHERE dateadd(DAY, 1, StartDate)<=  @endDate  
             ),  
  CTE2   
  AS (  
       SELECT 'CTE2' CTEType, @startDate StartDate,'W'+convert(varchar(2),DATEPART( wk, @startDate1))+'('+convert(varchar(2),@startDate1,106)+')' as 'WeekNumber'   
	      UNION ALL
	  SELECT 'CTE2' valuetype, dateadd(DAY, 1, StartDate) ,'W'+convert(varchar(2),DATEPART( wk, StartDate))+'('+convert(varchar(2),dateadd(DAY, 1, StartDate),106)+')' as 'WeekNumber'         
		  FROM CTE2  
		  WHERE dateadd(DAY, 1, StartDate)<=  @endDate1  
 ) 
  
    SELECT CTEType, Convert(varchar(10),StartDate,105)  as StartDate ,WeekNumber    FROM    CTE1  
		 UNION ALL   
    SELECT CTEType, Convert(varchar(10),StartDate,105)  as StartDate ,WeekNumber    FROM    CTE2

Output: When we run the query, we can see the below output.

输出：运行查询时，我们可以看到以下输出。

使用CTE查询SQL表 (Using CTE query for SQL Table)

Now let’s see on, how to use CTE query for our SQL server table data.

现在让我们看看如何对SQL Server表数据使用CTE查询。

Create Database: First, we create a database for creating our table

创建数据库：首先，我们创建一个用于创建表的数据库

 
USE MASTER    
GO    
-- 1) Check for the Database Exists .If the database is exist then drop and create new DB    
IF EXISTS (SELECT [name] FROM sys.databases WHERE [name] = 'CTEDB' )    
DROP DATABASE CTEDB    
GO    
    
CREATE DATABASE CTEDB    
GO    
    
USE CTEDB    
GO

Create Table: Now we create a sample Item Table on the created Database.

创建表：现在，我们在创建的数据库上创建示例项目表。

 
IF EXISTS ( SELECT [name] FROM sys.tables WHERE [name] = 'ItemDetails' )    
DROP TABLE ItemDetails    
GO    
    
CREATE TABLE ItemDetails    
(    
Item_ID int identity(1,1),    
Item_Name VARCHAR(100) NOT NULL,    
Item_Price int NOT NULL,    
Date VARCHAR(100) NOT NULL ,
CONSTRAINT [PK_ItemDetails] PRIMARY KEY CLUSTERED     
(     
[Item_ID] ASC     
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]     
) ON [PRIMARY]     
    
GO

Insert Sample Data: We will insert few sample records for using in our CTE Query.

插入样本数据：我们将插入一些样本记录以在CTE查询中使用。

 
Insert into ItemDetails(Item_Name,Item_Price,Date) values('Access Point',950,'2017-02-10')    
Insert into ItemDetails(Item_Name,Item_Price,Date) values('CD',350,'2017-02-13')     
Insert into ItemDetails(Item_Name,Item_Price,Date) values('Desktop Computer',1400,'2017-02-16')    
Insert into ItemDetails(Item_Name,Item_Price,Date) values('DVD',1390,'2017-03-05')    
Insert into ItemDetails(Item_Name,Item_Price,Date) values('DVD Player',450,'2017-05-07')    
Insert into ItemDetails(Item_Name,Item_Price,Date) values('Floppy',1250,'2017-05-07')    
Insert into ItemDetails(Item_Name,Item_Price,Date) values('HDD',950,'2017-07-10')       
Insert into ItemDetails(Item_Name,Item_Price,Date) values('MobilePhone',1150,'2017-07-10')    
Insert into ItemDetails(Item_Name,Item_Price,Date) values('Mouse',399,'2017-08-12')    
Insert into ItemDetails(Item_Name,Item_Price,Date) values('MP3 Player ',897,'2017-08-14')    
Insert into ItemDetails(Item_Name,Item_Price,Date) values('Notebook',750,'2017-08-16')     
Insert into ItemDetails(Item_Name,Item_Price, Date) values('Printer',675,'2017-07-18')    
Insert into ItemDetails(Item_Name,Item_Price,Date) values('RAM',1950,'2017-09-23')    
Insert into ItemDetails(Item_Name,Item_Price,Date) values('Smart Phone',679,'2017-09-10')    
Insert into ItemDetails(Item_Name,Item_Price,Date) values('USB',950,'2017-02-26')    
    
select * from ItemDetails

CTE Example:

CTE示例：

Now we will create a simple temporary result using CTE Query. Here in this CTE Query we have given the expression name as “itemCTE” and we have added the list of Columns which we use in the CTE query. In the CTE query we display all item details with the year.

现在，我们将使用CTE查询创建一个简单的临时结果。在此CTE查询中，我们将表达式名称指定为“ itemCTE”，并添加了在CTE查询中使用的“列”列表。在CTE查询中，我们显示所有项目详细信息以及年份。

 
;WITH itemCTE (Item_ID, Item_Name, Item_Price,SalesYear)
AS
(
  SELECT Item_ID, Item_Name, Item_Price ,YEAR(Date) SalesYear
  FROM ItemDetails   
)
 
Select * from itemCTE

Output: When we run the query, we can see the below output.

输出：运行查询时，我们可以看到以下输出。

CTE using Union ALL

使用Union ALL进行CTE

Let’s consider there is a below two scenarios to display the result.

让我们考虑以下两种情况来显示结果。

The first scenario is to display each Item Price of current Year.
第一种情况是显示当年的每个物料价格。
The second scenario is to increment 10% to each Item Price for next year.
第二种情况是明年将每个商品价格增加10％。

For this we use the above CTE Query. In this query, we add the UNION ALL and in UNION ALL Query we do calculation to add 10% to each item Price and show in next row with adding one year.

为此，我们使用上面的CTE查询。在此查询中，我们添加UNION ALL，在UNION ALL查询中，我们进行计算以将每项价格加10％，并在下一行中显示加一年。

 
;WITH itemCTE (Item_ID, Item_Name, Item_Price,MarketRate,SalesYear)
AS
(
    SELECT Item_ID, Item_Name, Item_Price ,'Present Price' as MarketRate,YEAR(Date) as SalesYear 
    FROM ItemDetails  
	UNION ALL
	 SELECT Item_ID as Item_ID, Item_Name,
			(Item_Price + (Item_Price *10 )/100) as Item_Price,
			'Future Price' as MarketRate,  YEAR(dateadd(YEAR, 1, Date))  as SalesYear
    FROM ItemDetails
    
) 
SELECT * from itemCTE Order by Item_Name,SalesYear

Output: When we run the query, we can see the below output.

输出：运行查询时，我们可以看到以下输出。

插入的通用表表达式（CTE） (Common Table Expressions (CTE) for Insert)

Now we will see how to insert the CTE result to another table. For this let’s consider our above Item Table. We insert the Item details result of above CTE query to Item History table. For this first we create an Item History table.

现在，我们将看到如何将CTE结果插入到另一个表中。为此，让我们考虑上面的项目表。我们将上述CTE查询的商品明细结果插入到商品历史记录表中。首先，我们创建一个项目历史记录表。

Create Item History Table: In this history table, we add the same columns as item table along with MarketRate column as present or future Item price. Here is the query to create an ItemHistory table.

创建物料历史记录表：在此历史记录表中，我们将与物料表相同的列以及MarketRate列添加为当前或将来的物料价格。这是创建ItemHistory表的查询。

 
CREATE TABLE ItemHistory    
(    
ID int identity(1,1),
oldITEMID    int, 
Item_Name VARCHAR(100) NOT NULL,    
Item_Price int NOT NULL,   
MarketRate  VARCHAR(100) NOT NULL,    
Date VARCHAR(100) NOT NULL )

CTE Insert Example:

CTE插入示例：

Here we use above same CTE query Insert the result in to the Item History table. From this query we insert both item details of present year Item price along with the next year Item prices added as 10% more.

在这里，我们使用上面相同的CTE查询将结果插入到“项目历史记录”表中。从该查询中，我们将插入当前年度物料价格以及明年物料价格增加10％的物料详细信息。

 
;WITH itemCTE (Item_ID, Item_Name, Item_Price,MarketRate,Date)
AS
(
    SELECT Item_ID, Item_Name, Item_Price ,'Present Price' as MarketRate,Date  
    FROM ItemDetails  
	UNION ALL
	 SELECT Item_ID as Item_ID, Item_Name,(Item_Price + (Item_Price *10 )/100) as Item_Price,
	 'Future Price' as MarketRate,  dateadd(YEAR, 1, Date) as Date
    FROM ItemDetails
    
)
-- Define the outer query referencing the CTE name.
Insert into ItemHistory(oldITEMID ,Item_Name,Item_Price,MarketRate,Date)  
SELECT Item_ID, Item_Name, Item_Price,MarketRate,year(Date) from itemCTE Order by Item_Name,Date

Output: When we run the query, we can see the below output as 30 records has been inserted into our Item History table.

输出：运行查询时，我们可以看到以下输出，因为30条记录已插入到“项目历史记录”表中。

Select Query:

选择查询：

To view the item history result we select and display all the details.

要查看项目历史记录结果，我们选择并显示所有详细信息。

 
select * from ItemHistory

Output: When we run the query, we can see the below output from item history table.

输出：运行查询时，我们可以从项目历史记录表中看到以下输出。

使用CTE创建视图示例： (Create View with CTE Example:)

Now we see how to use the above CTE query can be used in a view. Here we create a view and we add the CTE result inside the view. When we select the view as a result, we can see the CTE output will be displayed.

现在我们看看如何使用上面的CTE查询可以在视图中使用。在这里，我们创建一个视图，并将CTE结果添加到该视图内。当我们选择视图作为结果时，我们可以看到将显示CTE输出。

Example Query:

查询示例：

 
CREATE VIEW CTEVIEW
AS
 WITH    itemCTE1 AS
        (
         SELECT Item_ID, Item_Name, Item_Price ,'Present Price' as MarketRate,Date as IDate
    FROM ItemDetails  
	UNION ALL
	 SELECT Item_ID as Item_ID, Item_Name,(Item_Price + (Item_Price *10 )/100) as Item_Price,
	 'Future Price' as MarketRate,  dateadd(YEAR, 1, Date) as IDate
    FROM ItemDetails
      )
  
  SELECT Item_ID, Item_Name, Item_Price,MarketRate,year(IDate) as IDate from itemCTE1 
 
GO
 
-- T-SQL test view
SELECT * FROM CTEVIEW Order by Item_Name,IDate
GO

Output: When we run the query, we can see the below output as result from the View.

输出：运行查询时，我们可以从View中看到以下输出。

How to write a clean CTE Query:

如何编写干净的CTE查询：

Here are some basic guidelines that need to be followed to write a good CTE Query.

这是编写良好的CTE查询所需要遵循的一些基本准则。

A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns.
CTE之后必须是引用某些或所有CTE列的单个SELECT，INSERT，UPDATE或DELETE语句。
Multiple CTE query definitions can be defined in a non recursive CTE.
可以在非递归CTE中定义多个CTE查询定义。
A CTE can reference itself and previously defined CTEs in the same WITH clause
CTE可以在同一WITH子句中引用自身和先前定义的CTE。
We can use only one With Clause in a CTE
我们只能在CTE中使用一个With子句
ORDER BY, INTO, COMPUTE or COMPUTE BY, OPTION, FOR XML cannot be used in non-recursive CTE query definition
ORDER BY，INTO，COMPUTE或COMPUTE BY，OPTION，FOR XML不能在非递归CTE查询定义中使用
SELECT DISTINCT, GROUP BY, HAVING, Scalar aggregation, TOP, LEFT, RIGHT, OUTER JOIN (INNER JOIN is allowed) subqueries cannot be used in a recursive CTE query definition.
SELECT DISTINCT，GROUP BY，HAVING，标量聚合，TOP，LEFT，RIGHT，OUTER JOIN（允许使用INNER JOIN）子查询不能在递归CTE查询定义中使用。

结论 (Conclusion)

CTEs can be used to create a recursive query and can be used to reference itself multiple times. CTEs can be used instead of views and finally a CTE is easy and simple for readability and code maintainability.

CTE可用于创建递归查询，并可多次引用自身。可以使用CTE代替视图，最后CTE对于可读性和代码可维护性而言既简单又容易。

翻译自: https://www.sqlshack.com/sql-server-common-table-expressions-cte/

cte公用表表达式

culuo4781

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
cte公用表表达式_SQL Server公用表表达式（CTE）

cte公用表表达式什么是通用表表达式 (What is a Common Table Expression ) A Common Table Expression, also called as CTE in short form, is a temporary named result set that you can reference within a SELECT, INSERT...
复制链接

扫一扫