Organiser spørringer med felles tabelluttrykk

Fullført

Når du jobber med komplekse spørringer, må du ofte dele opp logikken i håndterbare deler eller referere til samme delspørring flere ganger. Common Table Expressions (CTEs) gir en måte å definere midlertidige navngitte resultatsett som kun eksisterer under én enkelt spørring, noe som gjør koden din mer lesbar og vedlikeholdbar.

Forstå CTE-syntaks

Et felles tabelluttrykk defineres ved hjelp av klausulen WITH , etterfulgt av CTE-navnet, en valgfri kolonneliste og en spørring som definerer resultatsettet. CTE-en kan deretter refereres til i den påfølgende SELECT, INSERT, UPDATE, eller DELETE setningen.

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

CTE-er tilbyr flere fordeler sammenlignet med avledede tabeller og underforespørsler:

• Forbedret lesbarhet: Komplekse spørringer blir lettere å forstå når de deles inn i navngitte logiske seksjoner
• Selvrefererende evne: Rekursive CTE-er kan referere til seg selv, noe som muliggjør hierarkisk datagjennomgang
• Flere referanser: Referer til samme CTE flere ganger i den ytre spørringen uten å omdefinere den
• Modulært design: Bygg komplekse spørringer inkrementelt ved å definere flere CTE-er

Bemerkning

CTE-er er midlertidige resultatsett som kun eksisterer under spørringsutførelse. I motsetning til tradisjonelle tabeller, varer de ikke lenger enn uttalelsen som bruker dem, og krever ikke eksplisitt opprydding.

Lag ikke-rekursive CTE-er

Ikke-rekursive CTE-er definerer et resultatsett basert på en enkel spørring som ikke refererer til seg selv. Dette mønsteret er nyttig for å forenkle komplekse joins, bryte ned flertrinnsberegninger eller forbedre kodeorganisering.

Følgende eksempel bruker en CTE for å beregne salgsmålinger før den kombineres med produktinformasjon:

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;

Denne spørringen oppretter først en CTE kalt SalesSummary som aggregerer ordredetaljer etter produkt, og beregner total solgt mengde, total inntekt og ordreantall. Hovedspørringen kobler deretter denne CTE-en til tabellen Product for å vise produktnavn sammen med deres salgsmålinger. Funksjonen NULLIF forhindrer divisjon med null når gjennomsnittlig enhetspris beregnes.

Du kan definere flere CTE-er i én WITH setning ved å skille dem med kommaer. Senere CTE-er kan referere til tidligere data, noe som muliggjør progressiv datatransformasjon:

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

Når du bygger spørringer med flere CTE-er, start med de mest detaljerte datatransformasjonene og aggreger eller filtrer gradvis i påfølgende CTE-er. Denne tilnærmingen gjør feilsøking enklere siden du kan teste hver CTE uavhengig.

Lag rekursive CTE-er

Rekursive CTE-er refererer til seg selv for å behandle hierarkiske eller graflignende datastrukturer. En rekursiv CTE består av to deler: et ankermedlem som gir det opprinnelige resultatsettet, og et rekursivt medlem som refererer til CTE for å bygge videre på tidligere resultater.

Den generelle syntaksen for en rekursiv CTE er:

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;

Et vanlig bruksområde er å navigere i et organisatorisk hierarki. Tenk deg en ansatttabell hvor hver ansatt har en leder:

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;

Viktig!

Rekursive CTE-er kan forårsake uendelige løkker hvis termineringsbetingelsen aldri oppfylles. SQL Server begrenser rekursjon til 100 nivåer som standard. Bruk OPTION (MAXRECURSION n) for å endre denne grensen, hvor n er maksimal rekursjonsdybde (0 for ubegrenset).

Generer sekvenser med rekursive CTE-er

Rekursive CTE-er utmerker seg i å generere sekvenser av tall eller datoer uten å kreve en fysisk talltabell. Denne teknikken er nyttig for å lage datointervaller, fylle hull i data eller generere 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 kombinere genererte sekvenser med faktiske data for å identifisere hull eller lage sammendragsrapporter:

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

Bruk CTE-er med dataendringssetninger

CTE-er kan brukes med INSERT, , og DELETE setninger, ikke bare SELECTUPDATE. Denne funksjonaliteten er nyttig når modifikasjonslogikken krever kompleks filtrering eller beregninger:

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

Denne spørringen bruker en CTE for å identifisere produkter som skal merkes som avviklet. CTE finner produkter hvor sluttdatoen for salg er mer enn to år siden og som ikke har dukket opp i noen ordredetaljer som er endret det siste året. Setningen UPDATE setter deretter for DiscontinuedDate disse produktene. Ved å skille ut utvalgslogikken i en CTE, blir spørringen lettere å lese og teste uavhengig.

For mer informasjon om Common Table Expressions, se WITH common_table_expression (Transact-SQL) og Rekursive spørringer som bruker Common Table Expressions.