Redgate Hub (Svenska)

introducerad i SQL Server 2005 är common table expression (CTE) en tillfällig namngiven resultatuppsättning som du kan referera till inom en SELECT, INSERT, UPDATE eller DELETE-sats. Du kan också använda en CTE i CREATE VIEW uttalande, som en del av vyn VÄLJER du fråga. Dessutom, från och med SQL Server 2008, kan du lägga till en CTE till den nya KOPPLINGSUTGÅVAN.

SQL Server stöder två typer av CTES-rekursiva och icke-rekursiva. I den här artikeln förklarar jag hur man skapar båda typerna., Exemplen jag tillhandahåller är baserade på en lokal instans av SQL Server 2008 och hämta data från AdventureWorks2008 provdatabas.

arbeta med vanliga Tabelluttryck

du definierar CTEs genom att lägga till en med-klausul direkt innan du väljer, infogar, uppdaterar, tar bort eller sammanfogar.,”>

1 2 3 4 5

] <common_table_expression>::= cte_name )] AS (cte_query)

…which can be represented like this…

As you can see, if you include more than one CTE in your WITH clause, you must separate them with commas., Dessutom, för varje CTE, måste du ange ett namn, som nyckelord, och en Välj uttalande. Du kan också ange kolumnnamn (separerade med kommatecken), så länge antalet namn matchar antalet kolumner som returneras av resultatuppsättningen.

select-satsen i din CTE-fråga måste följa samma krav som de som används för att skapa en vy. Mer information om dessa krav finns i avsnittet ” Skapa vy (Transact-SQL)” i SQL Server Books Online. Mer information om CTEs i allmänhet finns i avsnittet ”med common_table_expression (Transact-SQL).,”

När du har definierat din med-klausul med de nödvändiga ct-värdena kan du sedan referera till dessa ct-värden som du skulle ha någon annan tabell. Du kan dock referera till en CTE endast inom räckvidden för uttalandet som omedelbart följer med-klausulen. När du har kört ditt uttalande är CTE-resultatuppsättningen inte tillgänglig för andra uttalanden.

skapa ett icke-rekursivt vanligt Tabelluttryck

en icke rekursiv CTE är en som inte refererar till sig själv inom CTE. Nonrecursive CTES tenderar att vara enklare än rekursiva CTEs, varför jag börjar med den här typen.,cteTotalSales:

När jag har angett CTE-namnet anger jag två kolumnnamn, SalesPersonID och NetSales, som är inneslutna inom parentes och separerade med ett kommatecken. Det betyder att resultatuppsättningen som returneras av CTE-frågan måste returnera två kolumner.

därefter tillhandahåller jag as-sökordet, sedan en uppsättning parenteser som bifogar CTE-frågan. I det här fallet returnerar select-satsen den totala försäljningen för varje säljare (total försäljning grupperad efter säljare-ID)., Som du kan se kan CTE-frågan innehålla Transact-SQL-funktioner, Gruppera efter klausuler eller element som SELECT-satsen i en vydefinition kan innehålla.

Jag kan nu referera till cteTotalSales i uttalandet som omedelbart följer. I det här exemplet skapar jag ett SELECT-uttalande som går med i försäljningen.vSalesPerson visa att cteTotalSales, baserat på säljare ID. Jag drar sedan namn och platser från vyn och nettoomsättningen från CTE. Följande tabell visar de resultat som returneras av detta uttalande.,

som du såg tidigare i syntaxen kan du inkludera flera CTEs i en med-klausul.,

den första CTE-ctetotalsales-liknar den i föregående exempel, förutom att where klausulen har kvalificerats ytterligare för att inkludera försäljning endast från 2003. När jag definierar cteTotalSales, jag lägger till ett kommatecken, och sedan definiera cteTargetDiff, som beräknar skillnaden mellan försäljning totalt och säljkvot.

den nya CTE-definitionen anger tre kolumner för resultatuppsättningen: SalesPersonID, SalesQuota och QuotaDiff., Som du förväntar dig returnerar CTE-frågan tre kolumner. Den första är säljare ID. Den andra är försäljningskvoten. Men eftersom en säljkvot inte är definierad för vissa säljare använder jag en KUNDCASEDEKLARATION. Om värdet är null är det värdet inställt på 0, annars används det faktiska försäljningsvärdet.

den sista kolumnen som returneras är skillnaden mellan nettoomsättning och säljkvot. Återigen använder jag ett fall uttalande. Om Säljkvotvärdet är null, används nettoförsäljningsvärdet, annars subtraheras försäljningskvoten från nettoförsäljningen för att komma fram till skillnaden.,

något intressant att notera om den andra CTE-frågan är att jag har gått med i försäljningen.Säljare tabell till den första CTE-cteTotalSales-så jag kunde beräkna skillnaden mellan total försäljning och säljkvot. När du definierar flera CTEs i en enda med klausul kan du referera till föregående CTEs (men inte tvärtom).

När jag har definierat mina CTEs kan jag referera till dem i det första uttalandet som följer CTE, som du såg i föregående exempel. I det här fallet går jag med i försäljningen.,vSalesPerson visa att cteTotalSales och sedan gå till cteTargetDiff, alla baserade på säljare ID. Min Välj lista innehåller sedan kolumner från alla tre källor. Satsen returnerar resultaten som visas i följande tabell.

som du kan se tillhandahålls försäljningsdata för alla säljare, inklusive den stad där de bor, deras nettoomsättning, deras försäljningskvot och den beräknade skillnaden mellan de två siffrorna. I det här fallet överstiger alla väl kvoten, där en kvot har definierats.,

skapa ett rekursivt vanligt Tabelluttryck

en rekursiv CTE är en som refererar sig inom den CTE. Den rekursiva CTE är användbar när man arbetar med hierarkiska data eftersom CTE fortsätter att exekvera tills frågan returnerar hela hierarkin.

ett typiskt exempel på hierarkisk data är en tabell som innehåller en lista över anställda. För varje anställd ger tabellen en hänvisning till den personens chef. Denna hänvisning är i sig ett anställnings-ID inom samma tabell., Du kan använda en rekursiv CTE för att visa hierarkin för personaldata, som det skulle visas i organisationsschemat.

Observera att en CTE som skapats felaktigt kan komma in i en oändlig slinga. För att förhindra detta kan du inkludera maxrecursion-tipset i alternativklausulen för primärval, infoga, uppdatera, ta bort eller sammanfogning. Information om hur du använder frågetips finns i avsnittet ”Frågetips (Transact-SQL)” i SQL Server Books Online.,

för att visa hur den rekursiva CTE fungerar använde jag följande Transact-SQL-satser för att skapa och fylla tabellen Anställda i AdventureWorks2008-databasen:

som du kanske inser innehåller AdventureWorks2008-databasen redan HumanResources.Anställd bord. Den tabellen använder emellertid nu hierarchyid-datatypen för att lagra hierarkiska data, vilket skulle införa onödig komplexitet när man försöker visa en rekursiv CTE. Av den anledningen skapade jag mitt eget bord., Men om du vill prova en rekursiv CTE utan att skapa och fylla i en ny tabell, kan du använda AdventureWorks provdatabas som levereras med SQL Server 2005. Mänskliga Resurser.Anställd tabell i databasen lagrar data på ett sätt som liknar tabellen jag skapar ovan.,div>2

som du kan se returnerar CTE fem kolumner: EmpID, Förnamn, Efternamn, MgrID och EmpLevel. Kolumnen EmpLevel avser nivån i hierarkin där de anställda passar. Den högsta nivån i hierarkin är 1, nästa nivå är 2, följt av 3 och så vidare.

CTE-frågan består i sig av två utvalda uttalanden, kopplade till UNION ALL-operatören., En rekursiv CTE-fråga måste innehålla minst två medlemmar (uttalanden), anslutna av Unionen alla, UNION, INTERSECT eller utom operatör. I det här exemplet är det första SELECT-uttrycket ankarmedlemmen, och det andra uttalandet är den rekursiva medlemmen. Alla ankarmedlemmar måste föregå de rekursiva medlemmarna, och endast de rekursiva medlemmarna kan referera till CTE själv. Dessutom måste alla medlemmar returnera samma antal kolumner med motsvarande datatyper.

Nu kan vi titta närmare på uttalandena själva., Det första uttalandet, ankarmedlemmen, hämtar medarbetar-ID, Förnamn, Efternamn och chef-ID från tabellen Anställda, där chef-ID är null. Detta skulle vara arbetstagaren högst upp i hierarkin, vilket innebär att den här personen rapporterar till ingen. Följaktligen är förvaltarens ID-värde null. För att återspegla att den här personen är högst upp i hierarkin, tilldelar jag ett värde på 1 till emplevel-kolumnen.

det andra uttalandet i CTE-frågan-den rekursiva medlemmen-hämtar också medarbetar-ID, Förnamn, Efternamn och chef-ID för anställda i tabellen Anställda., Observera dock att jag går med i tabellen Anställda till CTE själv. Dessutom är kopplingen baserad på chef ID i tabellen Anställda och anställd ID i CTE. Genom att göra detta, CTE kommer slinga genom tabellen Anställda tills den returnerar hela hierarkin.

ett annat objekt att märka om det andra uttalandet är att för emplevel-kolumnen lägger jag värdet 1 till Emplevelvärdet som det visas i CTE. På så sätt, varje gång uttalandet loopar genom hierarkin, tillämpas nästa korrekta nivå på de anställda på nivån.,

När jag har definierat min med-klausul skapar jag en SELECT-sats som hämtar data från CTE. Observera dock att för Chefskolumnen hämtar jag för-och efternamn på den anställde som är associerad med chefs-ID i CTE. Detta gör det möjligt för mig att visa chefens fullständiga namn för varje anställd. Följande tabell visar resultatuppsättningen som returneras av select-satsen och dess CTE.,

som du kan se kan CTE, vare sig det är rekursivt eller icke-rekursivt, vara ett användbart verktyg när du behöver generera tillfälliga resultatuppsättningar som kan nås i en SELECT, INSERT, UPDATE, DELETE eller MERGE-sats. På ett sätt är en CTE som en härledd tabell: den lagras inte som ett objekt och gäller endast under utförandet av det primära uttalandet. Men till skillnad från den härledda tabellen kan en CTE refereras flera gånger inom en fråga och det kan vara självreferenser. Och bäst av allt är CTEs relativt lätta att genomföra.,

Du har märkt att Bob använder AdventureWorks2008 snarare än AdventureWorks. Om du föredrar att köra dessa exempel mot AdventureWorks databasen snarare än AdventureWorks2008 databasen, bör du ändra BusinessEntityID kolumnen till SalesPersonID kolumn.