Nabhan Hub

Käyttöön SQL Server 2005, yhteisen pöydän ilme (CTE) on väliaikainen nimeltä tulos asetettu, että voit viittaus sisällä SELECT, INSERT, UPDATE tai DELETE. Voit myös käyttää CTE: tä CREATE VIEW statementissa osana näkymän valitse-kyselyä. Lisäksi SQL Server 2008: sta lähtien voit lisätä CTE: n uuteen YHDISTÄMISLAUSEKKEESEEN.

SQL Server tukee kahdenlaisia CTEs-rekursiivinen ja nonrecursive. Tässä artikkelissa, selitän, miten luoda molempia tyyppejä., Esimerkkejä I antaa ovat perustuu paikalliseen SQL Server-esiintymä 2008 ja hakea tietoja AdventureWorks2008-mallitietokanta.

Työskentely Yhteisen Pöydän Ilmaisuja,

Voit määrittää CTEs lisäämällä, JOISSA mainittiin suoraan, ennen kuin sinun VALITA, LISÄTÄ, PÄIVITTÄÄ, POISTAA tai YHDISTÄÄ lausunto.,”>

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., Lisäksi jokaiselle CTE: lle on annettava nimi, kuten avainsana ja SELECT statement. Voit myös antaa sarakenimiä (pilkuilla erotettuina), kunhan nimien määrä vastaa tulosjoukon palauttamien sarakkeiden määrää.

VALITSE lausunnon CTE-kysely on noudatettava samoja vaatimuksia kuin ne, joita käytetään luoda näkymä. Lisätietoja näistä vaatimuksista, katso aihe ” luo näkymä (Transact-SQL)” SQL Server Books Online. Lisätietoja CTEs yleensä, katso aihe ” common_table_expression (Transact-SQL).,”

Kun olet määrittänyt teidän KANSSA lauseke tarvittavat CTEs, voit viittaus niitä CTEs kuin muu taulukko. Voit kuitenkin viitata CTE: hen vain lausekkeen toimeenpanoalueella, joka seuraa välittömästi WITH-lauseketta. Kun olet suorittanut lausuntosi, CTE – tulosjoukko ei ole muiden lausumien käytettävissä.

Luo Nonrecursive Yhteisen Pöydän Ilme

nonrecursive CTE on yksi, joka ei viittaa itsessään sisällä CTE. Nonrecursive CTEs taipumus olla yksinkertaisempi kuin rekursiivinen CTEs, joka on, miksi olen aloittamassa tämä tyyppi.,cteTotalSales:

Kun olen määritä CTE nimi, olen tarjota kaksi sarakkeen nimet, SalesPersonID ja NetSales, jotka ovat sulkeisiin ja erotetaan pilkulla. Tämä tarkoittaa, että CTE-kyselyn palauttaman tulosjoukon on palautettava kaksi saraketta.

seuraavaksi annan avainsanan, sitten joukon sulkeita, jotka liittävät CTE-kyselyn. Tässä tapauksessa SELECT statement palauttaa kunkin myyntihenkilön kokonaismyynnin (kokonaismyynti ryhmiteltynä myyjätunnuksen mukaan)., Kuten näette, CTE kysely voi sisältää Transact-SQL toimintoja, RYHMÄN lausekkeita, tai mitä tahansa elementtejä, että SELECT näkökulmasta määritelmä voi sisältää.

voin nyt viitata ctetotalsalesiin heti seuraavassa lausumassa. Tästä esimerkistä luon valikoidun lausuman, joka liittyy myyntiin.vSalesPerson view to ctetotalales, perustuu myyjätunnukseen. Sitten vedän nimet ja sijainnit näkyvistä ja liikevaihdon CTE: stä. Seuraavassa taulukossa on esitetty tämän lausunnon tulokset.,

Kuten näitte aiemmin syntaksin, voit sisällyttää useita CTEs show, JOSSA mainittiin.,

ensimmäinen CTE-cteTotalSales-on samanlainen kuin edellisessä esimerkissä paitsi, että where-lauseke on edelleen pätevä kuuluu myynti vain vuodesta 2003 alkaen. Kun määrittelen ctetotalales, lisään pilkun, ja sitten määritellä cteTargetDiff, joka laskee eron myynnin kokonaismäärän ja myyntikiintiön.

uusi CTE määritelmä määrittää kolme saraketta tulos asettaa: SalesPersonID, SalesQuota, ja QuotaDiff., Kuten arvata saattaa, CTE-kysely palauttaa kolme saraketta. Ensimmäinen on myyjän tunnus. Toinen on myyntikiintiö. Koska myyntikiintiötä ei ole määritelty joillekin myyjille, käytän kuitenkin tapausilmoitusta. Jos arvo on nolla, arvo on 0, muuten käytetään todellista Myyntikiintiötä.

lopullinen palsta on liikevaihdon ja myyntikiintiön erotus. Käytän jälleen tapauskohtaista lausuntoa. Jos Myyntikiintiön arvo on mitätön, käytetään NetSales-arvoa, muuten myyntikiintiö vähennetään liikevaihdosta eron saavuttamiseksi.,

jotain mielenkiintoista huomata toisesta CTE-kyselystä, että olen liittynyt myyntiin.Myyjä taulukko ensimmäiseen CTE-ctetotalales-joten voisin laskea eron kokonaismyynnin ja myyntikiintiön. Kun määrittelet useita CTEs yhdellä lausekkeella, voit viitata edeltävään CTEs (mutta ei toisin päin).

Kun olen määritellyt minun CTEs, en voi viitata ne ensimmäinen lausunto, joka seuraa CTE, kuten näitte edellisessä esimerkissä. Tässä tapauksessa Liityn myyntiin.,vSalesPerson view to ctetotalales and then join to ctetargetdiff, all based on the salesperson ID. Oma Valitse lista sitten sisältää sarakkeet kaikista kolmesta lähteestä. Lausunto palauttaa seuraavassa taulukossa esitetyt tulokset.

Kuten voit nähdä, myynti tiedot on säädetty kaikki myyjät, mukaan lukien kaupunki, jossa he asuvat, niiden liikevaihto, niiden myynti kiintiö, ja lasketaan ero kahden luvut. Tällöin kaikki ylittävät hyvin kiintiön, jossa on määritelty kiintiö.,

luo rekursiivisen yhteisen taulukon lausekkeen

rekursiivinen CTE on sellainen, joka viittaa itseensä kyseisen CTE: n sisällä. Rekursiivinen CTE on hyödyllinen työskenneltäessä hierarkkinen tietoja, koska CTE edelleen suorittaa kunnes kysely palauttaa koko hierarkian.

tyypillinen esimerkki hierarkkisesta datasta on taulukko, jossa on luettelo työntekijöistä. Kunkin työntekijän osalta taulukossa on maininta kyseisen henkilön johtajasta. Tämä viittaus on itse työntekijän tunnus samassa taulukossa., Voit käyttää rekursiivista CTE: tä työntekijöiden tietojen hierarkian näyttämiseen, kuten se näkyisi organisaatiokaaviossa.

huomaa, että väärin luotu CTE voisi syöttää äärettömän silmukan. Tämän estämiseksi voit sisällyttää maxrecursion-vihjeen ensisijaisten VALINTALAUSEKKEIDEN VALINTALAUSEKKEESEEN, lisätä, päivittää, poistaa tai yhdistää lausekkeen. Lisätietoja kyselyvihjeiden käytöstä on SQL Server Books Online-sivulla ”Kyselyvihjeet (Transact-SQL)”.,

osoittaa, miten rekursiivinen CTE toimii, olen käyttänyt seuraavia Transact-SQL luoda ja kansoittavat Työntekijät taulukko AdventureWorks2008-tietokanta:

Koska, saatat ymmärtää, että AdventureWorks2008-tietokanta sisältää jo Työvoimaa.Työntekijöiden pöytä. Tässä taulukossa käytetään kuitenkin nyt hierarkkista tietotyyppiä hierarkkisen datan tallentamiseen, mikä toisi mukanaan tarpeetonta monimutkaisuutta, kun yritetään osoittaa rekursiivista CTE: tä. Siksi loin Oman pöydän., Kuitenkin, jos haluat kokeilla rekursiivinen CTE ilman luominen ja asuttavat uuden taulukon, voit käyttää AdventureWorks näyte tietokanta, joka toimitetaan SQL Server 2005. Inhimilliset Voimavarat.Kyseisen tietokannan työntekijätaulukko tallentaa tiedot samalla tavalla kuin yllä oleva taulukko.,div>2

Kuten voit nähdä, CTE palaa viisi saraketta: EmpID, FirstName, LastName, MgrID, ja EmpLevel. EmpLevel-palstalla tarkoitetaan hierarkian tasoa, johon työntekijät mahtuvat. Hierarkian korkein taso on 1, seuraava taso 2, jota seuraa 3 ja niin edelleen.

CTE-kysely koostuu itse kahdesta valitusta lausekkeesta, jotka liittyvät UNION ALL operatoriin., Rekursiivisessa CTE-kyselyssä on oltava vähintään kaksi jäsentä (lausumia), jotka unioni yhdistää ALL, UNION, INTERSECT, or EXCEPT operator. Tässä esimerkissä ensimmäinen SELECT on ankkuri jäsen, ja toinen lausunto on rekursiivinen jäsen. Kaikki ankkuri jäsenten on ennen rekursiivinen jäseniä, ja vain rekursiivinen jäsenet voivat viitata CTE itse. Lisäksi kaikkien jäsenten on palautettava sama määrä sarakkeita vastaavat tietotyypit.

katsotaan nyt tarkemmin itse lausuntoja., Ensimmäinen lausuma, ankkurijäsen, hakee Työntekijätunnuksen, etunimen, sukunimen ja esimiehen tunnuksen työntekijöiden pöydästä, jossa esimiehen tunnus on nolla. Tämä olisi hierarkian huipulla oleva työntekijä, eli tämä henkilö ei raportoi kenellekään. Näin ollen managerin ID-arvo on nolla. Pohtia, että tämä henkilö on yläosassa hierarkian, määrittää arvo 1 EmpLevel sarakkeessa.

toisen lausuman CTE-kysely-rekursiivinen jäsen-myös hakee työntekijän ID, etunimi, sukunimi, ja johtaja TUNNUS työntekijät Työntekijät-taulukkoon., Huomaa kuitenkin, että liityn työntekijöiden taulukkoon itse CTE: lle. Lisäksi liittyminen perustuu henkilöstötaulukon johtajatunnukseen ja CTE: n työntekijätunnukseen. Näin CTE kiertää työntekijöiden pöydän läpi, kunnes se palauttaa koko hierarkian.

vielä Yksi asia huomata noin toinen toteamus on, että EmpLevel sarake, voin lisätä arvo 1 EmpLevel arvoa, koska se näkyy CTE. Näin joka kerta, kun lausuma kiertää hierarkian läpi, sovelletaan seuraavaa oikeaa tasoa tason työntekijöihin.,

Kun olen määritellä minun KANSSA lausekkeen, en luo SELECT, joka hakee tiedot CTE. Huomaa kuitenkin, että Esimiespalstalle haen CTE: ssä johtajan tunnukseen liittyvän työntekijän etu-ja sukunimen. Näin voin näyttää johtajan koko nimen jokaiselle työntekijälle. Seuraavassa taulukossa on SELECT-lausuman ja sen CTE: n palauttama tulosjoukko.,

Kuten voit nähdä, CTE, onko rekursiivinen tai nonrecursive, voi olla hyödyllinen työkalu, kun haluat luoda väliaikainen tulos sarjaa, jota voidaan käyttää SELECT, INSERT, PÄIVITTÄÄ, POISTAA tai YHDISTÄÄ lausunto. Tavallaan CTE on kuin johdettu taulukko: sitä ei tallenneta objektina ja se on voimassa vain ensisijaisen lausuman suorittamisen aikana. Johdetusta taulukosta poiketen CTE: hen voidaan kuitenkin viitata useita kertoja kyselyssä ja se voi olla itseohjautuva. Ja mikä parasta, CTEs on suhteellisen helppo toteuttaa.,

olet huomannut, että Bob käyttää Adventureworks2008: Aa eikä Adventureworksia. Jos haluat käyttää näiden esimerkkien vastaan AdventureWorks-tietokannan sijaan AdventureWorks2008-tietokanta, sinun pitäisi muuttaa BusinessEntityID sarakkeen SalesPersonID sarakkeessa.