Dalam SQL, klausa WITH juga dikenali sebagai CTE. Ia adalah ciri berkuasa yang membolehkan kami membuat set hasil sementara dalam pertanyaan. Satu peranan utama CTE ialah memudahkan pertanyaan kompleks kepada subkueri yang lebih kecil dan boleh digunakan semula. Ini membantu menjadikan kod lebih mudah dibaca dan diselenggara dalam jangka masa panjang.
Sertai kami dalam tutorial ini sambil kami meneroka cara kerja Ungkapan Jadual Biasa menggunakan klausa WITH dan kefungsian yang disokong.
Keperluan:
Untuk tujuan demonstrasi, kami akan menggunakan yang berikut:
- MySQL versi 8.0 dan ke atas
- pangkalan data sampel Sakila
Dengan keperluan yang diberikan dipenuhi, kami boleh meneruskan untuk mengetahui lebih lanjut tentang CTE dan klausa WITH.
SQL DENGAN Klausa
Klausa WITH membenarkan kami mentakrifkan satu atau lebih set hasil sementara yang dikenali sebagai Ungkapan Jadual Biasa.
Kami boleh merujuk CTE yang terhasil dalam pertanyaan utama seperti mana-mana jadual atau set hasil lain. Ini memainkan peranan penting dalam mencipta pertanyaan SQL modular.
Walaupun sintaks CTE mungkin berbeza sedikit bergantung pada keperluan anda, yang berikut menunjukkan sintaks asas CTE dalam SQL:
DENGAN nama_cte (lajur1, lajur2, ...) AS (
-- Pertanyaan CTE
PILIH...
DARI...
DI MANA...
)
-- Pertanyaan utama
PILIH...
DARI...
SERTAI cte_name PADA ...
DI MANA...
Kami bermula dengan kata kunci WITH yang memberitahu pangkalan data SQL yang kami ingin buat dan gunakan CTE.
Seterusnya, kami menentukan nama untuk CTE yang membolehkan kami merujuknya dalam pertanyaan lain.
Kami juga menentukan senarai pilihan nama lajur jika CTE termasuk alias lajur.
Seterusnya, kami meneruskan untuk menentukan pertanyaan CTE. Ini mengandungi semua tugas atau data yang CTE jalankan yang disertakan dalam sepasang kurungan.
Akhir sekali, kami menentukan pertanyaan utama yang merujuk kepada CTE.
Contoh Penggunaan:
Salah satu cara terbaik untuk memahami cara menggunakan dan bekerja dengan CTE ialah dengan melihat contoh praktikal.
Ambil contoh pangkalan data sampel Sakila. Katakan kami ingin mencari 10 pelanggan teratas dengan bilangan penyewaan tertinggi.
Lihat CTE yang ditunjukkan berikut.
Menggunakan Klausa SQL WITH untuk mencari 10 pelanggan teratas dengan kiraan sewaan tertinggi:
DENGAN CustomerRentals AS (PILIH c.customer_id, c.first_name, c.last_name, COUNT(r.rental_id) AS rental_count
DARIPADA pelanggan c
SERTAI sewa r ON c.customer_id = r.customer_id
KUMPULAN OLEH c.customer_id, c.first_name, c.last_name
)
PILIH *
DARIPADA CustomerRentals
PESANAN MENGIKUT rental_count DESC
HAD 10;
Dalam contoh yang diberikan, kami mulakan dengan mentakrifkan CTE baharu menggunakan kata kunci WITH diikuti dengan nama yang ingin kami tetapkan kepada CTE. Dalam kes ini, kami memanggilnya 'CustomerRentals'.
Di dalam badan CTE, kami mengira kiraan sewa untuk setiap pelanggan dengan menyertai jadual pelanggan dan sewaan.
Akhir sekali, dalam pertanyaan utama, kami memilih semua lajur daripada CTE, memesan keputusan berdasarkan kiraan sewaan (tertib menurun) dan mengehadkan output kepada hanya 10 baris teratas.
Ini membolehkan kami mengambil pelanggan dengan bilangan sewaan tertinggi seperti yang ditunjukkan dalam output berikut:
CTE rekursif
Dalam beberapa kes lain, anda mungkin berurusan dengan struktur data hierarki. Di sinilah CTE rekursif dimainkan.
Mari kita ambil contoh kes di mana kita ingin menavigasi organisasi hierarki atau untuk mewakili struktur seperti pokok. Kita boleh menggunakan kata kunci WITH RECURSIVE untuk mencipta CTE rekursif.
Oleh kerana tiada data hierarki yang boleh kita gunakan dalam pangkalan data Sakila untuk menunjukkan CTE rekursif, mari kita sediakan contoh asas.
CIPTA JADUAL jabatan (department_id INT PRIMARY KUNCI AUTO_INCREMENT,
nama_jabatan VARCHAR(255) BUKAN NULL,
parent_department_id INT,
KUNCI ASING (id_jabatan_ibu bapa) RUJUKAN jabatan(id_jabatan)
);
INSERT INTO department (department_name, parent_department_id)
NILAI
('Korporat', NULL),
('Kewangan', 1),
('HR', 1),
('Perakaunan', 2),
'Merekrut', 3),
('Gaji', 4);
Dalam kes ini, kami mempunyai sampel jadual 'jabatan' dengan beberapa data rawak. Untuk mencari struktur hierarki jabatan, kita boleh menggunakan CTE rekursif seperti berikut:
DENGAN RECURSIVE DepartmentHierarki AS (PILIH id_jabatan, nama_jabatan, id_jabatan_ibu bapa
DARI jabatan
DI MANA parent_department_id IS NULL
KESATUAN SEMUA
PILIH d.department_id, d.department_name, d.parent_department_id
DARI jabatan d
SERTAI JabatanHierarki dh HIDUP d.id_jabatan_ibu bapa = dh.id_jabatan
)
PILIH *
DARI JabatanHierarki;
Dalam kes ini, CTE rekursif bermula dengan jabatan yang mempunyai NULL 'id_department_ibu bapa' (jabatan akar) dan secara rekursif mendapatkan semula jabatan anak.
Kesimpulan
Dalam tutorial ini, kami mempelajari tentang ciri yang paling asas dan berguna dalam pangkalan data SQL seperti Ungkapan Jadual Biasa dengan memahami cara bekerja dengan kata kunci WITH.