[database] CTE (common table expression)

Common table expression (CTE) The definition of

The definition of a common expression consists of three parts ：

Name of common table expression （ stay WITH after ）
Column names involved （ Optional ）
One SELECT sentence ( Following the AS after ),

One of the benefits of common table expressions is how long they can be referenced in the next statement , Remember the next sentence

stay MSDN Prototype in ：

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

AS

( CTE_query_definition )

Non recursive common table expression (CTE）

Nonrecursive CTE Quotation and common Select There is not much substantive difference between queries , Call the following ：

with admin_CET
as
(
    select ID,name,parentid from adminpart
)
select ID,name,parentid from admin_CET

Non recursive CTE The most common place to call is paging query ：

with admin_CET
as
(
    select ID,name,parentid,row_number() over(order by ID) as rowNum  from adminpart
)
select ID,name,parentid,rowNum from admin_CET where rowNum between 1 and 10

recursive CTE call

Refer to CTE Call itself in the statement in it. CTE.CTE Derived recursion can be defined more than once . The concept of recursion , A function or procedure calls itself directly or indirectly ;

CTE The common place for recursive calls is that a table contains ownership , Query the sub data of the current data ;

Simply define a table ：

CREATE TABLE [dbo].[adminpart](
    [ID] [int] IDENTITY(1,1) NOT NULL,-- At present ID
    [name] [nvarchar](50) NOT NULL,-- full name 
    [parentid] [int] NOT NULL-- Parent class ID
)

The data are as follows

Inquire about admin All users under , At this time, we need to use the concept of recursion to complete ：

with admin_CET
as
(
    select ID,name,parentid,0 as alv from adminpart where name='admin'-- Basic query 
    union all
    select ap.ID,ap.name,ap.parentid,alv+1 as alv from admin_CET ac inner join adminpart ap
    on ap.parentid=ac.ID  -- recursive join Inquire about , Note that it must be ap.parentid=ac.ID, If you don't understand , We need to understand the concept of recursion 
)

CTE Appearance , It is a good solution to the query of mutual primary and secondary relationship data types . notes ：SQL2003 I won't support it CTE.

remarks ：

recursive CTE Contain at least two queries ( Also known as member ). The first query is fixed-point members , A fixed-point member is just a query that returns a valid table , A base or anchor for recursion . The second query is called recursive member , What makes this query a recursive member is a pair of CTE A recursive reference to a name is a trigger . Logically CTE The internal application of the name is understood as the result set of the previous query .

Recursive queries have no explicit recursive termination conditions , Stop recursion only if the second recursive query returns an empty result set or exceeds the maximum number of recursions . The method that refers to the upper limit of the number of recursions is to use MAXRECURION.

Mysql The syntax of recursion is a little different

It's mainly more RECURSIVE, Auspicious view ：【 note 】MYSQL8 new function CTE（ Common table expressions ） Elegant implementation of recursive query