MySQL rekursif CTE (ungkapan jadual biasa)

MySQL Recursive CTE membolehkan pengguna menulis pertanyaan yang melibatkan operasi rekursif. CTE rekursif ialah ungkapan yang ditakrifkan secara rekursif. Ia berguna dalam data hierarki, traversal graf, pengagregatan data dan pelaporan data. Dalam artikel ini, kita akan membincangkan CTE rekursif, sintaks dan contohnya.

Pengenalan

Ungkapan Jadual Biasa (CTE) ialah cara menamakan set hasil sementara yang dijana oleh setiap pertanyaan dalam MySQL. Klausa WITH digunakan untuk mentakrifkan CTE dan anda boleh menggunakan klausa ini untuk mentakrifkan berbilang CTE dalam satu pernyataan. Walau bagaimanapun, CTE hanya boleh merujuk kepada CTE lain yang ditakrifkan sebelum ini dalam klausa WITH yang sama. Skop setiap CTE adalah terhad kepada pernyataan yang mentakrifkannya.

CTE rekursif ialah subkueri yang merujuk kepada dirinya sendiri dengan namanya sendiri. Untuk menentukan CTE rekursif, klausa WITH RECURSIVE perlu digunakan dan ia mesti mempunyai syarat penamatan. CTE rekursif sering digunakan untuk menjana jujukan dan melintasi data hierarki atau berstruktur pokok.

Tatabahasa

Sintaks untuk mentakrifkan CTE rekursif dalam MySQL adalah seperti berikut:

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

• `cte_name`: Nama yang ditentukan untuk subquery rekursif yang ditulis dalam blok subquery.

• `col1, col2, ..., colN`: Nama yang ditentukan untuk lajur yang dijana oleh subkueri.

• "Subquery": Pertanyaan MySQL yang merujuk kepada dirinya sendiri menggunakan "cte_name" sebagai namanya. Nama lajur yang diberikan dalam pernyataan SELECT hendaklah sepadan dengan nama yang disediakan dalam senarai, diikuti dengan "cte_name".

Struktur CTE rekursif disediakan dalam blok subquery

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

CTE rekursif mempunyai subkueri bukan rekursif diikuti dengan subkueri rekursif.

• Pernyataan SELECT pertama ialah pernyataan bukan rekursif. Ia menyediakan baris awal untuk set hasil.

• `UNION [ALL, DISTINCT]` digunakan untuk menambah baris tambahan pada set hasil sebelumnya. Gunakan kata kunci "SEMUA" dan "DISTINCT" untuk menambah atau mengalih keluar baris pendua daripada set hasil terakhir.

• Pernyataan SELECT kedua ialah pernyataan rekursif. Ia secara berulang membina set hasil sehingga syarat yang diberikan dalam klausa WHERE adalah benar.

• Set keputusan yang dihasilkan oleh setiap lelaran adalah berdasarkan set hasil yang dihasilkan oleh lelaran sebelumnya.

• Rekursi tamat apabila pernyataan SELECT rekursif tidak menghasilkan sebarang baris tambahan.

Contoh 1

Pertimbangkan jadual yang dipanggil "pekerja". Ia mempunyai lajur "id", "nama" dan "gaji". Cari purata gaji pekerja yang telah berkhidmat dengan syarikat sekurang-kurangnya 2 tahun. Jadual "pekerja" mempunyai nilai berikut:

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

Atas ialah kandungan terperinci MySQL rekursif CTE (ungkapan jadual biasa). Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!