Aracılığıyla paylaş

WITH common_table_expression (Transact-SQL)

Şunlar için geçerlidir:SQL ServerAzure SQL VeritabanıAzure SQL Yönetilen ÖrneğiAzure Synapse AnalyticsAnaliz Platformu Sistemi (PDW)Microsoft Fabric'teki SQL analiz uç noktasıMicrosoft Fabric'teki ambarMicrosoft Fabric'teki SQL veritabanı

Ortak tablo ifadesi (CTE) olarak bilinen geçici adlandırılmış bir sonuç kümesi belirtir. Bu, basit bir sorgudan türetilir ve tek SELECTbir , INSERT, UPDATE, MERGEveya DELETE deyiminin yürütme kapsamında tanımlanır. Bu yan tümce, tanımlayıcı CREATE VIEW deyiminin bir parçası olarak deyiminde SELECT de kullanılabilir. Ortak bir tablo ifadesi kendisine başvurular içerebilir. Bu, özyinelemeli ortak tablo ifadesi olarak adlandırılır.

Daha fazla bilgi için bkz. Ortak tablo ifadelerini kullanarak özyinelemeli sorgular.

Transact-SQL söz dizimi kuralları

Syntax

[ WITH <common_table_expression> [ , ...n ] ]

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

Arguments

expression_name

Ortak tablo ifadesi için geçerli bir tanımlayıcı. expression_name aynı WITH <common_table_expression> yan tümcede tanımlanan diğer ortak tablo ifadelerinden farklı olmalıdır, ancak expression_name temel tablo veya görünümün adıyla aynı olabilir. Sorgudaki expression_name başvurularında temel nesne değil ortak tablo ifadesi kullanılır.

column_name

Ortak tablo ifadesinde bir sütun adı belirtir. Tek bir CTE tanımı içinde yinelenen adlara izin verilmez. Belirtilen sütun adlarının sayısı , CTE_query_definition sonuç kümesindeki sütun sayısıyla eşleşmelidir. Sütun adları listesi, yalnızca sorgu tanımında elde edilen tüm sütunlar için ayrı adlar sağlandığında isteğe bağlıdır.

CTE_query_definition

Sonuç kümesi ortak tablo ifadesini dolduran bir SELECT deyimi belirtir. SELECT CTE_query_definition deyimi, bir CTE'nin başka bir CTE tanımlayamamaması dışında, görünüm oluşturma gereksinimleriyle aynı gereksinimleri karşılamalıdır. Daha fazla bilgi için Açıklamalar bölümüne ve CREATE VIEW bölümüne bakın.

Birden fazla CTE_query_definition tanımlanmışsa, sorgu tanımları şu küme işleçlerinden biri tarafından birleştirilmelidir: UNION ALL, UNION, EXCEPTveya INTERSECT.

Kullanım yönergeleri

Yaygın tablo ifadelerinden gelen sorgu sonuçları gerçekleşmez. Adlandırılmış sonuç kümesine yapılan her dış başvuru, tanımlı sorgunun yeniden yürütülmesini gerektirir. Adlandırılmış sonuç kümesine birden çok başvuru gerektiren sorgular için bunun yerine geçici bir nesne kullanmayı göz önünde bulundurun.

Ortak bir tablo ifadesinde saklı yordam yürütemezsiniz.

Özyinelemeli ve özyinelemeli olmayan CTE'ler hakkında kullanım yönergeleri için aşağıdaki bölümlere bakın.

Özyinelemeli olmayan ortak tablo ifadeleri için yönergeler

Note

Aşağıdaki yönergeler, özyinelemeli olmayan ortak tablo ifadeleri için geçerlidir. Özyinelemeli ortak tablo ifadelerine uygulanan yönergeler için bkz. Özyinelemeli ortak tablo ifadeleri için yönergeler.

CTE'yi, CTE sütunlarının bir kısmına veya SELECT tümüne başvuran tek INSERTbir , UPDATEMERGE, , DELETEveya deyimi takip etmelidir. CTE, görünümün tanımlayıcı CREATE VIEW deyiminin bir parçası olarak deyiminde SELECT de belirtilebilir.

Birden çok CTE sorgu tanımı, özyinelemeli olmayan bir CTE'de tanımlanabilir. Tanımlar şu küme işleçlerinden biri tarafından birleştirilmelidir: UNION ALL, UNION, INTERSECTveya EXCEPT.

CTE, aynı WITH yan tümcede kendisine ve daha önce tanımlanmış CTE'lere başvurabilir. İleriye başvuruya izin verilmez.

CTE'de birden WITH fazla yan tümce belirtilmesine izin verilmez. Örneğin, bir CTE_query_definition bir alt sorgu içeriyorsa, bu alt sorgu başka bir CTE'yi tanımlayan iç içe WITH bir yan tümce içeremez.

Microsoft Fabric'te iç içe yerleştirilmiş CTE'ler hakkında daha fazla bilgi için bkz. Doku veri ambarında İç İçe Ortak Tablo İfadesi (CTE) (Transact-SQL).

aşağıdaki yan tümceler CTE_query_definition kullanılamaz:

  • ORDER BY(or TOP yan tümcesi belirtildiği durumlar OFFSET/FETCH dışında)
  • INTO
  • OPTION sorgu ipuçlarını içeren yan tümce 1
  • FOR BROWSE

1 Yan OPTION tümcesi bir CTE tanımı içinde kullanılamaz. Yalnızca en SELECT dıştaki deyimde kullanılabilir.

Bir toplu iş parçası olan bir deyimde bir CTE kullanıldığında, önce deyimi noktalı virgül tarafından takip edilmelidir.

bir Imleç tanımlamak için CTE'ye başvuran bir sorgu kullanılabilir.

Uzak sunuculardaki tablolara CTE'de başvurulabilir.

CTE yürütürken, CTE'ye başvuran tüm ipuçları, CTE temel tablolarına eriştiğinde bulunan diğer ipuçlarıyla çakışabilir ve sorgulardaki görünümlere başvuran ipuçlarıyla aynı şekilde. Bu durum oluştuğunda sorgu bir hata döndürür.

Özyinelemeli ortak tablo ifadeleri için yönergeler

Note

Aşağıdaki yönergeler özyinelemeli bir ortak tablo ifadesi tanımlamak için geçerlidir. Özyinelemeli olmayan CTE'ler için geçerli olan yönergeler için bkz. Özyinelemeli olmayan ortak tablo ifadeleri için yönergeler.

Özyinelemeli CTE tanımı en az iki CTE sorgu tanımı içermelidir: bir tutturucu üye ve özyinelemeli üye. Birden çok tutturucu üye ve özyinelemeli üye tanımlanabilir; ancak, tüm tutturucu üye sorgu tanımları ilk özyinelemeli üye tanımından önce yerleştirilmelidir. Tüm CTE sorgu tanımları, CTE'nin kendisine başvurmadıkları sürece bağlayıcı üyeleridir.

Tutturucu üyeleri şu küme işleçlerinden biri tarafından birleştirilmelidir: UNION ALL, UNION, INTERSECTveya EXCEPT. UNION ALL , son tutturucu üye ile ilk özyinelemeli üye arasında ve birden çok özyinelemeli üyeyi birleştirirken izin verilen tek küme işlecidir.

Tutturucudaki ve özyinelemeli üyelerdeki sütun sayısı aynı olmalıdır.

Özyinelemeli üyedeki bir sütunun veri türü, bağlayıcı üyedeki ilgili sütunun veri türüyle aynı olmalıdır.

Özyinelemeli FROM bir üyenin yan tümcesi CTE expression_name yalnızca bir kez başvurmalıdır.

Özyinelemeli üyenin CTE_query_definition aşağıdaki öğelere izin verilmez:

  • SELECT DISTINCT
  • GROUP BY
  • PIVOT 1
  • HAVING
  • Skaler agregasyon
  • TOP
  • LEFT, RIGHT, OUTER JOIN (INNER JOIN izin verilir)
  • Subqueries
  • bir CTE_query_definition içindeki CTE'ye özyinelemeli başvuruya uygulanan ipucu.

1 Veritabanı uyumluluk düzeyi 110 veya daha yüksek olduğunda. Bkz . SQL Server 2016'da Veritabanı Altyapısı özelliklerinde hataya neden olan değişiklikler.

Aşağıdaki yönergeler özyinelemeli bir ortak tablo ifadesi kullanmak için geçerlidir:

  • Özyinelemeli CTE tarafından döndürülen tüm sütunlar, katılan SELECT deyimler tarafından döndürülen sütunların null atanabilirliğine bakılmaksızın null atanabilir.

  • 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. Sonsuz döngünün önüne geçmek için, , , veya MAXRECURSION deyiminin yan tümcesinde ve arasında 032767OPTION bir ipucu ve bir değer kullanarak INSERT belirli bir deyim için izin verilen özyineleme düzeylerinin UPDATEDELETEsayısını sınırlayabilirsinizSELECT. Bu, döngü oluşturan kod sorununu çözene kadar deyiminin yürütülmesini denetlemenizi sağlar. Sunucu genelinde varsayılan değer 100'dür. 0 belirtildiğinde hiçbir sınır uygulanmaz. Deyim başına yalnızca bir MAXRECURSION değer belirtilebilir. Daha fazla bilgi için bkz. Sorgu ipuçları.

  • Özyinelemeli ortak tablo ifadesi içeren bir görünüm, verileri güncelleştirmek için kullanılamaz.

  • İmleçler, CTA'lar kullanılarak sorgularda tanımlanabilir. CTE, imlecin sonuç kümesini tanımlayan select_statement bağımsız değişkenidir. Özyinelemeli CTE'ler için yalnızca hızlı ileriye doğru ve statik (anlık görüntü) imleçlere izin verilir. Özyinelemeli bir CTE'de başka bir imleç türü belirtilirse, imleç türü statik olarak dönüştürülür.

  • Uzak sunuculardaki tablolara CTE'de başvurulabilir. CTE'nin özyinelemeli üyesinde uzak sunucuya başvurulursa, her uzak tablo için bir biriktirici oluşturulur, böylece tablolara yerel olarak tekrar tekrar erişilebilir. Bu bir CTE sorgusuysa, sorgu planında Dizin Biriktiricisi/Gecikmeli Biriktiriciler görüntülenir ve ek WITH STACK koşula sahip olur. Bu, doğru özyinelemesi onaylamanın bir yoludur.

  • CTE'nin özyinelemeli bölümündeki analiz ve toplama işlevleri, CTE kümesine değil, geçerli özyineleme düzeyi için kümeye uygulanır. CTE'nin özyinelemeli bölümüne geçirilen veri kümesinin tamamına değil, yalnızca geçerli özyineleme düzeyine göre geçirilen verilerin alt kümesinde çalışma gibi ROW_NUMBER işlevler. Daha fazla bilgi için bkz. örnek I. Aşağıdaki özyinelemeli CTE'de analitik işlevleri kullanma.

Azure Synapse Analytics ve Analytics Platform Sistemi'nde (PDW) yaygın tablo ifadeleri

Azure Synapse Analytics ve Analytics Platform Sistemi'nde (PDW) CTE'lerin geçerli uygulaması aşağıdaki özelliklere ve gereksinimlere sahiptir:

  • Bir deyimde SELECT CTE belirtilebilir.

  • Bir deyimde CREATE VIEW CTE belirtilebilir.

  • CTE bir CREATE TABLE AS SELECT (CTAS) deyiminde belirtilebilir.

  • CTE bir CREATE REMOTE TABLE AS SELECT (CRTAS) deyiminde belirtilebilir.

  • CTE bir CREATE EXTERNAL TABLE AS SELECT (CETAS) deyiminde belirtilebilir.

  • CTE'den uzak bir tabloya başvurulabilir.

  • CTE'den dış tabloya başvurulabilir.

  • CTE'de birden çok CTE sorgu tanımı tanımlanabilir.

  • CTE'yi , , SELECT, INSERT, UPDATEveya DELETE deyimleri takip MERGEedebilir.

  • Kendisine başvurular içeren ortak tablo ifadesi (özyinelemeli bir ortak tablo ifadesi) desteklenmez.

  • CTE'de birden WITH fazla yan tümce belirtilmesine izin verilmez. Örneğin, bir CTE sorgu tanımı bir alt sorgu içeriyorsa, bu alt sorgu başka bir CTE'yi tanımlayan iç içe WITH bir yan tümce içeremez.

  • Yan ORDER BY tümce, bir yan tümce belirtildiği durumlar dışında TOP kullanılamaz.

  • Bir toplu iş parçası olan bir deyimde bir CTE kullanıldığında, önce deyimi noktalı virgül tarafından takip edilmelidir.

  • tarafından sp_preparehazırlanan deyimlerde kullanıldığında, CTA'lar APS PDW'deki diğer SELECT deyimlerle aynı şekilde davranır. Ancak, CTA'lar tarafından sp_preparehazırlanan CETAS'ın bir parçası olarak kullanılıyorsa, bağlamanın için sp_prepareuygulanma şekli nedeniyle davranış SQL Server'dan ve diğer APS PDW deyimlerinden ertelenebilir. Bu, CTE'ye başvuruda bulunursa CTE'de bulunmayan yanlış bir sütun kullanıyorsa SELECT , sp_prepare hata algılanmadan geçer, ancak bunun yerine hata oluşur sp_execute .

Examples

A. Ortak tablo ifadesi oluşturma

Aşağıdaki örnekte Adventure Works Cycles'taki her satış temsilcisi için yıllık toplam satış siparişi sayısı gösterilmektedir.

-- 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. Sayıları ve rapor ortalamalarını sınırlamak için ortak bir tablo ifadesi kullanma

Aşağıdaki örnekte, satış temsilcileri için tüm yılların ortalama satış siparişi sayısı gösterilmektedir.

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. Tek bir sorguda birden çok CTE tanımı kullanma

Aşağıdaki örnekte, tek bir sorguda birden fazla CTE'nin nasıl tanımlanacağı gösterilmektedir. CTE sorgu tanımlarını ayırmak için virgül kullanılır. FORMAT Parasal tutarları para birimi biçiminde görüntülemek için kullanılan işlev, SQL Server 2012'de (11.x) kullanıma sunulmuştur.

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
     INNER JOIN Sales_Quota_CTE
         ON Sales_Quota_CTE.BusinessEntityID = Sales_CTE.SalesPersonID
        AND Sales_CTE.SalesYear = Sales_Quota_CTE.SalesQuotaYear
ORDER BY SalesPersonID, SalesYear;

Kısmi bir sonuç kümesi aşağıdadır.

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. Birden çok özyineleme düzeyini görüntülemek için özyinelemeli ortak tablo ifadesi kullanma

Aşağıdaki örnekte yöneticilerin ve onlara rapor veren çalışanların hiyerarşik listesi gösterilmektedir. Örnek, tabloyu oluşturup doldurarak dbo.MyEmployees başlar.

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

özyinelemenin iki düzeyini görüntülemek için özyinelemeli ortak tablo ifadesi kullanma

Aşağıdaki örnek, yöneticileri ve onlara rapor eden çalışanları gösterir. Döndürülen düzey sayısı iki ile sınırlıdır.

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;

Hiyerarşik liste görüntülemek için özyinelemeli ortak tablo ifadesi kullanma

Aşağıdaki örnek, yönetici ve çalışanların adlarını ve ilgili başlıklarını ekler. Yöneticilerin ve çalışanların hiyerarşisi, her düzeyin girintilenmesiyle de vurgulanır.

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
         INNER JOIN DirectReports AS d
             ON e.ManagerID = d.EmployeeID
)
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM DirectReports
ORDER BY Sort;

Bir deyimi iptal etmek için MAXRECURSION kullanma

MAXRECURSION düşük biçimlendirilmiş özyinelemeli bir CTE'nin sonsuz döngüye girmesini önlemek için kullanılabilir. Aşağıdaki örnek kasıtlı olarak sonsuz bir döngü oluşturur ve özyineleme düzeylerinin sayısını iki ile sınırlamak için MAXRECURSION ipucunu kullanır.

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

Kodlama hatası düzeltildikten sonra MAXRECURSION artık gerekli değildir. Aşağıdaki örnekte düzeltilen kod gösterilmektedir.

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
         INNER JOIN cte
             ON e.ManagerID = cte.EmployeeID
)
SELECT EmployeeID, ManagerID, Title
FROM cte;

E. SELECT deyimindeki özyinelemeli ilişkide seçmeli olarak adım adım ilerletmek için ortak tablo ifadesi kullanma

Aşağıdaki örnekte için bisiklet oluşturmak için ProductAssemblyID = 800gereken ürün derlemeleri ve bileşenleri hiyerarşisi gösterilmektedir.

USE AdventureWorks2022;
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. UPDATE deyiminde özyinelemeli CTE kullanma

Aşağıdaki örnek, ürününü PerAssemblyQtyderlemek 'Road-550-W Yellow, 44' (ProductAssemblyID 800) için kullanılan tüm parçaların değerini güncelleştirir. Ortak tablo ifadesi, derlemek ProductAssemblyID 800 için kullanılan parçaların hiyerarşik bir listesini ve bu parçaları oluşturmak için kullanılan bileşenleri vb. döndürür. Yalnızca ortak tablo ifadesi tarafından döndürülen satırlar değiştirilir.

USE AdventureWorks2022;
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
     INNER JOIN Parts AS d
         ON c.ProductAssemblyID = d.AssemblyID
WHERE d.ComponentLevel = 0;

H. Birden çok tutturucu ve özyinelemeli üye kullanma

Aşağıdaki örnek, belirtilen bir kişinin tüm atalarını döndürmek için birden çok tutturucu ve özyinelemeli üye kullanır. Özyinelemeli CTE tarafından döndürülen aile soy ağacını oluşturmak için bir tablo oluşturulur ve değerler eklenir.

-- 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. Özyinelemeli CTE'de analitik işlevleri kullanma

Aşağıdaki örnekte, CTE'nin özyinelemeli bölümünde analitik veya toplama işlevi kullanılırken oluşabilecek bir tuzak gösterilmektedir.

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 BIGINT) 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
         INNER JOIN vw AS t
             ON t.itmID = r.itmIDComp
)
SELECT Lvl, N FROM r;

Aşağıdaki sonuçlar sorgu için beklenen sonuçlardır.

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

Aşağıdaki sonuçlar sorgunun gerçek sonuçlarıdır.

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

N CTE'nin özyinelemeli bölümünün her geçişi için 1 döndürür çünkü bu özyineleme düzeyine ait verilerin yalnızca alt kümesi öğesine ROWNUMBERgeçirilir. Sorgunun özyinelemeli bölümünün yinelemelerinin her biri için öğesine yalnızca bir satır geçirilir ROWNUMBER.

Örnekler: Azure Synapse Analytics ve Analytics Platform Sistemi (PDW)

J. CTAS deyimi içinde ortak tablo ifadesi kullanma

Aşağıdaki örnek, Adventure Works Cycles'taki her satış temsilcisi için yıllık toplam satış siparişi sayısını içeren yeni bir tablo oluşturur.

USE AdventureWorks2022;
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. CETAS deyimi içinde ortak tablo ifadesi kullanma

Aşağıdaki örnek, Adventure Works Cycles'taki her satış temsilcisi için yıllık toplam satış siparişi sayısını içeren yeni bir dış tablo oluşturur.

USE AdventureWorks2022;
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. Deyimde birden çok virgülle ayrılmış CTS kullanma

Aşağıdaki örnek, tek bir deyimde iki CTA'nın dahil olduğunu gösterir. CTE'ler iç içe yerleştirilemiyor (özyineleme yok).

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;

İlgili içerik

  • Last updated on