Common_table_expression (Transact-SQL) ile

Bir ortak tabloifade (cte) bilinen sonuç kümesi, adlı geçici belirtir. Bu basit bir sorgudan elde edilen ve yürütme kapsam tek içinde tanımlanan select, INSERT, update, birleştirme veya delete deyim.Bu yan tümce create VIEW deyim tanımlayan select deyimbir parçası olarak da kullanılabilir.Ortak bir tablo ifade kendisi başvurular içerebilir.Bu, bir özyinelemeli ortak tablo ifadeadlandırılır.

Transact-SQL sözdizimi kuralları

Sözdizimi

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

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

Bağımsız değişkenler

• expression_name
  Ortak tablo ifadeiçin geçerli bir tanıtıcı iş. expression_name gerekir herhangi diğer ortak tablo ifade adından farklı tanımlı aynı ile de <common_table_expression> yan tümce, ancak expression_name olabilir aynı temel tablo veya görünüm.Tüm başvuru expression_name ortak tablo ifade ve temel nesnedeğil sorguda kullanır.

• column_name
  Ortak tabloifade sütun adını belirtir. Yinelenen adları tek bir cte tanımı içinde izin verilmez.Belirtilen sütun adlarının sayısı sonuç kümesi , sütun sayısı eşleşmelidir CTE_query_definition.sütun adları listesini elde edilen tüm sütunlar için farklı ad sorgu tanımında kullanılmazsa isteğe bağlıdır.

• CTE_query_definition
  Ortak tabloifadedoldurur, sonuç kümesi select deyim belirtir. select deyim CTE_query_definition dışında bir cte olamaz tanýmlayýn başka bir cte bir görünüm oluşturmak için aynı gereklerine uymalıdırDaha fazla bilgi için açıklamalar bölümüne bakın ve CREATE VIEW (Transact-SQL).

  Birden fazla ise CTE_query_definition olan tanımlı, sorgu tanımlamaları küme bu işleçlerden birini katılması gerekir: UNION all, BİRLİĞİ, DIŞINDAKİLER, veya INTERSECT.Özyinelemeli cte sorgu tanımlarını kullanma hakkında daha fazla bilgi için aşağıdaki "Uyarılar" konusuna bakın. Bölüm ve Özyinelemeli sorgular ortak tablo ifadeleri kullanma.

Açıklamalar

Oluşturma ve ortak tablo ifadeleri kullanma yönergeleri

Özyinelemesiz ortak tablo ifadeleri için aşağıdaki yönergeleri uygulayın.Özyinelemeli ortak tablo ifadeleri için uygulama yönergeleri için bkz: "Yönergeleri için tanımlama ve kullanarak özyinelemeli ortak tablo ifadeleri" aşağıdaki gibidir.

• Bir cte bir tek select, INSERT, update tarafından birleştirme, uyulması gereken veya delete deyim bazı veya tüm cte sütun başvuran.Bir cte, create VIEW deyim görünümü tanımlayan select deyim bir parçası olarak da belirtilebilir.

• Birden çok cte sorgu tanımı özyinelemesiz cte tanımlanabilir.Tanımları küme bu işleçlerden birini tarafından birleştirilmiş olmalıdır: UNION all, UNION, INTERSECT veya DIŞINDAKİLER.

• Bir cte kendisini başvurabilir ve yan tümceile aynı CTEs önceden tanımlanmış.İleri başvuran izin verilmez.

• Birden çok yan tümce ile birlikte bir cte belirterek izin verilmez.Örneğin, bir CTE_query_definition alt sorguiçeren bu alt sorgu bir iç içe tanımlayan başka bir cte. yan tümce ile içeremez

• Aşağıdaki yan tümceleri kullanılamaz CTE_query_definition:

  • compute ve compute by

  • TARAFINDAN sipariş (dışında üst yan tümce olduğunda belirtilen)

  • İÇİNE

  • Sorgu ipuçları ile OPTION yan tümce

  • XML İÇİN

  • GÖZAT

• Ne zaman bir cte bir toplu iş, noktalı virgül uyulması gereken önce deyim parçası olan bir deyim kullanılır.

• Bir cte başvuran bir sorgu, bir imleçtanımlamak için kullanılabilir.

• İçinde cte tablolar uzak sunuculardaki başvurulabilir.

• Bir cte yürütürken bir cte başvuran ipuçları cte, onun altındaki tüm tablolar, sorgular görünümlere başvuru ipuçları aynı şekilde eriştiğinde, bulunduğunda diğer ipuçları ile çakışabilir.Bu durumda, sorgu hata verir.Daha fazla bilgi için, bkz. Görünüm çözümleme.

• Bir cte bir update deyim hedef olduğunda cte deyim içinde yapılan tüm başvurular eşleşmesi gerekir.cte bir from yan tümcetümcesinde diğer ad atanmış ise, örneğin, diğer ad cte için tüm diğer başvurular için kullanılması gerekir.Belirsiz cte başvuruları birleştirmek beklenmeyen davranış ve istenmeyen sorgu sonuçlarüretebilir.Daha fazla bilgi için, bkz. UPDATE (Transact-SQL).

Tanımlama ve özyinelemeli ortak tablo ifadeleri kullanma yönergeleri

Bir özyinelemeli ortak tablo ifadetanımlamak için aşağıdaki kurallar uygulanır:

• Özyinelemeli cte tanımı, en az iki cte sorgu tanımlamaları, bir bağlama üyesi ve özyinelemeli üyeiçermelidir.Birden çok bağlantı üyeleri ve özyinelemeli üyeleri tanımlanabilir; Ancak, tüm bağlama üyesi sorgu tanımlamaları ilk özyinelemeli üye tanımı önce geçirmeniz gerekir.Tüm cte sorgu tanımlarıcte oldukları sürece bağlantı üyeleridir.

• Çapa üyeleri küme bu işleçlerden birini birleşik olarak gerekir: UNION all, UNION, INTERSECT veya DIŞINDAKİLER.UNION all son bağlama üyesi ile ilk özyinelemeli üyeve özyinelemeli olarak birden çok üye birleştirilirken izin tek kümeişleç olarak.

• Tutturucu ve özyinelemeli Üyeler'deki sütun sayısı aynı olması gerekir.

• Özyinelemeli üye bir sütun veri türü bağlama üyesikarşılık gelen sütun veri türü ile aynı olmalıdır.

• cte özyinelemeli üye from yan tümce yalnızca bir saat başvurması gerekir expression_name.

• Aşağıdaki öğeler de izin verilmeyen CTE_query_definition bir özyinelemeli üye:

  • SELECT DISTINCT

  • GÖRE GRUPLANDIR

  • SAHİP OLMAK

  • Skalar toplama

  • ÜST

  • left, RIGHT outer JOIN (INNER JOIN verilir)

  • Alt sorgular

  • İçinde bir cte özyinelemeli başvuru uygulanan bir ipucu bir CTE_query_definition.

Bir özyinelemeli ortak tablo ifadekullanma için aşağıdaki kurallar uygulanır:

• Tüm sütunlar null null atanabilirlik katılımcı select deyimleri tarafından döndürülen sütun ne olursa olsun cte özyinelemeli tarafından döndürülen.

• Yanlış oluşan bir özyinelemeli cte sonsuz bir döngüye neden olabilir.Örneğin, hem üst hem de alt sütunlar için aynı değerleri özyinelemeli üye sorgu tanımını verir, sonsuz bir döngüye oluşturulur.Sonsuz bir döngüye önlemek için OPTION yan tümce INSERT, update, birleştirme, delete veya select deyimiçinde MAXRECURSION ipucu ve 0 ile 32,767 arasında bir değer kullanarak için belirli bir deyim izin özyineleme düzeyi sayısını sınırlayabilirsiniz.Bu döngü oluşturma kodu sorunu giderene kadar deyim yürütülmesini kontrol etmenize olanak sağlar.Sunucu çapında varsayılan değer 100'dür.0 Belirtilirse, sınır uygulanır.Tek bir MAXRECURSION değeri deyimbelirtilebilir.Daha fazla bilgi için, bkz. Sorgu ipuçları (Transact-SQL).

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

• İmleçler CTEs kullanarak sorgulamaları tanımlanabilir.cte olan select_statement sonuç kümesi imleçtanımlar değişken.Sadece fast forward-only ve statik (anlık görüntü) imleçler için özyinelemeli CTEs izin verilir.Özyinelemeli cte başka bir imleç türü belirtilmezse, imleç türü için statik dönüştürülür.

• Uzak sunuculardaki tablolara cte içinde başvurulan.Özyinelemeli üye cte uzak sunucu başvuruyorsa, tabloları sürekli olarak yerel olarak erişilebilir şekilde bir biriktirme her ' % s'tablo uzak tablo için oluşturulur.Bir cte sorgu ise, dizin biriktirme/yavaş biriktirir sorgu planında görüntülenir ve ek ile yığın yüklemi olur.Bu uygun özyineleme onaylamak için bir yoludur.

• cte özyinelemeli parçası Analitik ve toplu işlevleri küme geçerli özyineleme düzey ve değil küme için cte uygulanır.Satır_num gibi işlevleri, yalnızca kendilerine geçerli özyineleme düzey ve tüm küme veri tarafından cte özyinelemeli parçası pased iletilen veri alt küme üzerinde işlem yapar.Daha fazla bilgi için, bkz. K. Using analytical functions in a recursive CTE.

Örnekler

A.Basit bir ortak tabloifadeoluşturma

Sırasında her bir satış temsilcisi için toplam satış siparişlerinin sayısı, her yıl aşağıdaki örnekte gösterilmektedir Adventure Works Cycles.

USE AdventureWorks2008R2;
GO
-- 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

B.Sayar ve rapor ortalamalar sınırlamak için ortak bir tablo ifade kullanma

Aşağıdaki örnek, satış temsilcileri için satış siparişleri tüm yıl için ortalama sayısını gösterir.

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

C.Birden çok düzeyi özyineleme görüntülemek için bir özyinelemeli ortak tablo ifade kullanma

Aşağıdaki örnek, yöneticileri ve onlara rapor çalışanlar hiyerarşik listesini gösterir.Örnek oluşturma ve doldurma başlar dbo.MyEmployees tablo.

-- 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 int NULL,
 CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC) 
);
-- 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);

USE AdventureWorks2008R2;
GO
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;
GO

D.Özyineleme iki düzeylerini görüntülemek için bir özyinelemeli ortak tablo ifade kullanma

Aşağıdaki örnek, Yöneticiler ve çalışanlar kendilerine rapor gösterir.Döndürülen düzeylerinin sayısını iki olarak sınırlıdır.

USE AdventureWorks2008R2;
GO
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 ;
GO

E.Hiyerarşik bir listesini görüntülemek için bir özyinelemeli ortak tablo ifade kullanma

Aşağıdaki örnekte, yönetici ve çalışanları ve bunların ilgili başlıkları adlarını ekleyerek örnek c oluşturur.Yöneticiler ve çalışanlar hiyerarşisini, ayrıca her düzeygirintisini artırarak vurgulanır.

USE AdventureWorks2008R2;
GO
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;
GO

F.Bir deyimiptal etmek için MAXRECURSION kullanma

MAXRECURSIONyetersiz özyinelemeli cte sonsuz bir döngüye girmesini önlemek için kullanılır.Aşağıdaki örnek, kasıtlı olarak sonsuz döngü oluşturur ve kullanır MAXRECURSION iki özyineleme düzeyi sayısını sınırlamak için ipucu.

USE AdventureWorks2008R2;
GO
--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);
GO

Kodlama hatası düzeltildi sonra MAXRECURSION artık gerekli değildir.Aşağıdaki örnek, düzeltilmiş kodunu gösterir.

USE AdventureWorks2008R2;
GO
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;
GO

G.Seçime bağlı olarak bir select deyimiçinde bir özyinelemeli ilişki gözden geçirmek için ortak bir tabloifade kullanma

Aşağıdaki örnek ürün birleştirmeleri ve bisiklet için oluşturmak için gereken bileşenleri hiyerarşisini gösterir ProductAssemblyID = 800.

USE AdventureWorks2008R2;
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;
GO

H.Özyinelemeli cte bir update deyimkullanarak

Aşağıdaki örnek güncelleştirmeleri PerAssemblyQty değeri ' Road 550 w sarı, 44' ürün oluşturmak için kullanılan tüm bölümleri için (ProductAssemblyID800).Ortak tabloifade oluşturmak için kullanılan bölümleri hiyerarşik bir listesini döndürür ProductAssemblyID 800 ve bu parça ve oluşturmak için kullanılan bileşenleribenzeri. Yalnızca ortak tablo ifade tarafından döndürülen satır değiştirilir.

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

Ö.Tutturucu ve özyinelemeli olarak birden çok üye kullanma

Aşağıdaki örnek, belirtilen bir kişinin tüm öncüleri dönmek için birden çok bağlantı ve özyinelemeli üyeleri kullanır.Bir tablo oluşturulur ve değerleri özyinelemeli cte tarafından döndürülen Aile soy ağacı kurmak için 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

J.Özyinelemeli cte analitik işlevler kullanma

Aşağıdaki örnek bir Analitik veya toplu işlev bir cte özyinelemeli parçası kullanırken karşılaşılabilecek bir durumu gösterir.

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 
    JOIN vw AS t ON t.itmID = r.itmIDComp
) ;

SELECT Lvl, N FROM r

Aşağıdaki sonuçlar sorgusu için beklenen sonuçlar olur.

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

Aşağıdaki sonuçlar sorgu sonuçlar gerçek olur.

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

Ncte özyinelemeli parçası her geçişi için 1 verir, çünkü yalnızca alt küme veri için özyineleme düzey için geçirilen ROWNUMBER.Her sorgu özyinelemeli parçası yinelemelerini için yalnızca bir satır geçirilir ROWNUMBER.

Ayrıca bkz.

Başvuru

CREATE VIEW (Transact-SQL)

DELETE (Transact-SQL)

DIŞINDA ve INTERSECT (Transact-SQL)

INSERT (Transact-SQL)

SELECT (Transact-SQL)

UPDATE (Transact-SQL)

Kavramlar

Özyinelemeli sorgular ortak tablo ifadeleri kullanma

Ortak tablo ifadeleri kullanma