Ekspresi Jadual Biasa Rekursif (CTE) adalah alat yang berkuasa dalam SQL yang digunakan untuk mengendalikan struktur data hierarki seperti carta organisasi, sistem fail, atau pokok kategori. Berikut adalah panduan langkah demi langkah mengenai cara menggunakannya:
Tentukan Ahli Anchor: Bahagian pertama CTE Rekursif adalah ahli Anchor, yang mentakrifkan titik permulaan rekursi. Ini adalah pertanyaan bukan rekursif yang mengembalikan satu set baris awal.
<code class="sql">WITH RECURSIVE EmployeeHierarchy AS ( SELECT id, name, manager_id, 0 AS level FROM Employees WHERE manager_id IS NULL -- Start from the top level (eg, CEO)</code>
Tentukan ahli rekursif: Mengikuti ahli utama, ahli rekursif mentakrifkan bagaimana rekursi itu diteruskan. Ia merujuk CTE itu sendiri untuk membina baris yang dikembalikan dari lelaran sebelumnya.
UNION ALL SELECT e.id, e.name, e.manager_id, level 1 FROM Employees e INNER JOIN EmployeeHierarchy m ON e.manager_id = m.id )
Menggabungkan hasilnya: CTE rekursif terus membina sendiri sehingga tiada baris baru dihasilkan. Anda kemudian menanyakan CTE untuk mendapatkan hasil yang diinginkan.
<code class="sql">SELECT id, name, level FROM EmployeeHierarchy;</code>
Contoh ini membina hierarki pekerja bermula dari bahagian atas (di mana manager_id
adalah NULL
) dan rekursif menambah bawahan ke setiap peringkat sehingga semua pekerja dimasukkan.
Mengoptimumkan CTE rekursif melibatkan beberapa strategi untuk meningkatkan prestasi dan mengurangkan penggunaan sumber:
Hadkan kedalaman rekursi: ketahui kedalaman rekursi anda. Jika boleh, laksanakan klausa WHERE
untuk menutup kedalaman maksimum.
<code class="sql">WHERE level < 10</code>
manager_id
dan id
dalam jadual Employees
.Apabila bekerja dengan CTE rekursif, anda mungkin menghadapi beberapa jenis kesilapan. Berikut adalah beberapa isu biasa dan bagaimana menyelesaikan masalah mereka:
Gelung Infinite: Jika bahagian rekursif CTE terus merujuk dirinya tanpa keadaan berhenti, ia boleh menyebabkan gelung tak terhingga. Pastikan rekursi anda mempunyai keadaan penamatan yang jelas.
<code class="sql">WHERE level < 10</code>
UNION ALL
, dan rujukan rekursif haruslah dalam FROM
dari anggota rekursif.Walaupun CTE rekursif berkuasa untuk mengendalikan data hierarki, terdapat kaedah alternatif yang mungkin lebih sesuai bergantung pada kes penggunaan khusus anda:
Model Senarai Adjacency: Model ini menyimpan hubungan ibu bapa dan anak segera. Ia mudah tetapi mungkin memerlukan pelbagai pertanyaan atau diri sendiri untuk menavigasi hierarki.
<code class="sql">CREATE TABLE Employees ( id INT PRIMARY KEY, name VARCHAR(100), manager_id INT, FOREIGN KEY (manager_id) REFERENCES Employees(id) );</code>
Laluan Terwujud: Model ini menyimpan keseluruhan laluan dari akar ke setiap nod sebagai rentetan. Ia baik untuk mendapatkan semula laluan keseluruhan tetapi boleh menjadi kompleks dengan kemas kini yang kerap.
<code class="sql">CREATE TABLE Categories ( id INT PRIMARY KEY, name VARCHAR(100), path VARCHAR(1000) );</code>
Set bersarang: Model ini memberikan nilai kiri dan kanan kepada setiap nod, yang boleh digunakan untuk menentukan hubungan ibu bapa dan kanak-kanak dengan cekap. Ia baik untuk pertanyaan yang perlu melintasi hierarki dengan cepat tetapi boleh menjadi rumit untuk dikemas kini.
<code class="sql">CREATE TABLE Categories ( id INT PRIMARY KEY, name VARCHAR(100), lft INT, rgt INT );</code>
Jadual Penutupan: Model ini menyimpan semua hubungan nenek moyang, menjadikannya cekap untuk pertanyaan yang melibatkan laluan tetapi memerlukan lebih banyak ruang penyimpanan.
<code class="sql">CREATE TABLE EmployeeHierarchy ( ancestor INT, descendant INT, PRIMARY KEY (ancestor, descendant), FOREIGN KEY (ancestor) REFERENCES Employees(id), FOREIGN KEY (descendant) REFERENCES Employees(id) );</code>
Setiap model ini mempunyai kekuatan dan kelemahannya, dan pilihannya bergantung kepada keperluan khusus aplikasi anda, termasuk jenis pertanyaan yang perlu anda lakukan dan kekerapan perubahan data.
Atas ialah kandungan terperinci Bagaimanakah saya menggunakan CTE rekursif dalam SQL untuk data hierarki?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!