Organisera frågor med Vanliga tabelluttryck

12 minuter

När du arbetar med komplexa frågor måste du ofta dela upp logiken i hanterbara delar eller referera till samma underfråga flera gånger. Common Table Expressions (CTE) är ett sätt att definiera tillfälliga namngivna resultatuppsättningar som bara finns under en enda fråga, vilket gör koden mer läsbar och underhållsbar.

Förstå CTE-syntax

Ett gemensamt tabelluttryck definieras med hjälp av WITH-satsen, följt av CTE-namnet, en valfri kolumnlista och en fråga som definierar resultatuppsättningen. Sedan kan CTE refereras till i den efterföljande SELECT, INSERT, UPDATE eller DELETE-instruktionen.

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

CTE:er har flera fördelar jämfört med härledda tabeller och underfrågor:

Förbättrad läsbarhet: Komplexa frågor blir lättare att förstå när de delas in i namngivna logiska avsnitt
Självrefererande funktion: Rekursiva CTE:er kan referera till sig själva, vilket möjliggör hierarkisk databläddring
Flera referenser: Referera till samma CTE flera gånger i den yttre frågan utan att definiera om den
Modulär design: Skapa komplexa frågor stegvis genom att definiera flera CTE:er

Anmärkning

CTE är tillfälliga resultatuppsättningar som bara existerar under utförandet av frågan. Till skillnad från traditionella tabeller bevaras de inte utöver instruktionen som använder dem och kräver inte explicit rensning.

Skapa icke-rekursiva CTE:er

Icke-cursiva CTE:er definierar en resultatuppsättning baserat på en enkel fråga som inte refererar till sig själv. Det här mönstret är användbart för att förenkla komplexa kopplingar, dela upp beräkningar i flera steg eller förbättra kodorganisationen.

I följande exempel används en CTE för att beräkna försäljningsmått innan det ansluts till produktinformationen.

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;

Den här frågan skapar först en CTE med namnet SalesSummary som aggregerar orderinformation efter produkt, beräknar totalt antal sålda kvantiteter, totala intäkter och orderantal. Huvudsökfrågan kopplar sedan denna CTE till Product-tabellen för att visa produktnamn tillsammans med deras försäljningsmått. Funktionen NULLIF förhindrar division med noll vid beräkning av genomsnittligt enhetspris.

Du kan definiera flera CTE:er i en enda WITH sats genom att avgränsa dem med kommatecken. Senare CTE:er kan referera till tidigare, vilket möjliggör progressiv datatransformering:

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;

Tips/Råd

När du skapar frågor med flera CTE:er börjar du med de mest detaljerade datatransformeringarna och sammanställer eller filtrerar progressivt i efterföljande CTE:er. Den här metoden gör felsökning enklare eftersom du kan testa varje CTE oberoende av varandra.

Skapa rekursiva CTE:er

Rekursiva CTE:er refererar till att bearbeta hierarkiska eller grafliknande datastrukturer. En rekursiv CTE består av två delar: en fästpunktsmedlem som tillhandahåller den första resultatuppsättningen och en rekursiv medlem som refererar till CTE för att bygga vidare på tidigare resultat.

Den allmänna syntaxen för en rekursiv CTE är:

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;

Ett vanligt användningsfall är att korsa en organisationshierarki. Överväg en tabell med anställda där varje anställd har en chef:

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;

Viktigt!

Rekursiva CTE:er kan orsaka oändliga loopar om avslutningsvillkoret aldrig uppfylls. SQL Server begränsar rekursionen till 100 nivåer som standard. Använd OPTION (MAXRECURSION n) för att ändra den här gränsen, där n är det maximala rekursionsdjupet (0 för obegränsat).

Generera sekvenser med rekursiva CTE:er

Rekursiva CTE:er utmärker sig för att generera sekvenser med tal eller datum utan att kräva en fysisk taltabell. Den här tekniken är användbar för att skapa datumintervall, fylla luckor i data eller generera testdata:

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

Du kan kombinera genererade sekvenser med faktiska data för att identifiera luckor eller skapa sammanfattningsrapporter:

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

Använd CTE:er med datamodifieringssatser

CTE:er kan användas med INSERT, UPDATEoch DELETE -instruktioner, inte bara SELECT. Den här funktionen är användbar när ändringslogik kräver komplex filtrering eller beräkningar:

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

Den här frågan använder en CTE för att identifiera produkter som ska markeras som utgående. CTE hittar produkter där det datum då de såldes är mer än två år sedan och som inte har dykt upp i några orderdetaljer som ändrats under det senaste året. Utsagan UPDATE ställer sedan in DiscontinuedDate för dessa produkter. Genom att separera urvalslogik i en CTE blir frågan enklare att läsa och testa oberoende av varandra.

Mer information om Vanliga tabelluttryck finns i WITH common_table_expression (Transact-SQL) och Rekursiva frågor med vanliga tabelluttryck.

Feedback

Var den här sidan till hjälp?