WITH common_table_expression (SQL Bertransaksi)

Berlaku untuk: SQL Server (semua versi yang didukung) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Menentukan tataan hasil bernama sementara, yang dikenal sebagai ekspresi tabel umum (CTE). Ini berasal dari kueri sederhana dan ditentukan dalam lingkup eksekusi pernyataan SELECT, INSERT, UPDATE, DELETE, atau MERGE tunggal. Klausa ini juga dapat digunakan dalam pernyataan CREATE VIEW sebagai bagian dari pernyataan SELECT yang menentukan. Ekspresi tabel umum dapat menyertakan referensi ke dirinya sendiri. Ini disebut sebagai ekspresi tabel umum rekursif.

Konvensi Sintaksis T-SQL

Sintaks

[ WITH <common_table_expression> [ ,...n ] ]  
  
<common_table_expression>::=  
    expression_name [ ( column_name [ ,...n ] ) ]  
    AS  
    ( CTE_query_definition )  

Catatan

Untuk melihat sintaks transact-SQL untuk SQL Server 2014 dan yang lebih lama, lihat Dokumentasi versi sebelumnya.

Argumen

expression_name
Adalah pengidentifikasi yang valid untuk ekspresi tabel umum. expression_name harus berbeda dari nama ekspresi tabel umum lainnya yang ditentukan dalam klausa WITH <common_table_expression> yang sama, tetapi expression_name bisa sama dengan nama tabel atau tampilan dasar. Referensi apa pun ke expression_name dalam kueri menggunakan ekspresi tabel umum dan bukan objek dasar.

column_name
Menentukan nama kolom dalam ekspresi tabel umum. Nama duplikat dalam satu definisi CTE tidak diizinkan. Jumlah nama kolom yang ditentukan harus cocok dengan jumlah kolom dalam kumpulan hasil CTE_query_definition. Daftar nama kolom bersifat opsional hanya jika nama yang berbeda untuk semua kolom yang dihasilkan disediakan dalam definisi kueri.

CTE_query_definition
Menentukan pernyataan SELECT yang tataan hasilnya mengisi ekspresi tabel umum. Pernyataan SELECT untuk CTE_query_definition harus memenuhi persyaratan yang sama seperti untuk membuat tampilan, kecuali CTE tidak dapat menentukan CTE lain. Untuk informasi selengkapnya, lihat bagian Keterangan dan CREATE VIEW (Transact-SQL).

Jika lebih dari satu CTE_query_definition ditentukan, definisi kueri harus digabungkan dengan salah satu operator set ini: UNION ALL, UNION, EXCEPT, atau INTERSECT.

Keterangan

Panduan untuk Membuat dan Menggunakan Ekspresi Tabel Umum

Panduan berikut berlaku untuk ekspresi tabel umum yang tidak aman. Untuk panduan yang berlaku untuk ekspresi tabel umum rekursif, lihat Panduan untuk Menentukan dan Menggunakan Ekspresi Tabel Umum Rekursif sebagai berikut.

• CTE harus diikuti oleh satu SELECTpernyataan , , INSERTUPDATE, atau DELETE yang mereferensikan beberapa atau semua kolom CTE. CTE juga dapat ditentukan dalam CREATE VIEW pernyataan sebagai bagian SELECT dari pernyataan penentuan tampilan.

• Beberapa definisi kueri CTE dapat didefinisikan dalam CTE yang tidak aman. Definisi harus digabungkan oleh salah satu operator set ini: UNION ALL, , UNIONINTERSECT, atau EXCEPT.

• CTE dapat mereferensikan dirinya sendiri dan CTE yang ditentukan sebelumnya dalam klausa yang sama WITH . Referensi penerusan tidak diperbolehkan.

• Menentukan lebih dari satu klausa WITH dalam CTE tidak diperbolehkan. Misalnya, jika CTE_query_definition berisi subkueri, subkueri tersebut tidak dapat berisi klausa WITH berlapis yang menentukan CTE lain.

• Klausa berikut tidak dapat digunakan dalam CTE_query_definition:

  • ORDER BY (kecuali ketika TOP klausul ditentukan)

  • INTO

  • OPTION klausa dengan petunjuk kueri

  • FOR BROWSE

• Ketika CTE digunakan dalam pernyataan yang merupakan bagian dari batch, pernyataan sebelum harus diikuti oleh titik koma.

• Kueri yang mereferensikan CTE dapat digunakan untuk menentukan kursor.

• Tabel pada server jarak jauh dapat dirujuk di CTE.

• Saat menjalankan CTE, petunjuk apa pun yang mereferensikan CTE dapat bertentangan dengan petunjuk lain yang ditemukan ketika CTE mengakses tabel yang mendasarinya, dengan cara yang sama seperti petunjuk yang mereferensikan tampilan dalam kueri. Ketika ini terjadi, kueri mengembalikan kesalahan.

Panduan untuk Menentukan dan Menggunakan Ekspresi Tabel Umum Rekursif

Panduan berikut berlaku untuk menentukan ekspresi tabel umum rekursif:

• Definisi CTE rekursif harus berisi setidaknya dua definisi kueri CTE, anggota jangkar, dan anggota rekursif. Beberapa anggota jangkar dan anggota rekursif dapat ditentukan; namun, semua definisi kueri anggota jangkar harus diletakkan sebelum definisi anggota rekursif pertama. Semua definisi kueri CTE adalah anggota jangkar kecuali mereka mereferensikan CTE itu sendiri.

• Anggota jangkar harus digabungkan oleh salah satu operator set ini: UNION ALL, UNION, INTERSECT, atau EXCEPT. UNION ALL adalah satu-satunya operator set yang diizinkan antara anggota jangkar terakhir dan anggota rekursif pertama, dan ketika menggabungkan beberapa anggota rekursif.

• Jumlah kolom dalam jangkar dan anggota rekursif harus sama.

• Jenis data kolom di anggota rekursif harus sama dengan jenis data kolom terkait di anggota jangkar.

• Klausa FROM dari anggota rekursif hanya boleh merujuk satu kali ke expression_name CTE.

• Item berikut ini tidak diperbolehkan dalam CTE_query_definition anggota rekursif:

  • SELECT DISTINCT

  • GROUP BY

  • PIVOT(Saat tingkat kompatibilitas database adalah 110 atau lebih tinggi. Lihat Perubahan Mencolok pada Fitur Mesin Database di SQL Server 2016.)

  • HAVING

  • Agregasi skalar

  • TOP

  • LEFT, RIGHT, OUTER JOIN (INNER JOIN diperbolehkan)

  • Subkueri

  • Petunjuk yang diterapkan ke referensi rekursif ke CTE di dalam CTE_query_definition.

Panduan berikut berlaku untuk menggunakan ekspresi tabel umum rekursif:

• Semua kolom yang dikembalikan oleh CTE rekursif dapat diubah ke null terlepas dari nullabilitas kolom yang dikembalikan oleh pernyataan yang berpartisipasi SELECT .

• 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. Untuk mencegah perulangan tak terbatas, Anda dapat membatasi jumlah tingkat rekursi yang diizinkan untuk pernyataan tertentu dengan menggunakan MAXRECURSION petunjuk dan nilai antara 0 dan 32.767 dalam klausa OPTION dari INSERTpernyataan , , UPDATEDELETE, atau SELECT . Ini memungkinkan Anda mengontrol eksekusi pernyataan sampai Anda menyelesaikan masalah kode yang membuat perulangan. Default seluruh server adalah 100. Ketika 0 ditentukan, tidak ada batas yang diterapkan. Hanya satu MAXRECURSION nilai yang dapat ditentukan per pernyataan. Untuk informasi selengkapnya, lihat Petunjuk Kueri (Transact-SQL).

• Tampilan yang berisi ekspresi tabel umum rekursif tidak dapat digunakan untuk memperbarui data.

• Kursor dapat ditentukan pada kueri menggunakan CTE. CTE adalah argumen select_statement yang menentukan kumpulan hasil kursor. Hanya kursor hanya maju dan statis (rekam jepret) yang cepat yang diizinkan untuk CTE rekursif. Jika jenis kursor lain ditentukan dalam CTE rekursif, jenis kursor dikonversi menjadi statis.

• Tabel pada server jarak jauh dapat dirujuk di CTE. Jika server jarak jauh direferensikan di anggota rekursif CTE, penampung dibuat untuk setiap tabel jarak jauh sehingga tabel dapat diakses berulang kali secara lokal. Jika ini adalah kueri CTE, Penampung Indeks/Penampung Malas ditampilkan dalam rencana kueri dan akan memiliki predikat tambahan WITH STACK . Ini adalah salah satu cara untuk mengonfirmasi rekursi yang tepat.

• Fungsi analitik dan agregat di bagian rekursif CTE diterapkan ke set untuk tingkat rekursi saat ini dan bukan ke yang ditetapkan untuk CTE. Fungsi seperti ROW_NUMBER hanya beroperasi pada subset data yang diteruskan ke mereka oleh tingkat rekursi saat ini dan bukan seluruh kumpulan data yang diteruskan ke bagian rekursif CTE. Untuk informasi selengkapnya, lihat contoh K. Menggunakan fungsi analitik dalam CTE rekursif yang mengikutinya.

Fitur dan Batasan Ekspresi Tabel Umum di Azure Synapse Analytics and Analytics Platform System (PDW)

Implementasi CTE saat ini dalam Azure Synapse Analytics and Analytics Platform System (PDW) memiliki fitur dan batasan berikut:

• CTE dapat ditentukan dalam SELECT pernyataan.

• CTE dapat ditentukan dalam CREATE VIEW pernyataan.

• CTE dapat ditentukan dalam CREATE TABLE AS SELECT pernyataan (CTAS).

• CTE dapat ditentukan dalam CREATE REMOTE TABLE AS SELECT pernyataan (CRTAS).

• CTE dapat ditentukan dalam CREATE EXTERNAL TABLE AS SELECT pernyataan (CETAS).

• Tabel jarak jauh dapat dirujuk dari CTE.

• Tabel eksternal dapat dirujuk dari CTE.

• Beberapa definisi kueri CTE dapat ditentukan dalam CTE.

• CTE harus diikuti oleh satu SELECT pernyataan. INSERTPernyataan , UPDATE, DELETE, dan MERGE tidak didukung.

• Ekspresi tabel umum yang menyertakan referensi ke dirinya sendiri (ekspresi tabel umum rekursif) tidak didukung.

• Menentukan lebih dari satu WITH klausa dalam CTE tidak diperbolehkan. Misalnya, jika definisi kueri CTE berisi subkueri, subkueri tersebut tidak dapat berisi klausa berlapis WITH yang menentukan CTE lain.

• Klausa ORDER BY tidak dapat digunakan dalam CTE_query_definition, kecuali ketika TOP klausa ditentukan.

• Ketika CTE digunakan dalam pernyataan yang merupakan bagian dari batch, pernyataan sebelum harus diikuti oleh titik koma.

• Ketika digunakan dalam pernyataan yang disiapkan oleh sp_prepare, CTE akan bertingkah sama seperti pernyataan lain SELECT dalam PDW. Namun, jika KTE digunakan sebagai bagian dari CETAS yang disiapkan oleh sp_prepare, perilaku dapat menunda dari SQL Server dan pernyataan PDW lainnya karena cara pengikatan diimplementasikan untuk sp_prepare. Jika SELECT itu mereferensikan CTE menggunakan kolom yang salah yang tidak ada di CTE, sp_prepare akan lulus tanpa mendeteksi kesalahan, tetapi kesalahan akan dilemparkan selama sp_execute sebagai gantinya.

Contoh

A. Membuat ekspresi tabel umum sederhana

Contoh berikut menunjukkan jumlah total pesanan penjualan per tahun untuk setiap perwakilan penjualan di Adventure Works Cycles.

-- Define the CTE expression name and column list.  
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)  
AS  
-- Define the CTE query.  
(  
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear  
    FROM Sales.SalesOrderHeader  
    WHERE SalesPersonID IS NOT NULL  
)  
-- Define the outer query referencing the CTE name.  
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear  
FROM Sales_CTE  
GROUP BY SalesYear, SalesPersonID  
ORDER BY SalesPersonID, SalesYear;  

B. Menggunakan ekspresi tabel umum untuk membatasi jumlah dan melaporkan rata-rata

Contoh berikut menunjukkan jumlah rata-rata pesanan penjualan selama bertahun-tahun untuk perwakilan penjualan.

WITH Sales_CTE (SalesPersonID, NumberOfOrders)  
AS  
(  
    SELECT SalesPersonID, COUNT(*)  
    FROM Sales.SalesOrderHeader  
    WHERE SalesPersonID IS NOT NULL  
    GROUP BY SalesPersonID  
)  
SELECT AVG(NumberOfOrders) AS "Average Sales Per Person"  
FROM Sales_CTE;  

C. Menggunakan beberapa definisi CTE dalam satu kueri

Contoh berikut menunjukkan cara menentukan lebih dari satu CTE dalam satu kueri. Perhatikan bahwa koma digunakan untuk memisahkan definisi kueri CTE. Fungsi FORMAT, digunakan untuk menampilkan jumlah moneter dalam format mata uang, tersedia dalam SQL Server 2012 dan yang lebih tinggi.

WITH Sales_CTE (SalesPersonID, TotalSales, SalesYear)  
AS  
-- Define the first CTE query.  
(  
    SELECT SalesPersonID, SUM(TotalDue) AS TotalSales, YEAR(OrderDate) AS SalesYear  
    FROM Sales.SalesOrderHeader  
    WHERE SalesPersonID IS NOT NULL  
       GROUP BY SalesPersonID, YEAR(OrderDate)  
  
)  
,   -- Use a comma to separate multiple CTE definitions.  
  
-- Define the second CTE query, which returns sales quota data by year for each sales person.  
Sales_Quota_CTE (BusinessEntityID, SalesQuota, SalesQuotaYear)  
AS  
(  
       SELECT BusinessEntityID, SUM(SalesQuota)AS SalesQuota, YEAR(QuotaDate) AS SalesQuotaYear  
       FROM Sales.SalesPersonQuotaHistory  
       GROUP BY BusinessEntityID, YEAR(QuotaDate)  
)  
  
-- Define the outer query by referencing columns from both CTEs.  
SELECT SalesPersonID  
  , SalesYear  
  , FORMAT(TotalSales,'C','en-us') AS TotalSales  
  , SalesQuotaYear  
  , FORMAT (SalesQuota,'C','en-us') AS SalesQuota  
  , FORMAT (TotalSales -SalesQuota, 'C','en-us') AS Amt_Above_or_Below_Quota  
FROM Sales_CTE  
JOIN Sales_Quota_CTE ON Sales_Quota_CTE.BusinessEntityID = Sales_CTE.SalesPersonID  
                    AND Sales_CTE.SalesYear = Sales_Quota_CTE.SalesQuotaYear  
ORDER BY SalesPersonID, SalesYear;    

Berikut adalah tataan hasil parsial.

SalesPersonID SalesYear   TotalSales    SalesQuotaYear SalesQuota  Amt_Above_or_Below_Quota  
------------- ---------   -----------   -------------- ---------- ----------------------------------   
274           2005        $32,567.92    2005           $35,000.00  ($2,432.08)  
274           2006        $406,620.07   2006           $455,000.00 ($48,379.93)  
274           2007        $515,622.91   2007           $544,000.00 ($28,377.09)  
274           2008        $281,123.55   2008           $271,000.00  $10,123.55  

D. Menggunakan ekspresi tabel umum rekursif untuk menampilkan beberapa tingkat rekursi

Contoh berikut menunjukkan daftar hierarkis manajer dan karyawan yang melaporkannya. Contoh dimulai dengan membuat dan mengisi dbo.MyEmployees tabel.

-- Create an Employee table.  
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 SMALLINT NULL,  
 CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC),
 CONSTRAINT FK_MyEmployees_ManagerID_EmployeeID FOREIGN KEY (ManagerID) REFERENCES dbo.MyEmployees (EmployeeID)
);  
-- Populate the table with values.  
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);  

WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS   
(  
    SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel  
    FROM dbo.MyEmployees   
    WHERE ManagerID IS NULL  
    UNION ALL  
    SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1  
    FROM dbo.MyEmployees AS e  
        INNER JOIN DirectReports AS d  
        ON e.ManagerID = d.EmployeeID   
)  
SELECT ManagerID, EmployeeID, Title, EmployeeLevel   
FROM DirectReports  
ORDER BY ManagerID;   

Menggunakan ekspresi tabel umum rekursif untuk menampilkan dua tingkat rekursi

Contoh berikut menunjukkan manajer dan karyawan yang melaporkannya. Jumlah tingkat yang dikembalikan dibatasi hingga dua.

WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS   
(  
    SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel  
    FROM dbo.MyEmployees   
    WHERE ManagerID IS NULL  
    UNION ALL  
    SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1  
    FROM dbo.MyEmployees AS e  
        INNER JOIN DirectReports AS d  
        ON e.ManagerID = d.EmployeeID   
)  
SELECT ManagerID, EmployeeID, Title, EmployeeLevel   
FROM DirectReports  
WHERE EmployeeLevel <= 2 ;  

Menggunakan ekspresi tabel umum rekursif untuk menampilkan daftar hierarkis

Contoh berikut menambahkan nama manajer dan karyawan, dan judul masing-masing. Hierarki manajer dan karyawan juga ditekankan dengan mengindentasi setiap tingkatan.

WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)  
AS (SELECT CONVERT(VARCHAR(255), e.FirstName + ' ' + e.LastName),  
        e.Title,  
        e.EmployeeID,  
        1,  
        CONVERT(VARCHAR(255), e.FirstName + ' ' + e.LastName)  
    FROM dbo.MyEmployees AS e  
    WHERE e.ManagerID IS NULL  
    UNION ALL  
    SELECT CONVERT(VARCHAR(255), REPLICATE ('|    ' , EmployeeLevel) +  
        e.FirstName + ' ' + e.LastName),  
        e.Title,  
        e.EmployeeID,  
        EmployeeLevel + 1,  
        CONVERT (VARCHAR(255), RTRIM(Sort) + '|    ' + FirstName + ' ' +   
                 LastName)  
    FROM dbo.MyEmployees AS e  
    JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID  
    )  
SELECT EmployeeID, Name, Title, EmployeeLevel  
FROM DirectReports   
ORDER BY Sort;  

Menggunakan MAXRECURSION untuk membatalkan pernyataan

MAXRECURSION dapat digunakan untuk mencegah CTE rekursif yang terbentuk dengan buruk masuk ke dalam perulangan tak terbatas. Contoh berikut sengaja membuat perulangan tak terbatas dan menggunakan MAXRECURSION petunjuk untuk membatasi jumlah tingkat rekursi menjadi dua.

--Creates an infinite loop  
WITH cte (EmployeeID, ManagerID, Title) AS  
(  
    SELECT EmployeeID, ManagerID, Title  
    FROM dbo.MyEmployees  
    WHERE ManagerID IS NOT NULL  
  UNION ALL  
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title  
    FROM cte   
    JOIN  dbo.MyEmployees AS e   
        ON cte.ManagerID = e.EmployeeID  
)  
--Uses MAXRECURSION to limit the recursive levels to 2  
SELECT EmployeeID, ManagerID, Title  
FROM cte  
OPTION (MAXRECURSION 2);  

Setelah kesalahan pengodean dikoreksi, MAXRECURSION tidak lagi diperlukan. Contoh berikut menunjukkan kode yang dikoreksi.

WITH cte (EmployeeID, ManagerID, Title)  
AS  
(  
    SELECT EmployeeID, ManagerID, Title  
    FROM dbo.MyEmployees  
    WHERE ManagerID IS NOT NULL  
  UNION ALL  
    SELECT  e.EmployeeID, e.ManagerID, e.Title  
    FROM dbo.MyEmployees AS e  
    JOIN cte ON e.ManagerID = cte.EmployeeID  
)  
SELECT EmployeeID, ManagerID, Title  
FROM cte;  

E. Menggunakan ekspresi tabel umum untuk secara selektif menelusuri hubungan rekursif dalam pernyataan SELECT

Contoh berikut menunjukkan hierarki rakitan produk dan komponen yang diperlukan untuk membangun sepeda untuk ProductAssemblyID = 800.

USE AdventureWorks2012;  
GO  
WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS  
(  
    SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,  
        b.EndDate, 0 AS ComponentLevel  
    FROM Production.BillOfMaterials AS b  
    WHERE b.ProductAssemblyID = 800  
          AND b.EndDate IS NULL  
    UNION ALL  
    SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,  
        bom.EndDate, ComponentLevel + 1  
    FROM Production.BillOfMaterials AS bom   
        INNER JOIN Parts AS p  
        ON bom.ProductAssemblyID = p.ComponentID  
        AND bom.EndDate IS NULL  
)  
SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate,  
        ComponentLevel   
FROM Parts AS p  
    INNER JOIN Production.Product AS pr  
    ON p.ComponentID = pr.ProductID  
ORDER BY ComponentLevel, AssemblyID, ComponentID;  

F. Menggunakan CTE rekursif dalam pernyataan UPDATE

Contoh berikut memperbarui PerAssemblyQty nilai untuk semua bagian yang digunakan untuk membangun produk 'Road-550-W Yellow, 44' (ProductAssemblyID``800). Ekspresi tabel umum mengembalikan daftar hierarkis bagian yang digunakan untuk membangun ProductAssemblyID 800 dan komponen yang digunakan untuk membuat bagian tersebut, dan sebagainya. Hanya baris yang dikembalikan oleh ekspresi tabel umum yang dimodifikasi.

USE AdventureWorks2012;  
GO  
WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS  
(  
    SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,  
        b.EndDate, 0 AS ComponentLevel  
    FROM Production.BillOfMaterials AS b  
    WHERE b.ProductAssemblyID = 800  
          AND b.EndDate IS NULL  
    UNION ALL  
    SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,  
        bom.EndDate, ComponentLevel + 1  
    FROM Production.BillOfMaterials AS bom   
        INNER JOIN Parts AS p  
        ON bom.ProductAssemblyID = p.ComponentID  
        AND bom.EndDate IS NULL  
)  
UPDATE Production.BillOfMaterials  
SET PerAssemblyQty = c.PerAssemblyQty * 2  
FROM Production.BillOfMaterials AS c  
JOIN Parts AS d ON c.ProductAssemblyID = d.AssemblyID  
WHERE d.ComponentLevel = 0;  

H. Menggunakan beberapa jangkar dan anggota rekursif

Contoh berikut menggunakan beberapa jangkar dan anggota rekursif untuk mengembalikan semua leluhur orang tertentu. Tabel dibuat dan nilai yang disisipkan untuk menetapkan genealogi keluarga yang dikembalikan oleh CTE rekursif.

-- Genealogy table  
IF OBJECT_ID('dbo.Person','U') IS NOT NULL DROP TABLE dbo.Person;  
GO  
CREATE TABLE dbo.Person(ID int, Name VARCHAR(30), Mother INT, Father INT);  
GO  
INSERT dbo.Person   
VALUES(1, 'Sue', NULL, NULL)  
      ,(2, 'Ed', NULL, NULL)  
      ,(3, 'Emma', 1, 2)  
      ,(4, 'Jack', 1, 2)  
      ,(5, 'Jane', NULL, NULL)  
      ,(6, 'Bonnie', 5, 4)  
      ,(7, 'Bill', 5, 4);  
GO  
-- Create the recursive CTE to find all of Bonnie's ancestors.  
WITH Generation (ID) AS  
(  
-- First anchor member returns Bonnie's mother.  
    SELECT Mother   
    FROM dbo.Person  
    WHERE Name = 'Bonnie'  
UNION  
-- Second anchor member returns Bonnie's father.  
    SELECT Father   
    FROM dbo.Person  
    WHERE Name = 'Bonnie'  
UNION ALL  
-- First recursive member returns male ancestors of the previous generation.  
    SELECT Person.Father  
    FROM Generation, Person  
    WHERE Generation.ID=Person.ID  
UNION ALL  
-- Second recursive member returns female ancestors of the previous generation.  
    SELECT Person.Mother  
    FROM Generation, dbo.Person  
    WHERE Generation.ID=Person.ID  
)  
SELECT Person.ID, Person.Name, Person.Mother, Person.Father  
FROM Generation, dbo.Person  
WHERE Generation.ID = Person.ID;  
GO  

i. Menggunakan fungsi analitik dalam CTE rekursif

Contoh berikut menunjukkan perangkap yang dapat terjadi saat menggunakan fungsi analitik atau agregat di bagian rekursif CTE.

DECLARE @t1 TABLE (itmID INT, itmIDComp INT);  
INSERT @t1 VALUES (1,10), (2,10);   
  
DECLARE @t2 TABLE (itmID INT, itmIDComp INT);   
INSERT @t2 VALUES (3,10), (4,10);   
  
WITH vw AS  
 (  
    SELECT itmIDComp, itmID  
    FROM @t1  
  
    UNION ALL  
  
    SELECT itmIDComp, itmID  
    FROM @t2  
)   
,r AS  
 (  
    SELECT t.itmID AS itmIDComp  
           , NULL AS itmID  
           ,CAST(0 AS BITING) AS N  
           ,1 AS Lvl  
    FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS t (itmID)   
  
UNION ALL  
  
SELECT t.itmIDComp  
    , t.itmID  
    , ROW_NUMBER() OVER(PARTITION BY t.itmIDComp ORDER BY t.itmIDComp, t.itmID) AS N  
    , Lvl + 1  
FROM r   
    JOIN vw AS t ON t.itmID = r.itmIDComp  
)   
  
SELECT Lvl, N FROM r;  

Hasil berikut adalah hasil yang diharapkan untuk kueri.

Lvl  N  
1    0  
1    0  
1    0  
1    0  
2    4  
2    3  
2    2  
2    1  

Hasil berikut adalah hasil aktual untuk kueri.

Lvl  N  
1    0  
1    0  
1    0  
1    0  
2    1  
2    1  
2    1  
2    1  

N mengembalikan 1 untuk setiap pass bagian rekursif CTE karena hanya subset data untuk tingkat rekursi tersebut yang diteruskan ke ROWNUMBER. Untuk setiap perulangan dari bagian rekursif kueri, hanya satu baris yang diteruskan ke ROWNUMBER.

Contoh: Azure Synapse Analytics and Analytics Platform System (PDW)

j. Menggunakan ekspresi tabel umum dalam pernyataan CTAS

Contoh berikut membuat tabel baru yang berisi jumlah total pesanan penjualan per tahun untuk setiap perwakilan penjualan di Adventure Works Cycles.

USE AdventureWorks2012;  
GO   
CREATE TABLE SalesOrdersPerYear  
WITH  
(  
    DISTRIBUTION = HASH(SalesPersonID)  
)  
AS  
    -- Define the CTE expression name and column list.  
    WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)  
    AS  
    -- Define the CTE query.  
    (  
        SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear  
        FROM Sales.SalesOrderHeader  
        WHERE SalesPersonID IS NOT NULL  
    )  
    -- Define the outer query referencing the CTE name.  
    SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear  
    FROM Sales_CTE  
    GROUP BY SalesYear, SalesPersonID  
    ORDER BY SalesPersonID, SalesYear;  
GO  

K. Menggunakan ekspresi tabel umum dalam pernyataan CETAS

Contoh berikut membuat tabel eksternal baru yang berisi jumlah total pesanan penjualan per tahun untuk setiap perwakilan penjualan di Adventure Works Cycles.

USE AdventureWorks2012;  
GO    
CREATE EXTERNAL TABLE SalesOrdersPerYear  
WITH  
(  
    LOCATION = 'hdfs://xxx.xxx.xxx.xxx:5000/files/Customer',  
    FORMAT_OPTIONS ( FIELD_TERMINATOR = '|' )   
)  
AS  
    -- Define the CTE expression name and column list.  
    WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)  
    AS  
    -- Define the CTE query.  
    (  
        SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear  
        FROM Sales.SalesOrderHeader  
        WHERE SalesPersonID IS NOT NULL  
    )  
    -- Define the outer query referencing the CTE name.  
    SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear  
    FROM Sales_CTE  
    GROUP BY SalesYear, SalesPersonID  
    ORDER BY SalesPersonID, SalesYear;  
GO  

L. Menggunakan beberapa CTE yang dipisahkan koma dalam pernyataan

Contoh berikut menunjukkan termasuk dua CTE dalam satu pernyataan. CTEs tidak dapat ditumpuk (tidak ada rekursi).

WITH   
 CountDate (TotalCount, TableName) AS  
    (  
     SELECT COUNT(datekey), 'DimDate' FROM DimDate  
    ) ,  
 CountCustomer (TotalAvg, TableName) AS  
    (  
     SELECT COUNT(CustomerKey), 'DimCustomer' FROM DimCustomer  
    )  
SELECT TableName, TotalCount FROM CountDate  
UNION ALL  
SELECT TableName, TotalAvg FROM CountCustomer;  

Lihat juga

CREATE VIEW (SQL Bertransaksi)
DELETE (Transact-SQL)
EXCEPT dan INTERSECT (Transact-SQL)
INSERT (Transact-SQL)
SELECT (Transact-SQL)
UPDATE (SQL Bertransaksi)