Aracılığıyla paylaş

Ortak tablo ifadelerini kullanan özyinelemeli sorgular (Transact-SQL)

Şunlar için geçerlidir:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalitik Platform Sistemi (PDW)Microsoft Fabric'te SQL veritabanı

Ortak tablo ifadesi (CTE), kendisine başvurabilmenin önemli bir avantajını sağlar, böylece özyinelemeli bir CTE oluşturur. Özyinelemeli CTE, sonuç kümesinin tamamı elde edilene kadar verilerin alt kümelerini döndürmek için ilk CTE'nin tekrar tekrar yürütülmesidir.

Bir sorgu, özyinelemeli bir CTE'ye başvurduğunda özyinelemeli sorgu olarak adlandırılır. Hiyerarşik verileri döndürmek, özyinelemeli sorguların yaygın bir kullanımıdır. Örneğin, bir kuruluş şemasında çalışanları veya bir ana ürünün bir veya daha fazla bileşenin bulunduğu ve bu bileşenlerin alt bileşenleri olabileceği veya diğer üst ürünlerin bileşenleri olabileceği bir malzeme listesi senaryosunda verileri görüntüleme.

Özyinelemeli bir CTE, bir , SELECT, INSERT, UPDATEveya DELETE deyimi içinde CREATE VIEWözyinelemeli sorgu çalıştırmak için gereken kodu büyük ölçüde basitleştirebilir. SQL Server'ın önceki sürümlerinde özyinelemeli bir sorgu genellikle özyinelemeli adımların akışını denetlemek için geçici tablolar, imleçler ve mantık kullanmayı gerektirir. Yaygın tablo ifadeleri hakkında daha fazla bilgi için bkz . WITH common_table_expression.

Microsoft Fabric'te Doku Veri Ambarı ve SQL analiz uç noktası standart, sıralı ve iç içe yerleştirilmiş CTE'leri destekler, ancak özyinelemeli CTE'leri desteklemez.

Özyinelemeli CTE'nin yapısı

Transact-SQL özyinelemeli CTE'nin yapısı, diğer programlama dillerindeki özyinelemeli yordamlara benzer. Diğer dillerde özyinelemeli bir yordam skaler değer döndürse de, özyinelemeli bir CTE birden çok satır döndürebilir.

Özyinelemeli bir CTE üç öğeden oluşur:

  1. Yordamın çağrılması.

    Özyinelemeli CTE'nin ilk çağrısı, , , UNION ALLUNIONveya EXCEPT işleçleri tarafından INTERSECTbirleştirilen bir veya daha fazla CTE sorgu tanımından oluşur. Bu sorgu tanımları CTE yapısının temel sonuç kümesini oluşturduğundan, bağlantı üyeleri olarak adlandırılır.

    CTE sorgu tanımları, CTE'nin kendisine başvurmadıkları sürece bağlayıcı üyeleri olarak kabul edilir. Tüm tutturucu üye sorgu tanımları, ilk özyinelemeli üye tanımından önce konumlandırılmalıdır ve son tutturucu üyeyi ilk özyinelemeli üyeyle birleştirmek için bir UNION ALL işleç kullanılmalıdır.

  2. Yordamın özyinelemeli çağrıları.

    Özyinelemeli çağrı, CTE'nin kendisine başvuran işleçler tarafından UNION ALL birleştirilen bir veya daha fazla CTE sorgu tanımı içerir. Bu sorgu tanımları özyinelemeli üyeler olarak adlandırılır.

  3. Sonlandırma denetimi.

    Sonlandırma denetimi örtükdür; önceki çağrıdan hiçbir satır döndürülmediğinde özyineleme durdurulur.

Note

Yanlış oluşturulmuş özyinelemeli bir CTE sonsuz döngüye neden olabilir. Örneğin, özyinelemeli üye sorgu tanımı hem üst hem de alt sütunlar için aynı değerleri döndürürse, sonsuz bir döngü oluşturulur. Özyinelemeli sorgunun sonuçlarını test ederken, ipucunu ve , , veya deyiminin yan tümcesinde MAXRECURSION 0 ile 32.767 arasında bir değeri kullanarak OPTION belirli bir deyim için izin verilen özyineleme düzeylerinin INSERTsayısını sınırlayabilirsiniz.UPDATEDELETESELECT

Daha fazla bilgi için bakınız:

Sahte kod ve semantik

Özyinelemeli CTE yapısı en az bir sabit üye ve bir özyinelemeli üye içermelidir. Aşağıdaki sahte kod, tek bir sabit üye ve tek özyinelemeli üye içeren basit bir özyinelemeli CTE'nin bileşenlerini gösterir.

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

Özyinelemeli yürütmenin semantiği aşağıdaki gibidir:

  1. CTE ifadesini tutturucu ve özyinelemeli üyelere bölün.
  2. İlk çağrıyı veya temel sonuç kümesini (T0) oluşturan tutturucu üyelerini çalıştırın.
  3. Özyinelemeli üyeleri Ti giriş olarak ve Ti + 1'i çıkış olarak çalıştırın.
  4. Boş bir küme döndürülene kadar 3. adımı yineleyin.
  5. Sonuç kümesini döndürür. Bu , için UNION ALLbir T0Tn şeklindedir.

Examples

Aşağıdaki örnek, veritabanındaki en yüksek derecelendirmeli çalışandan başlayarak hiyerarşik bir çalışan listesi döndürerek özyinelemeli CTE yapısının AdventureWorks2025 semantiğini gösterir. Örnek, kod yürütme adım adım izlenecek yollardan birini izler.

Çalışan tablosu oluşturma:

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)
);

Tabloyu değerlerle doldurun:

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

Örnek kod kılavuzu

Özyinelemeli CTE, DirectReportsbir tutturucu üye ve bir özyinelemeli üye tanımlar.

Tutturucu üyesi, temel sonuç kümesini T0döndürür. Bu, şirketin en yüksek rütbeli çalışanıdır. Yani, bir yöneticiye rapor etmeyen bir çalışan.

Yer işareti üyesi tarafından döndürülen sonuç kümesi aşağıdadır:

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

Özyinelemeli üye, tutturucu üye sonuç kümesindeki çalışanın doğrudan altlarını döndürür. Bu, Çalışan tablosu ile DirectReports CTE arasındaki birleştirme işlemiyle elde edilir. Özyinelemeli çağrıyı oluşturan CTE'nin kendisine yapılan bu başvurudur. Giriş () olarak CTE'deki DirectReports çalışana bağlı olarakTi, birleşim (MyEmployees.ManagerID = DirectReports.EmployeeID) çıktı (Ti + 1) olarak döndürür; yöneticisi (Ti) olan çalışanlar.

Bu nedenle, özyinelemeli üyenin ilk yinelemesi şu sonuç kümesini döndürür:

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

Özyinelemeli üye tekrar tekrar etkinleştirilir. Özyinelemeli üyenin ikinci yinelemesi, giriş değeri olarak 3. adımda (içeren EmployeeID273) tek satırlı sonuç kümesini kullanır ve şu sonuç kümesini döndürür:

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

Özyinelemeli üyenin üçüncü yinelemesi, giriş değeri olarak önceki sonuç kümesini kullanır ve şu sonuç kümesini döndürür:

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

Çalışan sorgu tarafından döndürülen son sonuç kümesi, tutturucu ve özyinelemeli üyeler tarafından oluşturulan tüm sonuç kümelerinin birleşimidir.

Sonuç kümesi aşağıdadır.

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

İlgili içerik

  • Last updated on