Özyinelemeli sorgular ortak tablo ifadeleri kullanma
Ortak bir tablo ifade (cte) kendisi, böylece bir özyinelemeli cte oluşturma başvurusu yapabilmek önemli avantajı sağlar.İlk bir cte tekrar tekrar tam sonuç küme alınana kadar veri alt kümeleri döndürmek için yürütüldüğü bir özyinelemeli cte biridir.
Ne zaman onu bir özyinelemeli cte başvuran bir özyinelemeli sorgulamak için bir sorgu denir.Hiyerarşik veri döndüren bir çok özyinelemeli sorgular, örneğin kullanılır: Kuruluş şeması görüntüleme çalışanları veya bir ürün, ana ürün bir veya daha fazla bileşenleri ve bu bileşenlerin, sırasıyla olabilir malzemeleri senaryonun veri alt bileşenleri sahip veya diğer Ebeveynler bileşenleri olabilir.
Özyinelemeli cte bir seçin, Ekle, update, delete veya create VIEW deyim içinde özyinelemeli sorgu çalıştırmak için gereken kodu büyük ölçüde basitleştirir.Önceki sürümlerinde SQL Serverözyinelenen bir sorgu genellikle gerektirdiği geçici tablolara, İmleçler ve mantık akışını denetlemek için kullanmaözyinelemeli adımları. Ortak tablo ifadeleri hakkında daha fazla bilgi için bkz: Ortak tablo ifadeleri kullanma.
Özyinelemeli cte yapısı
Diğer programlama dillerinde özyinelemeli yordamları Transact-sql içerisinde cte özyinelemeli yapısını benzer.Diğer dillerde bir özyinelemeli yordam, skaler bir değer döndüren olsa da, bir özyinelemeli cte birden çok satır döndürür.
Bir özyinelemeli cte üç öğelerden oluşur:
Yordam çağırma.
Özyinelemeli cte ilk çağrılmasını biri veya birkaçı oluşur CTE_query_definitions UNION all, BİRLİĞİ, DIŞINDAKİLER, katılan veya KESİŞEN operators.Çünkü bu sorgu tanımlarını form Bankası sonuç küme cte yapısı, bunlar için çapa üyeleri denir.
CTE_query_definitions cte oldukları sürece bağlantı üye olarak kabul edilir.Tüm bağlama üyesi sorgu tanımlamaları ilk özyinelemeli üye tanım önce yerleştirilmelidir ve UNION all işleç için kullanılan birleştirmek ilk özyinelemeli üye ile son bağlama üyesi.
Özyinelemeli çağırma yordamının.
Özyinelemeli çağırma birini veya daha fazlasını içeren CTE_query_definitions cte başvuran UNION all operatörleri tarafından katıldı.Bu sorgu tanımlarını özyinelemeli üyeleri denir.
Sonlandırma onay.
Sonlandırma onay örtülü; hiçbir satır önceki çağırma döndürülen özyineleme durur.
Not
Hatalı oluşan bir özyinelemeli cte sonsuz bir döngüye neden olabilir.Örneğin, üst ve alt sütunlar için aynı değerleri özyinelemeli üye sorgu tanımını verir, sonsuz bir döngüye oluşturulur.Test edilirken sonuçlar özyinelenen bir sorgu'nın MAXRECURSION ipucu ve 0 ile 32.767 arasında bir değer INSERT, update, DELETE seçeneği yan tümce tümce tümcesini kullanarak belirli bir deyim izin özyineleme düzeyi sayısını sınırlayabilirsiniz., veya deyim.Daha fazla bilgi için bkz: Sorgu ipuçları (Transact-sql) ve Common_table_expression (Transact-sql) ile.
Bir yarý semantiği
Özyinelemeli cte yapısı en az birini içermelidir bağlama üyesi ve bir özyinelemeli üye.Bir basit içeren tek bir cte özyinelemeli bileşenleri aşağıdaki yarı kod gösterilir bağlama üyesi ve tek özyinelemeli üye.
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 Özyinelemeli yürütme aşağıdaki gibidir:
cte bölme ifade tutturucu ve özyinelemeli üyeleri içine.
Çalıştır bağlama üye(s) oluşturma ilk çağırma veya temel sonuç küme (t0).
Özyinelemeli çalıştırmak üye(s) t ilei olarak girdi ve ti + 1 bir çıkış olarak.
Adımı yineleyin 3 kadar boş bir küme döndürülür.
Sonuç küme döndürür.Bu, bir BİRLİĞİ tüm t,0 tn.
Örnek
Aşağıdaki örnekte gösterildiği semantik çalışanlar hiyerarşik listesi döndürerek tarafından özyinelemeli cte yapısı, de en yüksek derecelendirme çalışanı ile başlayan Adventure Works Cycles şirketi.İzlenecek yol kod yürütme örnek aşağıda verilmiştir.
-- 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, 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 izlenecek yol
Özyinelemeli cte, DirectReports, biri tanımlar bağlama üyesi ve bir özyinelemeli üye.
bağlama üye t temel sonuç küme döndürür0.En yüksek derecelendirme çalışanın şirketteki olmasıdır; diğer bir deyişle, bir yöneticiye rapor bir çalışanı.
İşte tarafından döndürülen sonuç küme bağlama üye:
ManagerID EmployeeID Title Level --------- ---------- ----------------------------- ------ NULL 1 Chief Executive Officer 0Özyinelemeli üye içinde çalışanın doğrudan subordinate(s) verir bağlama üyesi sonuç küme.Bu bir birleştirmek işlemi tarafından sağlanır Employee Tablo ve DirectReports cte.Özyinelemeli çağırma kurar, cte kendisini bu başvurusudur.cte çalışana göre DirectReports Giriş (ti), birleştirmek (MyEmployees.ManagerID = DirectReports.EmployeeID) çıktı olarak verir (ti + 1), sahip çalışanlar (ti) kendi Yöneticisi olarak.Bu nedenle, ilk tekrarında özyinelemeli üye bu sonuç küme döndürür:
ManagerID EmployeeID Title Level --------- ---------- ----------------------------- ------ 1 273 Vice President of Sales 1Özyinelemeli üye art arda devreye girer.The second iteration of the recursive member uses the single-row result set in step 3 (containing EmployeeID273) as the input value, and returns this result set:
ManagerID EmployeeID Title Level --------- ---------- ----------------------------- ------ 273 16 Marketing Manager 2 273 274 North American Sales Manager 2 273 285 Pacific Sales Manager 2Özyinelemeli üçüncü tekrarında üye giriş değer olarak yukarıda küme sonuç kullanır ve bu sonuç küme 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 3En son sonuç küme çalışan sorgu tarafından döndürülen tüm birleşimdir sonuç kümesitutturucu ve özyinelemeli üyeleri tarafından üretilen s.
Örnek tarafından döndürülen tam sonuç küme 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