Simple usage example of MySQL 8.0 recursive query
PrefaceThis article uses the new features of MySQL 8.0 to implement recursive queries. Detailed example code is given in the article. Let's take a look at the detailed introduction. Mysql8.0 recursive query usageThe table data is as follows
1. We need to find all subcategories under the "clothing" category with recursive type_cte as (
select * from t_category where cat_id = 4
union all
select t.* from t_category t
inner join type_cte type_cte2 on t.parent_cid = type_cte2.cat_id
)
select
cat_id, name, parent_cid
from type_cte
2. Query all subcategories under the "Beauty" category, and the category name includes the name of the parent category with recursive type_cte as (
select cat_id,name,parent_cid from t_category where cat_id = 12
union all
select t.cat_id,concat(type_cte2.name,'>',t.name),t.parent_cid
from t_category t
inner join type_cte type_cte2 on t.parent_cid = type_cte2.cat_id
)
select
cat_id, name, parent_cid
from type_cte;
3. Query all parent categories of a category Just adjust the SQL according to the second question. with recursive type_cte as (
select cat_id,name,parent_cid from t_category where cat_id = 40
union all
select t.cat_id,concat(type_cte2.name,'>',t.name),t.parent_cid
from t_category t
inner join type_cte type_cte2 on t.cat_id = type_cte2.parent_cid
)
select
cat_id, name, parent_cid
from type_cte;
SummarizeThis is the end of this article about MySQL 8.0 recursive query. For more relevant MySQL 8.0 recursive query content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future! You may also be interested in:
|
<<: How to solve the background tiling and border breaking of CSS style div or li in IE6
>>: How to use positioning to center elements (web page layout tips)
Recommend
Understand the principles of MySQL persistence and rollback in one article
Table of contents redo log Why do we need to upda...
A brief discussion on DDL and DML in MySQL
Table of contents Preface 1. DDL 1.1 Database Ope...
MySQL practical skills: analysis of methods to compare whether two tables have different data
This article uses an example to describe how MySQ...
The JS hasOwnProperty() method detects whether a property is an object's own property.
The JavaScript hasOwnProperty() method is the pro...
js to achieve simple image drag effect
This article shares the specific code of js to ac...
Usage and demonstration of ref in Vue
ref definition: used to register reference inform...
MySQL stored procedure method example of returning multiple values
This article uses an example to describe how to r...
Solution to ERROR 1366 when entering Chinese in MySQL
The following error occurs when entering Chinese ...
How to solve the problem of not getting form value after submitting html form input using disabled
After the form input box input is set to the disa...
Vue elementUI implements tree structure table and lazy loading
Table of contents 1. Achieve results 2. Backend i...
Take you to understand the event scheduler EVENT in MySQL
The event scheduler in MySQL, EVENT, is also call...
CSS Tutorial: CSS Attribute Media Type
One of the most important features of a style she...
HTML user registration page settings source code
Design the web page shown above: <!DOCTYPE htm...
Detailed explanation of publicPath usage in Webpack
Table of contents output output.path output.publi...
In-depth understanding of the use of the infer keyword in typescript
Table of contents infer Case: Deepen your underst...