Various usages and common examples of WITH in mysql

Preface

In MySQL,WITHStatements (or Common Table Expressions, CTE for short) are used to define a temporary result set that can be repeatedly referenced in other parts of the query. It is usually used in complex queries, which facilitates decomposing the query logic into multiple parts, making the code clearer, and the intermediate results can be reused.

MySQL supports two types of CTEs:

• Non-recursive CTE: BasicWITHStatement, used to define a result set for one-time calculations.
• Recursive CTE: CTE refers to itself, usually used for queries of hierarchical data or tree structures.

The following are their usage and some common examples.

1. Non-recursive CTE

Non-recursive CTE defines a fixed result set in the query and will not change after execution. The syntax is as follows:

WITH cte_name AS ( SELECT ... ) SELECT * FROM cte_name;

Example 1: Calculate the average salary of employees in the department

Suppose there is oneemployeesTable, including employeesdepartment_id、nameandsalary。

WITH dept_avg_salary AS ( SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id ) SELECT , , d.avg_salary FROM employees e JOIN dept_avg_salary d ON e.department_id = d.department_id WHERE  > d.avg_salary;

This query is first usedWITHCalculate the average salary of each department (dept_avg_salary) and then find out the employees whose wages are higher than the department’s average salary.

Example 2: Split query by condition

Assuming you want to find the 5 salespeople with the highest sales, you can use CTE for temporary rankings:

WITH ranked_sales AS ( SELECT name, sales_amount, ROW_NUMBER() OVER (ORDER BY sales_amount DESC) AS rank FROM sales_team ) SELECT name, sales_amount FROM ranked_sales WHERE rank <= 5;

CTE ranked_salesA sales record table with ranking is generated, and the main query extracts the top five from it.

2. Recursive CTE

Recursive CTE allows reference to itself when defined and is often used for queries in hierarchical structures, such as management hierarchies, tree structures, etc. The syntax is as follows:

WITH RECURSIVE cte_name AS ( SELECT ... -- Initial query UNION ALL SELECT ... FROM cte_name -- Recursive query ) SELECT * FROM cte_name;

Example 3: Calculate the factorial

Here is a recursive CTE example that calculates factorials from 1 to 5.

WITH RECURSIVE factorial_cte AS ( SELECT 1 AS n, 1 AS factorial UNION ALL SELECT n + 1, factorial * (n + 1) FROM factorial_cte WHERE n < 5 ) SELECT * FROM factorial_cte;

This CTE first definesn=1andfactorial=1The initial value of  and then recursively calculate factorials from 1 to 5.

Example 4: Query the hierarchy of the department

Suppose there is onedepartmentsTable, each department has oneidandparent_id(Point to the superior department). A recursive CTE can query all sub-departments starting with a certain department.

WITH RECURSIVE dept_hierarchy AS ( SELECT id, name, parent_id FROM departments WHERE id = 1 -- From the root department ID for 1 start UNION ALL SELECT , , d.parent_id FROM departments d JOIN dept_hierarchy h ON d.parent_id =  ) SELECT * FROM dept_hierarchy;

3. Nested CTE and Multi-CTE Definitions

Multiple CTEs can be defined in one query and referenced in other parts of the query. These CTEs can be referenced to each other and processed in order.

Example 5: Nested Query for Multiple CTEs

Assuming that you want to query the intermediate calculation results of a set of data, you can use a nested CTE:

WITH initial_sales AS ( SELECT salesperson_id, SUM(sales_amount) AS total_sales FROM sales GROUP BY salesperson_id ), ranked_sales AS ( SELECT salesperson_id, total_sales, RANK() OVER (ORDER BY total_sales DESC) AS sales_rank FROM initial_sales ) SELECT salesperson_id, total_sales, sales_rank FROM ranked_sales WHERE sales_rank <= 10;

here,initial_salesCalculate the total sales of each salesperson.ranked_salesRank sales and then search for the top ten sales personnel.

4. Use CTE to simplify complex query logic

Example 6: Step-by-step calculation of complex queries

Suppose there is an e-commerce order system that requires statistics on the sales and growth rate of each product per month.

WITH monthly_sales AS ( SELECT product_id, DATE_FORMAT(order_date, '%Y-%m') AS month, SUM(sales_amount) AS total_sales FROM orders GROUP BY product_id, month ), sales_growth AS ( SELECT m1.product_id, , m1.total_sales, (m1.total_sales - m2.total_sales) / m2.total_sales AS growth_rate FROM monthly_sales m1 LEFT JOIN monthly_sales m2 ON m1.product_id = m2.product_id AND DATE_FORMAT(DATE_SUB(, INTERVAL 1 MONTH), '%Y-%m') =  ) SELECT * FROM sales_growth;

This query uses two CTEs:monthly_salesCalculate the total sales per month,sales_growthCalculate monthly sales growth rate.

Summarize

WITHThe various usages of the statement are summarized as follows:

• Non-recursive CTEUsed to decompose complex queries.
• Recursive CTEUsed for hierarchical data query.
• Nested CTEQueries with multiple steps can be combined.
• Simplify query logic: Decompose complex SQL logic to make queries clearer and easier to understand.

CTE is an indispensable tool in complex queries that helps make the code concise and easy to maintain.

WITH RECURSIVE For example, the table structure is the pointer of id and pid. There are about five layers.

Example: Recursive query department level

Suppose there is onedepartmentsThe table, structure is as follows:

• id: Department ID
• name: Department name
• pid: Parent department ID (top departmentpidforNULL）

There are five layers of nested department data in the table:

Recursive CTE query: Get all subordinate departments of the specified department

We can use recursive CTE from the root department (e.g. at the company levelid=1) Start, query all sub-departments and display hierarchical relationships.

wITH RECURSIVE dept_hierarchy AS ( -- Initial query，Get top departments（Here we id=1 The company started） SELECT id, name, pid, 1 AS level FROM departments WHERE id = 1 -- Here you can change it to the root department you want to query ID UNION ALL -- Recursive query：Find the superior department（Parent）Next level department SELECT , , ,  + 1 AS level FROM departments d JOIN dept_hierarchy h ON  =  ) SELECT * FROM dept_hierarchy; 

Query results explanation

This recursive CTE is divided into two parts:

• Initial query：SELECT id, name, pid, 1 AS level, start with the specified department (id=1), set its level to 1.
• Recursive query: From the superior departmentid(Right now) set out, find all its subordinate departments, andlevelAdd 1, so that the hierarchy relationship will grow recursively until there are no subordinate departments.

After execution, the results show the hierarchical relationship of the department:

In this query,levelColumns represent the level of the department, incrementing from 1.

Summarize

This is the end of this article about the various usages and common examples of WITH in mysql. For more related examples of usage of WITH, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!