Kueri rekursif menggunakan ekspresi tabel umum (Transact-SQL)

Berlaku untuk:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Sistem Platform Analitik (PDW)Database SQL di Microsoft Fabric

Ekspresi tabel umum (CTE) memberikan keuntungan signifikan untuk dapat mereferensikan dirinya sendiri, sehingga menciptakan CTE rekursif. CTE rekursif adalah CTE awal yang berulang kali dijalankan untuk mengembalikan subset data hingga kumpulan hasil lengkap diperoleh.

Kueri disebut sebagai kueri rekursif saat mereferensikan CTE rekursif. Mengembalikan data hierarkis adalah penggunaan umum kueri rekursif. Misalnya, menampilkan karyawan dalam bagan organisasi, atau data dalam skenario tagihan bahan di mana produk induk memiliki satu atau beberapa komponen dan komponen tersebut mungkin memiliki subkomponen, atau mungkin merupakan komponen, dari induk lain.

CTE rekursif dapat sangat menyederhanakan kode yang diperlukan untuk menjalankan kueri rekursif dalam SELECTpernyataan , , INSERTUPDATE, DELETE, atau CREATE VIEW . Dalam versi SQL Server sebelumnya, kueri rekursif biasanya memerlukan penggunaan tabel sementara, kursor, dan logika untuk mengontrol alur langkah-langkah rekursif. Untuk informasi selengkapnya tentang ekspresi tabel umum, lihat WITH common_table_expression.

Di Microsoft Fabric, Fabric Data Warehouse dan titik akhir analitik SQL mendukung CTE standar, berurutan, dan berlapis, tetapi bukan CTA rekursif.

Struktur CTE rekursif

Struktur CTE rekursif dalam Transact-SQL mirip dengan rutinitas rekursif dalam bahasa pemrograman lainnya. Meskipun rutinitas rekursif dalam bahasa lain mengembalikan nilai skalar, CTE rekursif dapat mengembalikan beberapa baris.

CTE rekursif terdiri dari tiga elemen:

Pemanggilan rutinitas.

Pemanggilan pertama CTE rekursif terdiri dari satu atau beberapa definisi kueri CTE yang digabungkan oleh UNION ALLoperator , UNION, EXCEPT, atau INTERSECT . Karena definisi kueri ini membentuk kumpulan hasil dasar struktur CTE, definisi tersebut disebut sebagai anggota jangkar.

Definisi kueri CTE dianggap sebagai anggota jangkar kecuali mereka mereferensikan CTE itu sendiri. Semua definisi kueri anggota jangkar harus diposisikan sebelum definisi anggota rekursif pertama, dan UNION ALL operator harus digunakan untuk bergabung dengan anggota jangkar terakhir dengan anggota rekursif pertama.
Pemanggilan rutinitas rekursif.

Pemanggilan rekursif mencakup satu atau beberapa definisi kueri CTE yang bergabung dengan UNION ALL operator yang mereferensikan CTE itu sendiri. Definisi kueri ini disebut sebagai anggota rekursif.
Pemeriksaan penghentian.

Pemeriksaan penghentian bersifat implisit; rekursi berhenti ketika tidak ada baris yang dikembalikan dari pemanggilan sebelumnya.

Note

CTE rekursif yang salah disusun dapat menyebabkan perulangan tak terbatas. Misalnya, jika definisi kueri anggota rekursif mengembalikan nilai yang sama untuk kolom induk dan anak, perulangan tak terbatas dibuat. Saat menguji hasil kueri rekursif, Anda dapat membatasi jumlah tingkat rekursi yang diizinkan untuk pernyataan tertentu dengan menggunakan MAXRECURSION petunjuk dan nilai antara 0 dan 32.767 dalam OPTION klausa INSERTpernyataan , , UPDATE, DELETEatau SELECT .

Untuk informasi selengkapnya, lihat:

Pseudocode dan semantik

Struktur CTE rekursif harus berisi setidaknya satu anggota jangkar dan satu anggota rekursif. Pseudocode berikut menunjukkan komponen CTE rekursif sederhana yang berisi satu anggota jangkar dan anggota rekursif tunggal.

WITH cte_name ( column_name [ ,...n ] )
AS
(
    CTE_query_definition -- Anchor member is defined.
    UNION ALL
    CTE_query_definition -- Recursive member is defined referencing cte_name.
)

-- Statement using the CTE
SELECT *
FROM cte_name

Semantik eksekusi rekursif adalah sebagai berikut:

Pisahkan ekspresi CTE menjadi jangkar dan anggota rekursif.
Jalankan anggota jangkar yang membuat pemanggilan pertama atau kumpulan hasil dasar (T0).
Jalankan anggota rekursif dengan Ti sebagai input dan Ti + 1 sebagai output.
Ulangi langkah 3 hingga set kosong dikembalikan.
Mengembalikan tataan hasil. Ini adalah dari UNION ALLT0 untuk Tn.

Examples

Contoh berikut menunjukkan semantik struktur CTE rekursif dengan mengembalikan daftar hierarkis karyawan, dimulai dengan karyawan dengan peringkat tertinggi, dalam AdventureWorks2025 database. Panduan eksekusi kode mengikuti contoh.

Buat tabel karyawan:

CREATE TABLE dbo.MyEmployees
(
    EmployeeID SMALLINT NOT NULL,
    FirstName NVARCHAR (30) NOT NULL,
    LastName NVARCHAR (40) NOT NULL,
    Title NVARCHAR (50) NOT NULL,
    DeptID SMALLINT NOT NULL,
    ManagerID INT NULL,
    CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC)
);

Isi tabel dengan nilai:

INSERT INTO dbo.MyEmployees
VALUES
    (1, N'Ken', N'Sánchez', N'Chief Executive Officer', 16, NULL),
    (273, N'Brian', N'Welcker', N'Vice President of Sales', 3, 1),
    (274, N'Stephen', N'Jiang', N'North American Sales Manager', 3, 273),
    (275, N'Michael', N'Blythe', N'Sales Representative', 3, 274),
    (276, N'Linda', N'Mitchell', N'Sales Representative', 3, 274),
    (285, N'Syed', N'Abbas', N'Pacific Sales Manager', 3, 273),
    (286, N'Lynn', N'Tsoflias', N'Sales Representative', 3, 285),
    (16, N'David', N'Bradley', N'Marketing Manager', 4, 273),
    (23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);

USE AdventureWorks2008R2;
GO

WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS (
-- Anchor member definition
SELECT e.ManagerID,
           e.EmployeeID,
           e.Title,
           edh.DepartmentID,
           0 AS Level
    FROM dbo.MyEmployees AS e
         INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
             ON e.EmployeeID = edh.BusinessEntityID
            AND edh.EndDate IS NULL
    WHERE ManagerID IS NULL
    UNION ALL
-- Recursive member definition
    SELECT e.ManagerID,
           e.EmployeeID,
           e.Title,
           edh.DepartmentID,
           Level + 1
    FROM dbo.MyEmployees AS e
         INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
             ON e.EmployeeID = edh.BusinessEntityID
            AND edh.EndDate IS NULL
         INNER JOIN DirectReports AS d
             ON e.ManagerID = d.EmployeeID)
-- Statement that executes the CTE
SELECT ManagerID,
       EmployeeID,
       Title,
       DeptID,
       Level
FROM DirectReports
     INNER JOIN HumanResources.Department AS dp
         ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Sales and Marketing'
      OR Level = 0;
GO

Contoh panduan kode

CTE rekursif, DirectReports, mendefinisikan satu anggota jangkar dan satu anggota rekursif.

Anggota jangkar mengembalikan kumpulan T0hasil dasar . Ini adalah karyawan dengan peringkat tertinggi di perusahaan. Artinya, seorang karyawan yang tidak melapor ke manajer.

Berikut adalah tataan hasil yang dikembalikan oleh anggota jangkar:

ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
NULL      1          Chief Executive Officer        0

Anggota rekursif mengembalikan bawahan langsung karyawan dalam tataan hasil anggota jangkar. Ini dicapai oleh operasi gabungan antara tabel Karyawan dan DirectReports CTE. Ini adalah referensi ke CTE itu sendiri yang menetapkan pemanggilan rekursif. Berdasarkan karyawan di CTE DirectReports sebagai input (Ti), gabungan (MyEmployees.ManagerID = DirectReports.EmployeeID) mengembalikan sebagai output (Ti + 1), karyawan yang memiliki (Ti) sebagai manajer mereka.

Oleh karena itu, iterasi pertama anggota rekursif mengembalikan tataan hasil ini:

ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
1         273        Vice President of Sales       1

Anggota rekursif diaktifkan berulang kali. Iterasi kedua anggota rekursif menggunakan hasil baris tunggal yang ditetapkan di langkah 3 (berisi EmployeeID ) 273sebagai nilai input, dan mengembalikan tataan hasil ini:

ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
273       16         Marketing Manager             2
273       274        North American Sales Manager  2
273       285        Pacific Sales Manager         2

Iterasi ketiga dari anggota rekursif menggunakan hasil sebelumnya yang ditetapkan sebagai nilai input, dan mengembalikan tataan hasil ini:

ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
16        23         Marketing Specialist          3
274       275        Sales Representative          3
274       276        Sales Representative          3
285       286        Sales Representative          3

Tataan hasil akhir yang dikembalikan oleh kueri yang sedang berjalan adalah penyatuan semua kumpulan hasil yang dihasilkan oleh jangkar dan anggota rekursif.

Berikut kumpulan hasilnya.

ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
NULL      1          Chief Executive Officer       0
1         273        Vice President of Sales       1
273       16         Marketing Manager             2
273       274        North American Sales Manager  2
273       285        Pacific Sales Manager         2
16        23         Marketing Specialist          3
274       275        Sales Representative          3
274       276        Sales Representative          3
285       286        Sales Representative          3

Saran dan Komentar

Apakah halaman ini membantu?

Last updated on 2025-11-18