Shulou(Shulou.com)06/01 Report--

Today, I will talk to you about how to understand the common table expression CTE in the MariaDB table, which may not be understood by many people. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

Preface

Common table expressions (Common Table Expression,CTE) are similar to derived tables in that they are virtual tables, but CTE has some advantages and convenience over derived tables.

There are two types of CTE: non-recursive CTE and recursive CTE.

CTE is a feature of standard SQL and is a type of table expression. It is not until MariaDB supports CTE,MySQL 8 that CTE is supported.

1. Non-recursive CTE

CTE is defined using the WITH clause and consists of three parts: the CTE name cte_name, the query statement inner_query_definition that defines the CTE, and the external query statement outer_query_definition that references the CTE.

Its format is as follows:

WITH cte_name1 [(column_name_list)] AS (inner_query_definition_1) [, cte_name2 [(column_name_list)] AS (inner_query_definition_2)] [,...] outer_query_definition

Where column_name_list specifies the list name in inner_query_definition, if you do not write this option, you need to ensure that all columns in inner_query_definition are named and unique, that is, there are two naming methods for column names: internal naming and external naming.

Note that outer_quer_definition must be executed at the same time as the CTE definition statement, because CTE is a temporary virtual table, and its definition is meaningful only if it is referenced immediately.

The following statement is a simple use of CTE. First define a virtual table, CTE, and then reference it in an external query.

CREATE OR REPLACE TABLE t (id INT NOT NULL PRIMARY KEY,sex CHAR (3), NAME CHAR (20)); INSERT INTO t VALUES (1), (2), (2), (3), (3), (3), (4), (5), (6), (6), (7), (8) # define CTE, rename each column by the way, and use the ORDER BY clause WITH nv_t (myid,mysex,myname) AS (SELECT * FROM t WHERE sex='nv' ORDER BY id DESC) # use CTESELECT * FROM nv_t +-+ | myid | mysex | myname | 2 | nv | Mariah | | 3 | nv | gaoxiaofang | | 5 | nv | Selina | | 8 | nv | xiaofang | +- -+

As you can see from the results, it is useless to use the ORDER BY clause in the definition statement of CTE.

Here you can find several things that CTE and derived tables have in common: column names are required for each column, including computed columns; column names must be unique; and ORDER BY clauses cannot be used unless the TOP keyword is used (standard SQL strictly adheres to the rule that ORDER BY cannot be used, but is allowed in MySQL/MariaDB). Not only CTE and derived tables, but also other table expressions (inline table-valued functions (supported by sql server only), views) should also meet these conditions. The reason is that the essence of table expressions is tables, and although they are virtual tables, they should also meet the conditions for forming tables.

On the one hand, in the relational model, the table corresponds to the relationship, the rows in the table correspond to the tuples in the relational model, and the fields (or columns) in the table correspond to the attributes in the relationship. An attribute consists of three parts: the name of the attribute, the type of the attribute, and the value of the attribute. So to form a table, you must ensure the name of the attribute, that is, each column has a name and is unique.

On the other hand, the relational model is based on collections and does not require ordering in collections, so data cannot be arranged in order when forming tables, that is, ORDER BY clauses cannot be used. The reason why you can use the ORDER BY clause after using TOP is that ORDER BY at this time only provides logical data extraction service for TOP and does not provide sorting service. For example, use ORDER BY to help TOP select the first 10 rows, but these 10 rows of data are not guaranteed to be sequential when forming a table.

CTE has several advantages over derived tables:

1. Multiple references: avoid repetitive writing.

two。 Multiple definitions: avoid nesting of derived tables.

3. You can use recursive CTE to implement recursive queries.

For example:

# multiple references to avoid rewriting WITH nv_t (myid,mysex,myname) AS (SELECT * FROM t WHERE sex='nv') SELECT t1.writing journal t2.quote from nv_t T1 JOIN nv_t t2WHERE t1.myid = t2.myid+1 # multiple definitions to avoid derived table nesting WITHnv_t1 AS (/ * first CTE * / SELECT * FROM t WHERE sex='nv'), nv_t2 AS (/ * second CTE * / SELECT * FROM nv_t1 WHERE id > 3) SELECT * FROM nv_t2

If the above statement uses derived tables instead of CTE, it is equivalent to:

SELECT * FROM (SELECT * FROM (SELECT * FROM t WHERE sex='nv') AS nv_t1) AS nv_t2;2. Recursive CTE

SQL is a structured query language, and its recursion is very poor. Using recursive CTE can slightly improve this flaw.

A common table expression (CTE) has the important advantage of being able to reference itself to create a recursive CTE. A recursive CTE is a common table expression that repeatedly executes the initial CTE to return a subset of data until the complete result set is obtained.

When a query references a recursive CTE, it is called a recursive query. Recursive queries are often used to return hierarchical data, such as displaying employees or BOM scenarios in an organization chart (where the parent product has one or more components, and those components may have child components, or data from other parent products).

Recursive CTE can greatly simplify the code required to run recursive queries in SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statements.

That is, recursive CTE is implemented by referencing itself. It will repeatedly query each recursive subset until the final result is obtained. This makes it very suitable for dealing with "tree structure" data or data with "hierarchical relationships".

2.1 Grammar

A recursive cte contains one or more anchor members, one or more recursive members, and the last anchor member must join the first recursive member using "union [all]" (the recursive CTE in mariadb only supports the union [all] set algorithm).

The following is the recursive CTE syntax for a single anchor member and a single recursive member:

With recursive cte_name as (select_statement_1 / * this cte_body is called anchor member * / union [all] cte_usage_statement / * here refers to cte itself, called recursive member * /) outer_definition_statement / * query for recursive CTE is called recursive query * /

Where:

Select_statement_1: known as the "anchor member", this is the first part of the recursive cte that executes and is the data source when the recursive member begins recursion.

Cte_usage_statement: called a "recursive member", the cte itself must be referenced in this statement. It is the real place of recursion in the recursive cte. It first obtains the recursive data source from the anchor member, and then combines with other data sets to start recursion. Each recursion passes the recursive result to the next recursive action. After repeated queries, the recursion ends when the data cannot be found at last.

Outer_definition_statement: is a query for recursive cte, which is called a "recursive query".

2.2 Recursive CTE example (1)

Take the most classic example: genealogy.

For example, here is a genealogical table

CREATE OR REPLACE TABLE fork (id INT NOT NULL UNIQUE,NAME CHAR (20), father INT,mother INT) INSERT INTO fork VALUES (2), (3) (3), (3) (3), (4), (4), (6), (6), (6), (6), (6), (7), (7), (8), (7), (8), (5), (4), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (4) INSERT INTO fork VALUES (3), (3) (3), (3) (3) (3) (3) (3) (3) (3) (3) (3) (3) (3) (3) (3) (3) (3) (3) (3) (3) (3) (3) (3) (3) (3) (3) (3) (3) (3) (3) (3) (3) (3) (3) (3) (3) (3) (3) (3) (3) (3) (3) (3) (3) (3 +-+ | id | name | father | mother | +-- + | 1 | chenyi | 2 | 3 | 2 | huagner | 4 | 5 | | 3 | zhangsan | NULL | NULL | | 4 | lisi | 6 | 7 | 5 | wangwu | 8 | 9 | | 6 | zhaoliu | NULL | NULL | | 7 | sunqi | NULL | NULL | 8 | songba | NULL | NULL | | 9 | yangjiu | NULL | NULL | +-

The corresponding structure diagram of the genealogy table:

If you want to find someone's paternal line in the genealogy, first find out who you want to start looking from among the anchor members, for example, start with "Chen Yi" in the picture above. So Chen Yi's record is the data source of the first recursive member. Connect this data source to the genealogical table to find Chen Yi's father, Huang er, and the result will be combined into the last "Chen Yi" through the union clause. Once again to Huang er recursion, find Li Si, and then Li Si recursion to find Zhao Liu, Zhao Liu recursion can not find the next data, so the recursion of this branch ends.

The statement for recursive cte is as follows:

WITH recursive fuxi AS (SELECT * FROM fork WHERE `name` = 'chenyi' UNION SELECT f.* FROM fork f JOIN fuxi a WHERE f.id=a.father) SELECT * FROM fuxi

The evolution results are as follows:

First execute the statement in the anchor part to get the anchor member, that is, the first row of the result set in the result:

Based on the anchor member, start executing the recursive statement:

When recursive, the id=2 result is obtained by filtering according to the conditions of f.id=a.father, which is combined with the previous data through union as the data source fuxi for the next recursion.

Do a second recursion:

The third recursion:

Since the father value of id=6 is null after the third recursion, the result of the fourth recursion is empty, so the recursion ends after the fourth.

2.2 Recursive CTE example (2)

The main purpose of the CTE example is to demonstrate the field name when switching recursion.

For example, there are several bus stops whose interoperability is shown in the following figure:

The corresponding tables are:

CREATE OR REPLACE TABLE bus_routes (src char (50), dst char (50)); INSERT INTO bus_routes VALUES ('stopA','stopB'), (' stopB','stopA'), ('stopA','stopC'), (' stopC','stopB'), ('stopC','stopD'); MariaDB [test] > select * from bus_routes +-+ | src | dst | +-+-+ | stopA | stopB | | stopB | stopA | | stopA | stopC | | stopC | stopB | | stopC | stopD | +-+-+

To calculate which sites can be reached with stopA as the starting point, the recursive CTE is as follows:

WITH recursive dst_stop AS (SELECT src AS dst FROM bus_routes WHERE src='stopA' / * note: src as dst * / UNION SELECT b.dst FROM bus_routes b JOIN dst_stop d WHERE d.dst=b.src) SELECT * FROM dst_stop

The results are as follows:

+-+ | dst | +-+ | stopA | | stopB | | stopC | | stopD | +-+

First execute the anchor statement to get the anchor member stopA, and the field name is dst.

Then perform the first recursion between the anchor member result and the bus_ routes table join, as shown in the following figure:

Do a second recursion:

The third recursion is performed, but during the third recursion, stopD cannot find the corresponding record, so the recursion ends.

2.2 Recursive CTE example (3)

It's still a bus map:

Calculate which sites can be reached with stopA as the starting point, and give a roadmap. For example: stopA-- > stopC-- > stopD.

The following is a recursive CTE statement:

WITH recursive bus_path (bus_path,bus_dst) AS (SELECT src,src FROM bus_routes WHERE src='stopA' UNION SELECT CONCAT (b2. Bussiness path), b1.dst), b1.dst FROM bus_routes b1 JOIN bus_path b2 WHERE b2.bus_dst = b1.src AND LOCATE (b1.dstrect b2.busroompath) = 0) SELECT * FROM bus_path

First get the starting point stopA, then get its target stopB and stopC, and connect the starting point to the target using the "-->" connection, that is, concat (src, "-->", "dst"). Then get their targets according to stopB and stopC. The target for stopC is stopD and the target for stopB,stopB is stopA. If the connection is successful, the route is:

StopA-- > stopB-- > stopA Target: stopAstopA-- > stopC-- > stopD Target: stopDstopA-- > stopC-- > stopB Target: stopB

This will be recursive indefinitely, so we have to judge when to end the recursion. The method of judgment is that the target is not allowed to appear in the route, as long as it appears, it means that the route will be calculated repeatedly.

After reading the above, do you have any further understanding of the common table expression CTE in the MariaDB table? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.