MySQL 再帰 CTE (共通テーブル式)

MySQL Recursive CTE を使用すると、ユーザーは再帰操作を含むクエリを作成できます。再帰的 CTE は、再帰的に定義される式です。これは、階層データ、グラフの走査、データの集計、およびデータのレポート作成に役立ちます。この記事では、再帰的 CTE、その構文、例について説明します。

＃＃＃導入＃＃＃

共通テーブル式 (CTE) は、MySQL の各クエリによって生成される一時的な結果セットに名前を付ける方法です。 WITH 句は CTE の定義に使用され、この句を使用して 1 つのステートメントで複数の CTE を定義できます。ただし、CTE は、同じ WITH 句で以前に定義された他の CTE のみを参照できます。各 CTE のスコープは、それを定義するステートメントに制限されます。

再帰 CTE は、それ自体を独自の名前で参照するサブクエリです。再帰 CTE を定義するには、WITH RECURSIVE 句を使用する必要があり、終了条件が必要です。再帰的 CTE は、シーケンスを生成し、階層またはツリー構造のデータを走査するためによく使用されます。

＃＃＃文法＃＃＃

MySQL で再帰的 CTE を定義するための構文は次のとおりです:

リーリー

`cte_name`: サブクエリ ブロックに記述された再帰サブクエリに指定された名前。

• `col1、col2、...、colN`: サブクエリによって生成された列に指定された名前。

• "Subquery": "cte_name" を名前として使用してそれ自体を参照する MySQL クエリ。 SELECT ステートメントで指定された列名は、リストで指定された名前の後に「cte_name」が続いたものと一致する必要があります。

• サブクエリ ブロックで提供される再帰 CTE 構造

  リーリー
再帰的 CTE には、非再帰的サブクエリの後に再帰的サブクエリが続きます。

最初の SELECT ステートメントは非再帰ステートメントです。結果セットの最初の行を提供します。

• `UNION [ALL, DISTINCT]` は、前の結果セットに行を追加するために使用されます。 「ALL」および「DISTINCT」キーワードを使用して、最後の結果セットに重複行を追加または削除します。

• 2 番目の SELECT ステートメントは再帰ステートメントです。 WHERE 句で指定された条件が true になるまで、結果セットを繰り返し構築します。

• 各反復によって生成された結果セットは、前の反復によって生成された結果セットに基づいています。

• 再帰的 SELECT ステートメントによって追加の行が生成されなくなると、再帰は終了します。

• 例 1

「employees」という名前のテーブルについて考えてみましょう。 「id」、「name」、「salary」の列があります。少なくとも 2 年以上勤務している従業員の平均給与を求めます。 「従業員」テーブルには次の値があります:

#id ＃＃＃＃＃＃名前＃＃＃＃＃＃ ＃＃＃＃＃＃給料＃＃＃＃＃＃ 3アリスマイケルサラ110000120000

1

ジョン

50000

2

＃＃＃＃＃＃単純＃＃＃＃＃＃

60000

＃＃＃＃＃＃ボブ＃＃＃＃＃＃

70000

4

80000

5

90000

6

100000

＃＃＃＃＃＃7＃＃＃＃＃＃

デビッド

8

エミリー

9

＃＃＃＃＃＃マーク＃＃＃＃＃＃

130000

10

＃＃＃＃＃＃ジュリア＃＃＃＃＃＃

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中编写复杂的查询。

以上がMySQL 再帰 CTE (共通テーブル式)の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。