CTE récursif MySQL (expression de table commune)

MySQL Recursive CTE permet aux utilisateurs d'écrire des requêtes impliquant des opérations récursives. Un CTE récursif est une expression définie de manière récursive. Il est utile pour les données hiérarchiques, le parcours de graphiques, l'agrégation de données et le reporting de données. Dans cet article, nous discuterons du CTE récursif, de sa syntaxe et des exemples.

Présentation

Common Table Expression (CTE) est un moyen de nommer l'ensemble de résultats temporaires généré par chaque requête dans MySQL. La clause WITH est utilisée pour définir les CTE, et vous pouvez utiliser cette clause pour définir plusieurs CTE dans une seule instruction. Cependant, un CTE ne peut faire référence qu'à d'autres CTE préalablement définis dans la même clause WITH. La portée de chaque CTE est limitée à la déclaration qui la définit.

Un CTE récursif est une sous-requête qui se réfère à elle-même par son propre nom. Pour définir un CTE récursif, la clause WITH RECURSIVE doit être utilisée et elle doit avoir une condition de fin. Le CTE récursif est souvent utilisé pour générer des séquences et parcourir des données hiérarchiques ou arborescentes.

Grammaire

La syntaxe pour définir le CTE récursif dans MySQL est la suivante :

WITH RECURSIVE cte_name [(col1, col2, ...)]
AS (subquery)
SELECT col1, col2, ... FROM cte_name;

• `cte_name` : Le nom spécifié pour la sous-requête récursive écrite dans le bloc de sous-requête.

• `col1, col2, ..., colN` : noms spécifiés pour les colonnes générées par la sous-requête.

• "Sous-requête" : Une requête MySQL qui se réfère à elle-même en utilisant "cte_name" comme nom. Le nom de colonne donné dans l'instruction SELECT doit correspondre au nom fourni dans la liste, suivi de "cte_name".

Structure CTE récursive fournie dans le bloc de sous-requête

SELECT col1, col2, ..., colN FROM table_name
UNION [ALL, DISTINCT]
SELECT col1, col2, ..., colN FROM cte_name
WHERE clause

Un CTE récursif a une sous-requête non récursive suivie d'une sous-requête récursive.

• La première instruction SELECT est une instruction non récursive. Il fournit les lignes initiales du jeu de résultats.

• `UNION [ALL, DISTINCT]` est utilisé pour ajouter des lignes supplémentaires à l'ensemble de résultats précédent. Utilisez les mots-clés "ALL" et "DISTINCT" pour ajouter ou supprimer des lignes en double du dernier jeu de résultats.

• La deuxième instruction SELECT est une instruction récursive. Il construit de manière itérative le jeu de résultats jusqu'à ce que la condition fournie dans la clause WHERE soit vraie.

• L'ensemble de résultats produit par chaque itération est basé sur l'ensemble de résultats produit par l'itération précédente.

• La récursion se termine lorsque l'instruction SELECT récursive ne produit aucune ligne supplémentaire.

Exemple 1

Considérez un tableau appelé « employés ». Il comporte des colonnes « id », « nom » et « salaire ». Trouvez le salaire moyen des salariés qui sont dans l’entreprise depuis au moins 2 ans. Le tableau "employés" a les valeurs suivantes :

id	Nom	Salaire
1	Jean	50000
2	Jane	60000
3	Bob	70000
4	Alice	80000
5	Michael	90000
6	Sarah	100000
7	David	110000
8	Emily	120000
9	étiquette	130000
10	Julia	140000

因此，下面给出了所需的查询

WITH RECURSIVE employee_tenure AS (
   SELECT id, name, salary, hire_date, 0 AS tenure
   FROM employees
   UNION ALL
   SELECT e.id, e.name, e.salary, e.hire_date, et.tenure + 1
   FROM employees e
   JOIN employee_tenure et ON e.id = et.id
   WHERE et.hire_date < DATE_SUB(NOW(), INTERVAL 2 YEAR)
)
SELECT AVG(salary) AS average_salary
FROM employee_tenure
WHERE tenure >= 2;

在此查询中，我们首先定义一个名为“employee_tenure”的递归 CTE。它通过将“员工”表与 CTE 本身递归连接来计算每个员工的任期。递归的基本情况从“员工”表中选择所有员工，起始任期为 0。递归情况将每个员工与 CTE 连接起来，并将其任期增加 1。

生成的“employee_tenure”CTE 包含“id”、“name”、“salary”、“hire_date”和“tenure”列。然后我们选择任期至少2年的员工的平均工资。它使用一个带有 WHERE 子句的简单 SELECT 语句来过滤掉任期小于 2 的员工。

查询的输出将是一行。它将包含在公司工作至少 2 年的员工的平均工资。具体值取决于“员工”表中分配给每个员工的随机工资。

示例 2

下面是在 MySQL 中使用递归 CTE 生成一系列前 5 个奇数的示例：

查询

WITH RECURSIVE 
odd_no (sr_no, n) AS
(
   SELECT 1, 1 
   UNION ALL
   SELECT sr_no+1, n+2 FROM odd_no WHERE sr_no < 5 
)
SELECT * FROM odd_no;  

输出

sr_no	n
1	1
2	3
3	5
4	7
5	9

上面的查询由两部分组成——非递归和递归。

非递归部分 - 它将生成由名为“sr_no”和“n”的两列和一行组成的初始行。

查询

SELECT 1, 1

输出

sr_no	n
1	1

递归部分 - 它将向先前的输出添加行，直到满足终止条件，在本例中是当 sr_no 小于 5 时。

SELECT sr_no+1, n+2 FROM odd_no WHERE sr_no < 5 

当`sr_no`变为5时，条件变为假，递归终止。

结论

MySQL Recursive CTE 是一种递归定义的表达式，在分层数据、图形遍历、数据聚合和数据报告中很有用。递归 CTE 使用自己的名称引用自身，并且必须有终止条件。定义递归 CTE 的语法涉及使用WITH RECURSIVE 子句以及非递归和递归子查询。在本文中，我们讨论了递归 CTE 的语法和示例，包括使用递归 CTE 查找在公司工作至少 2 年的员工的平均工资，并生成一系列前 5 个奇数。总的来说，Recursive CTE是一个强大的工具，可以帮助用户在MySQL中编写复杂的查询。

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!