Redgate Hub (Dansk)

der blev Indført i SQL Server 2005, fælles tabel udtryk (CTE) er en midlertidig opkaldt der er resultatet, at du kan henvise til i en SELECT, INSERT, UPDATE, eller DELETE-sætning. Du kan også bruge en CTE i en Create vie. – erklæring som en del af visningens SELECT-forespørgsel. Derudover kan du fra S .l Server 2008 tilføje en CTE til den nye MERGE-erklæring.

s .l Server understøtter to typer CTEs-rekursiv og ikke-rekursiv. I denne artikel forklarer jeg, hvordan man opretter begge typer., Eksemplerne jeg giver er baseret på en lokal forekomst af s .l Server 2008 og hente data fra Adventure .orks2008 prøve database.

arbejde med almindelige Tabeludtryk

du definerer CTEs ved at tilføje en med-klausul direkte inden din select, INSERT, UPDATE, DELETE eller MERGE-sætning.,”>

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., Derudover skal du for hver CTE angive et navn, AS-nøgleordet og en SELECT-erklæring. Du kan også angive kolonnenavne (adskilt af kommaer), så længe antallet af navne svarer til antallet af kolonner, der returneres af det indstillede resultat.

SELECT-sætningen i din CTE-forespørgsel skal følge de samme krav som dem, der bruges til at oprette en visning. For detaljer om disse krav, se emnet ” Opret visning (Transact-s .l)” I S .l Server Books Online. For flere detaljer om ctes generelt, se emnet ” med common_table_e .pression (Transact-s .l).,”

Når du har defineret din med klausul med de nødvendige CTE ‘er, kan du derefter henvise til disse CTE’ er, som du ville have enhver anden tabel. Du kan dog kun henvise til en CTE inden for eksekveringsområdet for den erklæring, der umiddelbart følger med-klausulen. Når du har kørt din erklæring, er CTE-resultatsættet ikke tilgængeligt for andre udsagn.

oprettelse af et ikke-rekursivt fælles Tabeludtryk

en ikke-rekursiv CTE er en, der ikke henviser til sig selv inden for CTE. Nonrecursive CTE ‘er har en tendens til at være enklere end rekursive CTE’ er, hvorfor jeg starter med denne type.,cteTotalSales:

Når jeg har angivet CTE-navnet, giver jeg to kolonnenavne, SalesPersonID og Netsalgs, som er lukket i parentes og adskilt af et komma. Det betyder, at det resultat, der returneres af CTE-forespørgslen, skal returnere to kolonner.

dernæst giver jeg AS-nøgleordet, derefter et sæt parenteser, der omslutter CTE-forespørgslen. I dette tilfælde returnerer SELECT-erklæringen det samlede salg for hver sælger (samlet salg grupperet efter sælger-ID)., Som du kan se, kan CTE-forespørgslen indeholde Transact-s .l-funktioner, gruppe efter klausuler eller elementer, som SELECT-sætningen i en visningsdefinition kan indeholde.

Jeg kan nu henvise til cteTotalSales i den erklæring, der umiddelbart følger. I dette eksempel opretter jeg en SELECT-erklæring, der slutter sig til salget.vSalesPerson udsigt til cteTotalSales, baseret på sælger-ID. Jeg trækker derefter navne og placeringer fra visningen og nettosalget fra CTE. Følgende tabel viser de resultater, der returneres af denne erklæring.,

som du så tidligere i syntaksen, kan du inkludere flere CTE ‘ er i A med klausul.,

Den første CTE-cteTotalSales-er den samme som i det foregående eksempel, bortset fra at HVOR klausulen er blevet yderligere kvalificeret til at omfatte salg kun fra 2003. Efter at jeg har defineret cteTotalSales, tilføjer jeg et komma og definerer derefter cteTargetDiff, som beregner forskellen mellem salgstall og salgskvote.

den nye CTE-definition angiver tre kolonner for resultatsættet: SalesPersonID, Sales .uota og .uotadiff., Som du ville forvente, returnerer CTE-forespørgslen tre kolonner. Den første er sælgerens ID. Den anden er salgskvoten. Men fordi en salgskvote ikke er defineret for nogle sælgere, bruger jeg en SAGSERKLÆRING. Hvis værdien er null, sættes denne værdi til 0, ellers bruges den faktiske Salgskvotaværdi.

den sidste kolonne, der returneres, er forskellen mellem nettosalg og salgskvote. Igen, Jeg bruger en sag erklæring. Hvis Salgskvotaværdien er null, bruges Netsalgsværdien, ellers trækkes salgskvoten fra nettosalget for at nå frem til forskellen.,

noget interessant at bemærke om den anden CTE forespørgsel er, at jeg har tilsluttet salget.Sælger bord til de første CTE-cteTotalSales-så jeg kunne beregne forskellen mellem det samlede salg og salgskvoten. Når du definerer flere CTEs i en enkelt med klausul, kan du henvise til foregående CTEs (men ikke omvendt).

Når jeg har defineret mine CTEs, kan jeg henvise til dem i den første erklæring, der følger CTE, som du så i det foregående eksempel. I dette tilfælde deltager jeg i salget.,vSalesPerson vie.til cteTotalSales og derefter slutte sig til cteTargetDiff, alle baseret på sælger ID. Min SELECT list indeholder derefter kolonner fra alle tre kilder. Erklæringen returnerer resultaterne vist i den følgende tabel.

som du kan se, leveres salgsdata for alle sælgere, herunder den by, hvor de bor, deres nettosalg, deres salgskvote og den beregnede forskel mellem de to tal. I dette tilfælde overstiger alle godt kvoten, hvor en kvote er defineret.,

oprettelse af et rekursivt fælles Tabeludtryk

en rekursiv CTE er en, der refererer til sig selv inden for den CTE. Den rekursive CTE er nyttig, når du arbejder med hierarkiske data, fordi CTE fortsætter med at udføre, indtil forespørgslen returnerer hele hierarkiet.

et typisk eksempel på hierarkiske data er en tabel, der indeholder en liste over medarbejdere. For hver medarbejder giver tabellen en henvisning til den pågældende persons leder. Denne henvisning er i sig selv en medarbejder-ID inden for samme tabel., Du kan bruge en rekursiv CTE til at vise hierarkiet af medarbejderdata, som det ser ud i organisationskortet.

Bemærk, at en CTE, der er oprettet forkert, kan indtaste en uendelig løkke. For at forhindre dette, kan du inkludere MAXRECURSION hint i MULIGHED for bestemmelse af den primære SELECT, INSERT, UPDATE, DELETE, eller FUSIONERE erklæring. For information om brug af forespørgselstip, se emnet” Forespørgselstip (Transact-s .l) ” I S .l Server Books Online.,

for At demonstrere, hvordan de rekursive CTE virker, har jeg brugt følgende Transact-SQL-sætninger til at oprette og udfylde tabellen Medarbejdere i AdventureWorks2008 database:

Som du måske indse, AdventureWorks2008 database, der allerede indeholder HumanResources.Medarbejder bord. Imidlertid bruger denne tabel nu hierarchy-datatypen til at gemme hierarkiske data, hvilket ville introducere unødvendig kompleksitet, når man prøver at demonstrere en rekursiv CTE. Af den grund oprettede jeg mit eget bord., Men, hvis du ønsker at prøve en rekursiv CTE uden at oprette og befolke en ny tabel, kan du bruge AdventureWorks prøve database, der blev leveret sammen med SQL Server 2005. De Menneskelige Ressourcer.Medarbejder tabel i denne database gemmer data på en måde svarende til tabellen jeg opretter ovenfor.,div>2

Som du kan se, CTE returnerer fem kolonner: EmpID, Fornavn, Efternavn, MgrID, og EmpLevel. EmpLevel-kolonnen henviser til niveauet i hierarkiet, hvor medarbejderne passer. Det højeste niveau af hierarkiet er 1, det næste niveau er 2, efterfulgt af 3 og så videre.

CTE-forespørgslen består i sig selv af to udvalgte udsagn, der er forbundet med UNION all-operatøren., En rekursiv CTE forespørgsel skal indeholde mindst to medlemmer (udsagn), forbundet af fagforeningen alle, UNION, INTERSECT, eller undtagen operatør. I dette eksempel er den første SELECT-sætning ankerelementet, og den anden sætning er det rekursive medlem. Alle ankermedlemmer skal gå forud for de rekursive medlemmer, og kun de rekursive medlemmer kan henvise til selve CTE. Derudover skal alle medlemmer returnere det samme antal kolonner med tilsvarende datatyper.

lad os nu se nærmere på udsagnene selv., Den første erklæring, ankermedlemmet, henter medarbejder-ID, fornavn, efternavn og manager-ID fra Medarbejdertabellen, hvor manager-ID ‘ et er null. Dette ville være medarbejderen øverst i hierarkiet, hvilket betyder, at denne person rapporterer til ingen. Derfor er manager ID-værdien null. For at afspejle, at denne person er øverst i hierarkiet, tildeler jeg en værdi på 1 til kolonnen EmpLevel.

den anden erklæring i CTE-forespørgslen-det rekursive medlem-henter også medarbejder-ID, fornavn, efternavn og manager-ID for medarbejdere i Medarbejdertabellen., Bemærk dog, at jeg slutter mig til Medarbejderbordet til selve CTE. Derudover er tilmeldingen baseret på leder-ID ‘et i Medarbejdertabellen og Medarbejder-ID’ et i CTE. Ved at gøre dette vil CTE gennemløbe Medarbejdertabellen, indtil den returnerer hele hierarkiet.et andet element, der skal bemærkes om den anden erklæring, er, at jeg for kolonnen EmpLevel tilføjer værdien 1 til EmpLevel-værdien, som den vises i CTE. På den måde, hver gang erklæringen løber gennem hierarkiet, anvendes det næste korrekte niveau til medarbejderne på niveauet.,

Når jeg har defineret min med klausul, opretter jeg en SELECT-sætning, der henter dataene fra CTE. Bemærk dog, at for Manager-kolonnen henter jeg for-og efternavnet på den medarbejder, der er knyttet til manager-ID ‘ et i CTE. Dette giver mig mulighed for at vise det fulde navn på lederen for hver medarbejder. Følgende tabel viser det resultatsæt, der returneres af SELECT-sætningen og dens CTE.,

Som du kan se, CTE, om rekursive eller ikke-rekursiv, kan være et nyttigt værktøj, når du skal generere et midlertidigt resultat sæt, der kan tilgås i en SELECT, INSERT, UPDATE, DELETE, eller FUSIONERE erklæring. På en måde er en CTE som en afledt tabel: den gemmes ikke som et objekt og er kun gyldigt under udførelsen af den primære erklæring. Imidlertid, i modsætning til den afledte tabel, en CTE kan refereres flere gange inden for en forespørgsel, og det kan være selvreferencerende. Og bedst af alt er CTE ‘ er relativt lette at implementere.,

du har bemærket, at Bob bruger Adventure .orks2008 snarere end Adventure .orks. Hvis du foretrækker at køre disse eksempler mod AdventureWorks database snarere end AdventureWorks2008 database, bør du ændre BusinessEntityID kolonne til SalesPersonID kolonne.