MySQL 재귀 CTE(공통 테이블 표현식)

MySQL Recursive CTE를 사용하면 사용자는 재귀 작업과 관련된 쿼리를 작성할 수 있습니다. 재귀적 CTE는 재귀적으로 정의된 표현식입니다. 계층적 데이터, 그래프 순회, 데이터 집계 및 데이터 보고에 유용합니다. 이 기사에서는 재귀 CTE와 해당 구문 및 예제에 대해 설명합니다.

소개

공통 테이블 표현식(CTE)은 MySQL의 각 쿼리에 의해 생성된 임시 결과 집합의 이름을 지정하는 방법입니다. WITH 절은 CTE를 정의하는 데 사용되며, 이 절을 사용하여 단일 문에서 여러 CTE를 정의할 수 있습니다. 그러나 CTE는 동일한 WITH 절에서 이전에 정의된 다른 CTE만 참조할 수 있습니다. 각 CTE의 범위는 이를 정의하는 문으로 제한됩니다.

재귀적 CTE는 자체 이름으로 자신을 참조하는 하위 쿼리입니다. 재귀적 CTE를 정의하려면 WITH RECURSIVE 절을 사용해야 하며 종료 조건이 있어야 합니다. 재귀 CTE는 시퀀스를 생성하고 계층적 또는 트리 구조 데이터를 탐색하는 데 자주 사용됩니다.

문법

MySQL에서 재귀 CTE를 정의하는 구문은 다음과 같습니다.

으아악

• `cte_name`: 하위 쿼리 블록에 작성된 재귀 하위 쿼리에 대해 지정된 이름입니다.

• `col1, col2, ..., colN`: 하위 쿼리에 의해 생성된 열에 지정된 이름입니다.

• "하위 쿼리": "cte_name"을 이름으로 사용하여 자신을 참조하는 MySQL 쿼리입니다. SELECT 문에 제공된 열 이름은 목록에 제공된 이름과 일치해야 하며 그 뒤에 "cte_name"이 와야 합니다.

하위 쿼리 블록에 제공되는 재귀적 CTE 구조

으아악

재귀적 CTE에는 비재귀적 하위 쿼리와 그 뒤에 재귀적 하위 쿼리가 있습니다.

• 첫 번째 SELECT 문은 비재귀 문입니다. 결과 집합의 초기 행을 제공합니다.

• `UNION [ALL, DISTINCT]`는 이전 결과 집합에 추가 행을 추가하는 데 사용됩니다. 마지막 결과 세트에서 중복 행을 추가하거나 제거하려면 "ALL" 및 "DISTINCT" 키워드를 사용하십시오.

• 두 번째 SELECT 문은 재귀 문입니다. WHERE 절에 제공된 조건이 true가 될 때까지 결과 집합을 반복적으로 작성합니다.

• 각 반복에서 생성된 결과 집합은 이전 반복에서 생성된 결과 집합을 기반으로 합니다.

• 재귀 SELECT 문이 추가 행을 생성하지 않으면 재귀가 종료됩니다.

예 1

"직원"이라는 테이블을 생각해 보세요. 여기에는 "id", "name" 및 "salary" 열이 있습니다. 회사에 최소 2년 이상 근무한 직원의 평균 급여를 구하세요. "employees" 테이블에는 다음과 같은 값이 있습니다:

id	이름	급여
1	존	50000
2	제인	60000
3	밥	70000
4	앨리스	80000
5	마이클	90000
6	사라	100000
7	데이비드	110000
8	에밀리	120000
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 중국어 웹사이트의 기타 관련 기사를 참조하세요!