MySQL rekursiver CTE (gemeinsamer Tabellenausdruck)

MySQL Recursive CTE ermöglicht Benutzern das Schreiben von Abfragen mit rekursiven Operationen. Ein rekursiver CTE ist ein rekursiv definierter Ausdruck. Es ist nützlich bei hierarchischen Daten, beim Durchlaufen von Diagrammen, bei der Datenaggregation und bei der Datenberichterstattung. In diesem Artikel besprechen wir den rekursiven CTE, seine Syntax und Beispiele.

Einführung

Common Table Expression (CTE) ist eine Möglichkeit, die temporäre Ergebnismenge zu benennen, die von jeder Abfrage in MySQL generiert wird. Die WITH-Klausel wird zum Definieren von CTEs verwendet, und Sie können diese Klausel verwenden, um mehrere CTEs in einer einzigen Anweisung zu definieren. Ein CTE kann jedoch nur auf andere CTEs verweisen, die zuvor in derselben WITH-Klausel definiert wurden. Der Geltungsbereich jedes CTE ist auf die Anweisung beschränkt, die ihn definiert.

Ein rekursiver CTE ist eine Unterabfrage, die mit ihrem eigenen Namen auf sich selbst verweist. Um einen rekursiven CTE zu definieren, muss die WITH RECURSIVE-Klausel verwendet werden und sie muss eine Abschlussbedingung haben. Rekursiver CTE wird häufig zum Generieren von Sequenzen und zum Durchlaufen hierarchischer oder baumstrukturierter Daten verwendet.

Grammatik

Die Syntax zum Definieren von rekursivem CTE in MySQL lautet wie folgt:

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

• `cte_name`: Der für die im Unterabfrageblock geschriebene rekursive Unterabfrage angegebene Name.

• `col1, col2, ..., colN`: Namen, die für die von der Unterabfrage generierten Spalten angegeben werden.

• „Unterabfrage“: Eine MySQL-Abfrage, die auf sich selbst verweist und „cte_name“ als Namen verwendet. Der in der SELECT-Anweisung angegebene Spaltenname sollte mit dem in der Liste angegebenen Namen übereinstimmen, gefolgt von „cte_name“.

Rekursive CTE-Struktur im Unterabfrageblock bereitgestellt

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

Ein rekursiver CTE verfügt über eine nicht rekursive Unterabfrage, gefolgt von einer rekursiven Unterabfrage.

• Die erste SELECT-Anweisung ist eine nicht rekursive Anweisung. Es stellt die ersten Zeilen für die Ergebnismenge bereit.

• `UNION [ALL, DISTINCT]` wird verwendet, um zusätzliche Zeilen zum vorherigen Ergebnissatz hinzuzufügen. Verwenden Sie die Schlüsselwörter „ALL“ und „DISTINCT“, um doppelte Zeilen zum letzten Ergebnissatz hinzuzufügen oder daraus zu entfernen.

• Die zweite SELECT-Anweisung ist eine rekursive Anweisung. Die Ergebnismenge wird iterativ erstellt, bis die in der WHERE-Klausel angegebene Bedingung wahr ist.

• Der von jeder Iteration erzeugte Ergebnissatz basiert auf dem von der vorherigen Iteration erzeugten Ergebnissatz.

• Rekursion endet, wenn die rekursive SELECT-Anweisung keine zusätzlichen Zeilen erzeugt.

Beispiel 1

Stellen Sie sich eine Tabelle mit dem Namen „Mitarbeiter“ vor. Es gibt die Spalten „ID“, „Name“ und „Gehalt“. Finden Sie das Durchschnittsgehalt von Mitarbeitern, die seit mindestens 2 Jahren im Unternehmen sind. Die Tabelle „Mitarbeiter“ hat folgende Werte:

id	Name	Gehalt
1	John	50000
2	Jane	60000
3	Bob	70000
4	Alice	80000
5	Michael	90000
6	Sarah	100000
7	David	110000
8	Emily	120000
9	Tag	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中编写复杂的查询。

Das obige ist der detaillierte Inhalt vonMySQL rekursiver CTE (gemeinsamer Tabellenausdruck). Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!