Ortak Tablo İfadeleri ile sorguları düzenleme

12 dakika

Karmaşık sorgularla çalışırken mantığı yönetilebilir parçalara ayırmanız veya aynı alt sorguya birden çok kez başvurmanız gerekir. Ortak Tablo İfadeleri (CTEs), yalnızca tek bir sorgu sırasında var olan geçici adlandırılmış sonuç kümelerini tanımlamanın bir yolunu sağlayarak kodunuzu daha okunabilir ve sürdürülebilir hale getirir.

CTE söz dizimlerini anlama

Ortak Tablo İfadesi yan tümcesi WITH kullanılarak tanımlanır ve ardından CTE adı, isteğe bağlı sütun listesi ve sonuç kümesini tanımlayan bir sorgu gelir. Daha sonra CTE'ye sonraki SELECT, , INSERTUPDATEveya DELETE deyiminde başvurulabilir.

WITH CTE_Name (Column1, Column2)
AS
(
    -- CTE query definition
    SELECT Column1, Column2
    FROM SomeTable
    WHERE SomeCondition = 'Value'
)
SELECT * FROM CTE_Name;

CTE'ler türetilmiş tablolara ve alt sorgulara göre çeşitli avantajlar sunar:

Geliştirilmiş okunabilirlik: Karmaşık sorguların adlandırılmış mantıksal bölümlere ayrıldığında anlaşılması daha kolay hale gelir
Kendi kendine başvuran özellik: Özyinelemeli CTE'ler kendilerine başvurarak hiyerarşik veri dolaşımı sağlayabilir
Birden çok başvuru: Dış sorguda yeniden tanımlamadan aynı CTE'ye birden çok kez başvuruda bulunma
Modüler tasarım: Birden çok CTE tanımlayarak karmaşık sorguları artımlı olarak oluşturma

Uyarı

CTA'lar yalnızca sorgu yürütme sırasında var olan geçici sonuç kümeleridir. Geleneksel tabloların aksine, bunları kullanan deyimin ötesinde kalıcı olmazlar ve açık temizleme gerektirmezler.

Özyinelemesiz CTE'ler oluşturma

Özyinelemeli olmayan CTA'lar, kendisine başvurmayan basit bir sorguyu temel alan bir sonuç kümesi tanımlar. Bu düzen karmaşık birleşimleri basitleştirmek, çok adımlı hesaplamaları bölmek veya kod düzenlemesini geliştirmek için kullanışlıdır.

Aşağıdaki örnekte, ürün bilgilerine katılmadan önce satış ölçümlerini hesaplamak için bir CTE kullanılır:

WITH SalesSummary AS
(
    SELECT 
        ProductID,
        SUM(OrderQty) AS TotalQuantity,
        SUM(LineTotal) AS TotalRevenue,
        COUNT(DISTINCT SalesOrderID) AS OrderCount
    FROM SalesLT.SalesOrderDetail
    GROUP BY ProductID
)
SELECT 
    p.Name AS ProductName,
    p.ProductNumber,
    p.ListPrice,
    ss.TotalQuantity,
    ss.TotalRevenue,
    ss.OrderCount,
    ss.TotalRevenue / NULLIF(ss.TotalQuantity, 0) AS AverageUnitPrice
FROM SalesLT.Product AS p
INNER JOIN SalesSummary AS ss
    ON p.ProductID = ss.ProductID
ORDER BY ss.TotalRevenue DESC;

Bu sorgu ilk olarak sipariş ayrıntılarını ürüne göre toplayan, satılan toplam miktarı, toplam geliri ve sipariş sayısını hesaplayan adlı SalesSummary bir CTE oluşturur. Ardından ana sorgu, satış ölçümleriyle birlikte ürün adlarını görüntülemek için bu CTE'yi tabloyla Product birleştirir. işlevi, NULLIF ortalama birim fiyatı hesaplarken sıfıra bölmeyi engeller.

Birden çok CTE'yi virgülle ayırarak tek bir WITH ifadesinde tanımlayabilirsiniz. Daha sonraki CTE'ler öncekilere başvurarak aşamalı veri dönüştürmeyi etkinleştirebilir:

WITH CategorySales AS
(
    SELECT 
        p.ProductCategoryID,
        SUM(sod.LineTotal) AS CategoryRevenue
    FROM SalesLT.Product AS p
    INNER JOIN SalesLT.SalesOrderDetail AS sod
        ON p.ProductID = sod.ProductID
    GROUP BY p.ProductCategoryID
),
RankedCategories AS
(
    SELECT 
        ProductCategoryID,
        CategoryRevenue,
        RANK() OVER (ORDER BY CategoryRevenue DESC) AS RevenueRank
    FROM CategorySales
)
SELECT 
    pc.Name AS CategoryName,
    rc.CategoryRevenue,
    rc.RevenueRank
FROM RankedCategories AS rc
INNER JOIN SalesLT.ProductCategory AS pc
    ON rc.ProductCategoryID = pc.ProductCategoryID
WHERE rc.RevenueRank <= 5;

Tavsiye

Birden çok CTE ile sorgu oluştururken, en ayrıntılı veri dönüştürmeleriyle başlayın ve ardından sonraki CTE'lerde aşamalı olarak toplama veya filtreleme yapın. Bu yaklaşım, her bir CTE'yi bağımsız olarak test ettiğiniz için hata ayıklamayı kolaylaştırır.

Özyinelemeli CTE'ler oluşturma

Özyinelemeli CTA'lar hiyerarşik veya graf benzeri veri yapılarını işlemek için kendilerine başvurur. Özyinelemeli CTE iki bölümden oluşur: ilk sonuç kümesini sağlayan bir tutturucu üye ve önceki sonuçları oluşturmak için CTE'ye başvuran özyinelemeli bir üye.

Özyinelemeli bir CTE için genel söz dizimi şöyledir:

WITH RecursiveCTE AS
(
    -- Anchor member: starting point
    SELECT columns
    FROM table
    WHERE starting_condition
    
    UNION ALL
    
    -- Recursive member: references the CTE
    SELECT columns
    FROM table
    INNER JOIN RecursiveCTE
        ON join_condition
)
SELECT * FROM RecursiveCTE;

Yaygın bir kullanım örneği, bir kuruluş hiyerarşisinde gezinmektir. Her çalışanın yöneticisinin bulunduğu bir çalışan tablosu düşünün:

WITH EmployeeHierarchy AS
(
    -- Anchor: Start with top-level managers (no manager)
    SELECT 
        EmployeeID,
        FirstName,
        LastName,
        ManagerID,
        0 AS Level,
        CAST(FirstName + ' ' + LastName AS NVARCHAR(500)) AS HierarchyPath
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    
    UNION ALL
    
    -- Recursive: Find employees who report to previously found employees
    SELECT 
        e.EmployeeID,
        e.FirstName,
        e.LastName,
        e.ManagerID,
        eh.Level + 1,
        CAST(eh.HierarchyPath + ' > ' + e.FirstName + ' ' + e.LastName AS NVARCHAR(500))
    FROM HumanResources.Employee AS e
    INNER JOIN EmployeeHierarchy AS eh
        ON e.ManagerID = eh.EmployeeID
)
SELECT 
    EmployeeID,
    FirstName,
    LastName,
    Level,
    HierarchyPath
FROM EmployeeHierarchy
ORDER BY HierarchyPath;

Önemli

Özyinelemeli CTE'ler sonlandırma koşulu sağlanmadığında sonsuz döngülere neden olabilir. SQL Server, özyineleni varsayılan olarak 100 düzeyle sınırlar. OPTION (MAXRECURSION n)'ı kullanarak bu sınırı değiştirin, burada n en fazla özyineleme derinliğidir (sınırsız için 0).

Özyinelemeli CTE'ler ile diziler oluşturma

Özyinelemeli CTE'ler, fiziksel sayılar tablosuna gerek kalmadan sayı veya tarih dizileri oluşturma konusunda çok başarılıdır. Bu teknik tarih aralıkları oluşturmak, verilerdeki boşlukları doldurmak veya test verileri oluşturmak için kullanışlıdır:

-- Generate a sequence of dates for the current month
WITH DateSequence AS
(
    -- Anchor: Get the first day of the current month
    SELECT CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS DATE) AS DateValue
    
    UNION ALL
    
    -- Recursive: Add one day until we reach the end of the month
    SELECT DATEADD(DAY, 1, DateValue)
    FROM DateSequence
    WHERE DateValue < EOMONTH(GETDATE())
)
-- Output each date with its day name
SELECT DateValue, DATENAME(WEEKDAY, DateValue) AS DayName
FROM DateSequence
OPTION (MAXRECURSION 31);  -- Allow up to 31 iterations (max days in a month)

Boşlukları belirlemek veya özet raporlar oluşturmak için oluşturulan dizileri gerçek verilerle birleştirebilirsiniz:

-- Generate a numbers table from 1 to 1000
WITH Numbers AS
(
    -- Anchor: Start with 1
    SELECT 1 AS n
    UNION ALL
    -- Recursive: Increment until we reach 1000
    SELECT n + 1 FROM Numbers WHERE n < 1000
),
-- Convert numbers to dates for the entire year
DateRange AS
(
    SELECT DATEADD(DAY, n - 1, '2024-01-01') AS OrderDate
    FROM Numbers
    WHERE DATEADD(DAY, n - 1, '2024-01-01') <= '2024-12-31'
)
-- Count orders for each date, showing 0 for dates with no orders
SELECT 
    dr.OrderDate,
    COALESCE(COUNT(soh.SalesOrderID), 0) AS OrderCount
FROM DateRange AS dr
LEFT JOIN SalesLT.SalesOrderHeader AS soh
    ON CAST(soh.OrderDate AS DATE) = dr.OrderDate
GROUP BY dr.OrderDate
ORDER BY dr.OrderDate
OPTION (MAXRECURSION 366);  -- Allow up to 366 iterations (leap year)

CTE'leri veri değişikliği ifadeleri ile birlikte kullanın

CTE'ler INSERT, UPDATE, ve DELETE deyimleriyle, yalnızca SELECT ile değil, kullanılabilir. Bu özellik, değişiklik mantığı karmaşık filtreleme veya hesaplamalar gerektirdiğinde kullanışlıdır:

-- Update using a CTE to identify target rows
WITH DiscontinuedProducts AS
(
    SELECT ProductID
    FROM SalesLT.Product
    WHERE SellEndDate < DATEADD(YEAR, -2, GETDATE())
        AND ProductID NOT IN (
            SELECT DISTINCT ProductID 
            FROM SalesLT.SalesOrderDetail
            WHERE ModifiedDate > DATEADD(YEAR, -1, GETDATE())
        )
)
UPDATE SalesLT.Product
SET DiscontinuedDate = GETDATE()
WHERE ProductID IN (SELECT ProductID FROM DiscontinuedProducts);

Bu sorgu, sonlandırıldı olarak işaretlenmesi gereken ürünleri tanımlamak için bir CTE kullanır. CTE, satılan bitiş tarihinin iki yıldan uzun bir süre önce olduğu ve geçen yıl içinde değiştirilen sipariş ayrıntılarında görünmemiş ürünleri bulur. Daha sonra UPDATE ifadesi, bu ürünler için DiscontinuedDate ayarını yapar. Seçim mantığını bir CTE'ye ayırarak sorgunun okunup bağımsız olarak testlenmesi kolaylaşır.

Ortak Tablo İfadeleri hakkında daha fazla bilgi için bkz . WITH common_table_expression (Transact-SQL) ve Ortak Tablo İfadelerini Kullanan Özyinelemeli Sorgular.

Geri Bildirim

Bu sayfayı yararlı buldunuz mu?