Redgate Hub (Norsk)

Innført i SQL Server 2005, felles bord uttrykk (CTE) er en midlertidig heter resultat som du kan bruke i en SELECT, INSERT, UPDATE eller DELETE uttalelse. Du kan også bruke en CTE i en CREATE VIEW uttalelse, som en del av den vise er å VELGE en spørring. I tillegg, som av SQL Server 2008, kan du legge til en CTE til den nye MERGE-setningen.

SQL Server støtter to typer CTEs-rekursive og ikke-rekursive. I denne artikkelen, vil jeg forklare hvordan du oppretter begge typer., Eksemplene jeg gir er basert på en lokal forekomst av SQL Server 2008 og hente data fra AdventureWorks2008 eksempel databasen.

Arbeide med Felles Bord Uttrykk

Du angi CTEs ved å legge til et punkt rett før SELECT, INSERT, UPDATE, DELETE, eller FLETTE uttalelse.,»>

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., I tillegg, for hver ETC, må du oppgi et navn, SOM søkeord, og en SELECT-setning. Du kan også gi kolonne-navn (atskilt med komma), så lenge det antall navn som samsvarer med antall kolonner som returneres av resultat.

VELG uttalelse i CTE spørring må følge de samme krav som de som brukes for å opprette en visning. For mer informasjon om disse kravene, kan du se emnet OPPRETTE VISE (Ta-SQL)» i SQL Server Books Online. For mer informasjon om CTEs generelt, kan du se emnet «MED common_table_expression (Ta-SQL).,»

Etter at du definere din MED klausul med nødvendig CTEs, kan du deretter refererer til de CTEs som du ville noe annet bord. Imidlertid, kan du referere til en CTE bare i gjennomføring omfanget av utsagn som umiddelbart følger MED klausul. Etter at du har kjørt uttalelse, CTE resultat er ikke tilgjengelig for andre uttalelser.

Opprette en ikke-rekursiv Felles Bord Uttrykk

En ikke-rekursiv CTE er en som ikke har referanse seg innenfor ETC. Ikke-rekursive CTEs har en tendens til å være enklere enn rekursiv CTEs, som er grunnen til at jeg starter med denne typen.,cteTotalSales:

Etter at jeg angi ETC navn, skal jeg gi to kolonnen navn, SalesPersonID og NetSales, som er omsluttet av parenteser, atskilt med komma. Det betyr at resultatet satt tilbake av CTE spørringen skal returnere to kolonner.

Neste, jeg gir SOM søkeord, og deretter et sett med parenteser som omslutter ETC spørring. I dette tilfellet VELGER uttalelse returnerer sum salg for hver selger (totalt salg gruppert etter selger-ID)., Som du kan se, CTE spørring kan inkludere Handle-SQL-funksjoner, GRUPPE AV klausuler, eller alle elementene som den SELECT-setning i en visning definisjon kan inneholde.

jeg kan nå referanse cteTotalSales i uttalelsen som følger umiddelbart. For dette eksempelet, jeg lage en SELECT-setning som slutter seg til Salg.vSalesPerson vise til cteTotalSales, basert på selger-ID-en. Jeg drar navn og steder fra visning og netto salg fra ETC. Følgende tabell viser resultatene som returneres av denne uttalelsen.,

Som du så tidligere i syntaksen, kan du inkludere flere CTEs i en paragraf.,

Den første CTE-cteTotalSales-er på samme måte som i forrige eksempel, bortsett fra at DER punkt har blitt ytterligere kvalifisert til å omfatte salg bare fra 2003. Etter at jeg definere cteTotalSales, jeg legger til et komma og deretter definere cteTargetDiff, som beregner forskjellen mellom salg sum og salg kvote.

Den nye ETC definisjon angir tre kolonner for resultat: SalesPersonID, SalesQuota, og QuotaDiff., Som du forventer, ETC spørringen returnerer tre kolonner. Den første er selger-ID-en. Den andre er salg kvote. Imidlertid, fordi et salg kvote er ikke definert i noen selgere bruker jeg en CASE-uttrykket. Hvis verdien er null, vil denne verdien er satt til 0, ellers vil den faktiske SalesQuota verdi er brukt.

Den siste kolonnen som returneres, er forskjellen mellom netto salg (kjøp og salg av kvote. Igjen, jeg bruker en CASE-uttrykket. Hvis SalesQuota verdien er null, så NetSales verdi er brukt, ellers salg kvote er trukket fra netto salg for å komme frem til forskjellen.,

Noe interessant å merke seg om andre CTE spørring er at jeg har sluttet seg til Salg.Selger tabellen til første CTE-cteTotalSales-slik at jeg kunne beregne differansen mellom totale salg og salg kvote. Når du definere flere CTEs i en enkelt paragraf, kan du referere foregående CTEs (men ikke den andre veien rundt).

Når jeg har definert mitt CTEs, jeg kan henvise til dem i det første utsagnet som følger ETC, som du så i forrige eksempel. I dette tilfellet, jeg blir med Salg.,vSalesPerson vise til cteTotalSales, og deretter koble til cteTargetDiff, alle basert på selger-ID-en. Min VELG listen omfatter kolonner fra alle tre kilder. Den setningen returnerer resultatene er vist i følgende tabell.

Som du kan se, salg av data er tilgjengelig for alle selgere, inkludert byen der de bor, deres netto salg, salg kvote, og den beregnede forskjellen mellom de to tallene. I dette tilfellet, alle godt over kvoten, der kvoten er blitt definert.,

Opprette en Rekursiv Felles Bord Uttrykk

En rekursiv CTE er en som refererer til seg selv i det ETC. Den rekursive CTE er nyttig når du arbeider med hierarkiske data fordi ETC fortsetter å kjøre til spørringen returnerer hele hierarkiet.

Et typisk eksempel på hierarkisk data er en tabell som inneholder en liste over ansatte. For hver ansatt, tabellen gir en referanse til personen som manager. Som referanse er i seg selv en ansatt-ID innen den samme tabellen., Du kan bruke en rekursiv ETC for å vise hierarki av ansattes data, så vil det vises i organisasjonskartet.

vær Oppmerksom på at en CTE opprettet på feil måte, kan gå inn i en uendelig løkke. For å unngå dette, kan du inkludere MAXRECURSION hint i ALTERNATIV punkt på den primære SELECT, INSERT, UPDATE, DELETE, eller FLETTE uttalelse. For informasjon om hvordan du bruker spørring hint, se emnet «Spørring Hint (Ta-SQL)» i SQL Server Books Online.,

for Å vise hvordan rekursiv ETC fungerer, brukte jeg følgende Handle-SQL-setninger for å opprette og fylle ut tabellen Ansatte i AdventureWorks2008 database:

Som du kanskje skjønner, det AdventureWorks2008 database inneholder allerede det HumanResources.Ansatt-tabellen. Men som tabell nå bruker hierarchyid data type for å lagre hierarkiske data, som ville innføre unødvendig kompleksitet når du prøver å vise en rekursiv ETC. Derfor opprettet jeg min egen tabell., Imidlertid, hvis du ønsker å prøve ut en rekursiv ETC uten å opprette og fylle en ny tabell, kan du bruke AdventureWorks eksempel databasen som fulgte med SQL Server 2005. Den HumanResources.Ansatte-tabellen i databasen lagrer data på en lignende måte til bordet jeg opprette ovenfor.,div>2

Som du kan se, CTE returnerer fem kolonner: EmpID, Fornavn, Etternavn, MgrID, og EmpLevel. Den EmpLevel kolonne refererer til nivået i hierarkiet, hvor de ansatte er passe. Det høyeste nivået i hierarkiet er 1, neste nivå er 2, etterfulgt av 3, og så videre.

CTE spørring er i seg selv består av to utvalgte uttalelser, koblet med UNION ALLE operatør., En rekursiv ETC søket må inneholde minst to medlemmer (uttalelser), som er forbundet med UNION ALLE, UNION, SNITT, og BORTSETT fra operatøren. I dette eksemplet er den første SELECT-setning er ankeret medlem, og den andre setningen er den rekursive medlem. Alle anker medlemmer må gå forut for den rekursive medlemmer, og bare den rekursive medlemmer kan referanse CTE seg selv. I tillegg, alle medlemmer må gå tilbake samme antall kolonner med tilsvarende data typer.

Nå kan se nærmere på uttalelsene seg selv., Den første setningen, anker-medlem, henter ansatt ID, fornavn, etternavn, og manager ID fra Ansatte-tabellen, der manager-ID er ugyldig. Dette ville være ansatt på toppen av hierarkiet, noe som betyr at denne personen rapporterer til ingen. Følgelig manager-ID-verdien er null. Å reflektere over at denne personen er på toppen av hierarkiet, jeg vil tilordne en verdi av 1 til EmpLevel kolonne.

Den andre setningen i CTE query-rekursive medlem-også henter ansatt ID, fornavn, etternavn, og manager ID for ansatte i Ansatte-tabellen., Men, legg merke til at jeg blir med i Ansatte-tabellen for å CTE seg selv. I tillegg delta i er basert på ID manager i Ansatte-tabellen og ansatt-ID-en i CTE. Ved å gjøre dette, CTE vil gjennomkjøring av de Ansatte-tabellen før den returnerer hele hierarkiet.

Et annet element å legge merke til om det andre uttrykket er at for EmpLevel kolonnen, jeg vil legge til verdien 1 til EmpLevel verdi som det vises i den ETC. På den måten, hver gang uttalelse looper gjennom hierarkiet, det neste riktig nivå er brukt til ansatte på nivå.,

Etter at jeg definerer min MED klausul, jeg lage en SELECT-setning som henter data fra ETC. Vær imidlertid oppmerksom på at for Manager kolonnen, jeg hente den første og siste navn på ansatt som er forbundet med leder-ID i CTE. Dette gir meg muligheten til å vise hele navnet på manager for hver ansatt. Den følgende tabellen viser resultat som er returnert av SELECT-setningen og dens ETC.,

Som du kan se, ETC, om rekursiv eller ikke-rekursive, kan være et nyttig verktøy når du trenger å generere midlertidige resultatsett som kan brukes i en SELECT, INSERT, UPDATE, DELETE, eller FLETTE uttalelse. I en forstand, en CTE er som en avledet tabellen: det er ikke lagret som et objekt, og er bare gyldig under utførelsen av den primære uttalelse. Imidlertid, i motsetning til det avledede, bord, ETC kan bli referert til flere ganger i løpet av en spørring, og det kan være selv-referanse. Og best av alt, CTEs er relativt lett å implementere.,

har Du lagt merke til at Bob bruker AdventureWorks2008 snarere enn AdventureWorks. Hvis du foretrekker å kjøre disse eksemplene mot AdventureWorks database snarere enn AdventureWorks2008 database, bør du endre BusinessEntityID kolonnen til SalesPersonID kolonne.