Redgate Hub

geà ntroduceerd in SQL Server 2005, is de common table expression (CTE) een tijdelijke named result set waarnaar u kunt verwijzen in een select, INSERT, UPDATE of DELETE statement. U kunt ook een CTE gebruiken in een CREATE VIEW statement, als onderdeel van de view select query. Bovendien kunt u vanaf SQL Server 2008 een CTE toevoegen aan het nieuwe MERGE statement.

SQL Server ondersteunt twee soorten CTE ‘ s-recursief en niet-recursief. In dit artikel leg ik uit hoe je beide typen kunt maken., De voorbeelden die ik geef zijn gebaseerd op een lokale instantie van SQL Server 2008 en halen gegevens op uit de AdventureWorks2008 voorbeelddatabase.

werken met gemeenschappelijke Tabeluitdrukkingen

u definieert CTE ‘ s door een met-clausule toe te voegen direct voor uw statement selecteren, invoegen, bijwerken, verwijderen of samenvoegen.,”>

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., Bovendien moet u voor elke CTE een naam, het trefwoord ALS en een SELECT statement opgeven. U kunt ook kolomnamen opgeven (gescheiden door komma ‘ s), zolang het aantal namen overeenkomt met het aantal kolommen dat door de resultaatset wordt geretourneerd.

het SELECT-statement in uw CTE-query moet aan dezelfde vereisten voldoen als die welke worden gebruikt voor het maken van een weergave. Zie het onderwerp “weergave maken (Transact-SQL)” in SQL Server Books Online voor meer informatie over deze vereisten. Voor meer details over CTEs in het algemeen, zie het onderwerp ” met common_table_expression (Transact-SQL).,”

nadat u uw WITH-clausule met de benodigde CTE ’s hebt gedefinieerd, kunt u deze CTE’ s dan refereren zoals in elke andere tabel. U kunt echter alleen verwijzen naar een CTE binnen het uitvoeringsbereik van de verklaring die onmiddellijk volgt op de WITH-clausule. Nadat u uw statement hebt uitgevoerd, is de CTE-resultaatset niet beschikbaar voor andere statements.

het creëren van een niet-cursieve gemeenschappelijke Tabeluitdrukking

een niet-cursieve CTE is een CTE die zichzelf niet binnen de CTE verwijst. Niet-recursieve CTE ’s zijn vaak eenvoudiger dan recursieve CTE’ s, daarom begin ik met dit type.,cteTotalSales:

nadat ik de CTE-naam specificeer, geef ik twee kolomnamen op, SalesPersonID en NetSales, die tussen haakjes staan en worden gescheiden door een komma. Dat betekent dat het resultaat dat door de CTE-query wordt geretourneerd, twee kolommen moet retourneren.

vervolgens geef ik het sleutelwoord AS, dan een set haakjes die de CTE-query omsluiten. In dit geval, de SELECT statement retourneert de totale verkoop voor elke verkoper persoon (totale verkoop gegroepeerd op verkoper ID)., Zoals u kunt zien, kan de CTE-query Transact-SQL-functies bevatten, groeperen op clausules, of elementen die het SELECT-statement in een weergave-definitie kan bevatten.

Ik kan nu verwijzen naar cteTotalSales in de verklaring die onmiddellijk volgt. Voor dit voorbeeld, Ik maak een SELECT statement dat zich aansluit bij de verkoop.vsalespersoon view to cteTotalSales, gebaseerd op de verkoper ID. Ik haal dan de namen en locaties uit het uitzicht en de netto-omzet van de CTE. De volgende tabel toont de resultaten van deze verklaring.,

zoals u eerder in de syntaxis zag, kunt u meerdere CTE ‘ s in een met-clausule opnemen.,

de eerste CTE-cteTotalSales-is vergelijkbaar met die in het vorige voorbeeld, behalve dat de WHERE-clausule verder is gekwalificeerd om de verkoop pas vanaf 2003 op te nemen. Nadat ik ctetotalsales definieer, voeg ik een komma toe, en definieer vervolgens cteTargetDiff, die het verschil berekent tussen de totale verkoop en het verkoopquotum.

De nieuwe CTE-definitie specificeert drie kolommen voor de resultatenreeks: SalesPersonID, SalesQuota en QuotaDiff., Zoals je zou verwachten, de CTE query retourneert drie kolommen. De eerste is de verkoper ID. De tweede is het verkoopquotum. Echter, omdat een verkoopquotum niet is gedefinieerd voor sommige verkopers gebruik ik een CASE statement. Als de waarde null is, wordt die waarde ingesteld op 0, anders wordt de werkelijke Verkoopquotawaarde gebruikt.

de laatste kolom die wordt geretourneerd is het verschil tussen de nettoverkoop en het verkoopquotum. Nogmaals, ik gebruik een CASE statement. Als de Verkoopquotawaarde null is, wordt de Nettoverkoopwaarde gebruikt, anders wordt het verkoopquotum van de nettoverkoop afgetrokken om tot het verschil te komen.,

iets interessants op te merken over de tweede CTE query is dat ik ben toegetreden tot de verkoop.Verkoper tabel naar de eerste CTE-cteTotalSales-zodat ik het verschil tussen de totale verkoop en de verkoopquota kon berekenen. Wanneer je meerdere CTE ’s definieert in een enkele met-clausule, kun je verwijzen naar voorafgaande CTE’ s (maar niet andersom).

zodra ik mijn CTE ‘ s heb gedefinieerd, kan ik ze verwijzen in het eerste statement dat de CTE volgt, zoals je in het vorige voorbeeld zag. In dit geval sluit ik me aan bij de verkoop.,vsalespersoon view to cteTotalSales and then join to cteTargetDiff, alle gebaseerd op de verkoper ID. Mijn SELECT lijst bevat dan kolommen van alle drie de bronnen. De statement geeft de resultaten in de volgende tabel.

zoals u kunt zien, worden verkoopgegevens verstrekt voor alle verkopers, inclusief de stad waar zij wonen, hun netto-omzet, hun verkoopquotum en het berekende verschil tussen de twee cijfers. In dit geval overschrijdt iedereen ruim het quotum, wanneer een quotum is vastgesteld.,

het creëren van een recursieve gemeenschappelijke Tabeluitdrukking

een recursieve CTE is een CTE die verwijst naar zichzelf binnen die CTE. De recursieve CTE is handig bij het werken met hiërarchische gegevens omdat de CTE blijft uitvoeren totdat de query de hele hiërarchie retourneert.

een typisch voorbeeld van hiërarchische gegevens is een tabel met een lijst van werknemers. Voor elke werknemer geeft de tabel een verwijzing naar de manager van die persoon. Die verwijzing is zelf een werknemer-ID binnen dezelfde tabel., U kunt een recursieve CTE gebruiken om de hiërarchie van werknemersgegevens weer te geven, zoals het zou verschijnen in het organigram.

merk op dat een onjuist aangemaakte CTE een oneindige lus kan binnengaan. Om dit te voorkomen, kunt u de maxrecursion hint opnemen in de optie clausule van de primaire select, INSERT, UPDATE, DELETE of MERGE statement. Zie het onderwerp “Query Hints (Transact-SQL)” in SQL Server Books Online voor meer informatie over het gebruik van query hints.,

om aan te tonen hoe de recursieve CTE werkt, heb ik de volgende Transact-SQL statements gebruikt om de werknemers tabel in de AdventureWorks2008 database aan te maken en te vullen:

zoals u zich wellicht realiseert, bevat de AdventureWorks2008 database al de HumanResources.Werknemers tafel. Echter, die tabel maakt nu gebruik van de hiërarchyid data type om hiërarchische gegevens op te slaan, die onnodige complexiteit zou introduceren wanneer het proberen om een recursieve CTE aan te tonen. Daarom heb ik mijn eigen tafel gemaakt., Als u echter een recursieve CTE wilt uitproberen zonder een nieuwe tabel te maken en te vullen, kunt u de AdventureWorks-voorbeelddatabase gebruiken die bij SQL Server 2005 is geleverd. De Menselijke Hulpbronnen.Employee table in die database slaat de gegevens op een manier die vergelijkbaar is met de tabel die ik hierboven maak.,div>2

zoals u kunt zien, geeft de CTE vijf kolommen terug: EmpID, voornaam, achternaam, MgrID en EmpLevel. De EmpLevel kolom verwijst naar het niveau in de hiërarchie waarin de werknemers passen. Het hoogste niveau van de hiërarchie is 1, het volgende niveau is 2, gevolgd door 3, enzovoort.

de CTE-query bestaat zelf uit twee SELECT-statements, verbonden met de operator UNION ALL., Een recursieve CTE-query moet ten minste twee leden (statements) bevatten, verbonden door de operator Union ALL, UNION, INTERSECT of EXCEPT. In dit voorbeeld is het eerste SELECT-statement het ankerlid en het tweede statement het recursieve lid. Alle anchor leden moeten voorafgaan aan de recursieve leden, en alleen de recursieve leden kunnen verwijzen naar de CTE zelf. Bovendien moeten alle leden hetzelfde aantal kolommen met bijbehorende gegevenstypen retourneren.

laten we nu beter kijken naar de statements zelf., Het eerste statement, het anchor member, haalt de werknemer-ID, voornaam, achternaam, en manager-ID van de werknemers tabel, waar de manager-ID is null. Dit zou de werknemer aan de top van de hiërarchie zijn, wat betekent dat deze persoon aan niemand rapporteert. Bijgevolg is de manager ID waarde null. Om aan te geven dat deze persoon aan de top van de hiërarchie staat, wijs ik een waarde van 1 toe aan de EmpLevel kolom.

Het tweede statement in de CTE-query-het recursieve lid-haalt ook de werknemer-id, voornaam, achternaam en manager-ID voor werknemers op in de tabel Werknemers., Merk echter op dat ik aan de werknemers tafel aan de CTE zelf. Daarnaast is de join gebaseerd op de manager ID in de werknemers tabel en de werknemer ID in de CTE. Door dit te doen, zal de CTE door de werknemers tabel lopen totdat het de hele hiërarchie retourneert.

een ander item om op te merken over het tweede statement is dat, Voor de EmpLevel kolom, ik de waarde 1 toevoeg aan de EmpLevel waarde zoals deze verschijnt in de CTE. Op die manier wordt elke keer dat het statement door de hiërarchie loopt, het volgende correcte niveau toegepast op de werknemers op het niveau.,

nadat ik mijn WITH-clausule heb gedefinieerd, maak ik een SELECT-statement dat de gegevens uit de CTE haalt. Merk echter op dat Voor de Manager kolom, ik haal de voor-en achternaam van de werknemer in verband met de manager ID in de CTE. Hierdoor kan ik voor elke medewerker de volledige naam van de manager weergeven. De volgende tabel toont het resultaat dat wordt geretourneerd door het SELECT statement en zijn CTE.,

zoals u kunt zien, kan de CTE, recursief of niet-recursief, een nuttig hulpmiddel zijn wanneer u tijdelijke resultaatsets wilt genereren die toegankelijk zijn in een statement selecteren, invoegen, bijwerken, verwijderen of samenvoegen. In zekere zin, een CTE is als een afgeleide tabel: het is niet opgeslagen als een object en is alleen geldig tijdens de uitvoering van de primaire statement. Echter, in tegenstelling tot de afgeleide tabel, een CTE kan meerdere keren worden verwezen binnen een query en het kan zelf-referencing. En het beste van alles, CTE ‘ s zijn relatief eenvoudig te implementeren.,

u zult gemerkt hebben dat Bob AdventureWorks2008 gebruikt in plaats van AdventureWorks. Als u liever deze voorbeelden tegen de AdventureWorks database in plaats van de AdventureWorks2008 database draaien, moet u de BusinessEntityID kolom naar de SalesPersonID kolom.