Redgate Hub (Magyar)

bevezetett SQL Server 2005, a common table expression (CTE) egy ideiglenes elnevezett eredményhalmaz, hogy hivatkozhat egy SELECT, INSERT, UPDATE, vagy DELETE utasítás. Ön is használja a CTE egy nézet létrehozása nyilatkozatot, részeként a nézet SELECT lekérdezés. Ezenkívül az SQL Server 2008-tól CTE-t is hozzáadhat az új egyesítési nyilatkozathoz.

az SQL Server kétféle CTE-rekurzív és nem recurzív. Ebben a cikkben elmagyarázom, hogyan lehet mindkét típust létrehozni., Az általam bemutatott példák az SQL Server 2008 helyi példányán alapulnak, és az adatokat a AdventureWorks2008 mintaadatbázisból szerezzük be.

közös Táblázatkifejezésekkel dolgozva

a CTE-ket úgy határozhatja meg, hogy közvetlenül a kijelölés, beillesztés, frissítés, törlés vagy egyesítés előtt hozzáad egy WITH záradékot.,”>

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., Ezenkívül minden CTE esetében meg kell adnia egy nevet, a AS kulcsszót, valamint egy SELECT nyilatkozatot. Oszlopneveket is megadhat (vesszővel elválasztva), mindaddig, amíg a nevek száma megegyezik az eredménykészlet által visszaküldött oszlopok számával.

a CTE lekérdezés SELECT utasításának ugyanazokat a követelményeket kell követnie, mint a nézet létrehozásához. Ezekről a követelményekről az SQL Server Online könyveiben lásd a “nézet létrehozása (Transact-SQL)” témakört. A CTE-kkel kapcsolatos további részletekért lásd a “common_table_expression (Transact-SQL).,”

miután meghatározta a WITH záradékot a szükséges CTE-kkel, akkor hivatkozhat ezekre a CTE-kre, mint bármely más táblázat. A CTE-t azonban csak a nyilatkozat végrehajtási hatókörén belül hivatkozhatja meg, amely azonnal követi az WITH záradékot. A nyilatkozat futtatása után a CTE eredménykészlet nem érhető el más kijelentésekhez.

Nemkurzív közös tábla kifejezés létrehozása

a nemkurzív CTE olyan, amely nem hivatkozik magára a CTE-n belül. A nemkurzív CTE-k általában egyszerűbbek, mint a rekurzív CTE-K, ezért kezdem ezt a típust.,cteTotalSales:

Miután adja meg a CTE név, én biztosít két oszlop nevek, SalesPersonID, valamint NetSales, amelyek zárt zárójelben, vesszővel elválasztva. Ez azt jelenti, hogy a CTE lekérdezés által visszaküldött eredménykészletnek két oszlopot kell visszatérnie.

ezután megadom az AS kulcsszót, majd zárójeleket, amelyek a CTE lekérdezést tartalmazzák. Ebben az esetben a SELECT utasítás minden egyes értékesítő személy teljes értékesítését adja vissza (az összes értékesítés az értékesítő azonosítója szerint csoportosítva)., Mint látható, a CTE lekérdezés tartalmazhat Transact-SQL funkciókat, csoportosításokat, vagy bármely olyan elemet, amelyet a nézetdefinícióban a SELECT utasítás tartalmazhat.

most már hivatkozhatok a cteTotalSales-re az azonnal következő nyilatkozatban. Ebben a példában létrehozok egy SELECT nyilatkozatot, amely csatlakozik az értékesítéshez.vSalesPerson nézet cteTotalSales, alapján az üzletkötő ID. Ezután kihúzom a neveket és a helyszíneket a nézetből és a nettó értékesítést a CTE-től. Az alábbi táblázat a jelen Nyilatkozat által visszaküldött eredményeket mutatja.,

amint azt a szintaxisban korábban láttuk, több CTE-t is beilleszthet a WITH záradékba.,

Az első CTE-cteTotalSales-ez hasonló az előző példa, de a HOL záradék már további minősített tartalmazza az értékesítés csak 2003-tól. Miután meghatároztam a cteTotalSales-t, hozzáadok egy vesszőt, majd definiálom a cteTargetDiff-et, amely kiszámítja az értékesítési összeg és az értékesítési kvóta közötti különbséget.

az új CTE definíció három oszlopot határoz meg az eredményhalmazhoz: SalesPersonID, SalesQuota és QuotaDiff., Ahogy az várható volt, a CTE lekérdezés három oszlopot ad vissza. Az első az eladó azonosítója. A második az értékesítési kvóta. Mivel azonban az értékesítési kvótát nem határozzák meg egyes értékesítők számára, eseti nyilatkozatot használok. Ha az érték null, akkor ez az érték 0-ra van állítva, különben a tényleges SalesQuota értéket használjuk.

a visszaküldött végső oszlop a nettó értékesítési és értékesítési kvóta közötti különbség. Ismét használok egy eset nyilatkozatot. Ha a SalesQuota érték null, akkor a NetSales értéket használják, különben az értékesítési kvótát levonják a nettó értékesítésből, hogy elérjék a különbséget.,

valami érdekes megjegyezni a második CTE lekérdezés, hogy én már csatlakozott az értékesítés.Értékesítési táblázat az első CTE-cteTotalSales-hez-így ki tudtam számítani a különbséget a teljes értékesítés és az értékesítési kvóta között. Amikor több CTE-t definiál egyetlen záradékkal, hivatkozhat az előző CTE-kre (de nem fordítva).

miután meghatároztam a CTE-met, hivatkozhatok rájuk a CTE-t követő első nyilatkozatban, amint azt az előző példában látta. Ebben az esetben csatlakozom az értékesítéshez.,vSalesPerson nézet cteTotalSales majd csatlakozzon cteTargetDiff, Minden alapján az üzletkötő ID. A kiválasztott lista ezután mindhárom forrásból oszlopokat tartalmaz. A nyilatkozat az alábbi táblázatban látható eredményeket adja vissza.

Mint látható, az eladási adatok minden értékesítők, beleértve a város, amelyben élnek, a nettó árbevétel, az értékesítési kvóta, valamint a számított különbség a két alak. Ebben az esetben mindenki jóval meghaladja a kvótát, ahol kvótát határoztak meg.,

rekurzív közös tábla kifejezés létrehozása

a rekurzív CTE az, amely a CTE-n belül hivatkozik magára. A rekurzív CTE akkor hasznos, ha hierarchikus adatokkal dolgozik, mert a CTE addig folytatódik, amíg a lekérdezés vissza nem adja a teljes hierarchiát.

a hierarchikus adatok tipikus példája egy táblázat, amely tartalmazza az alkalmazottak listáját. Minden alkalmazott esetében a táblázat utal az adott személy menedzserére. Ez a hivatkozás maga egy alkalmazott azonosítója ugyanabban a táblázatban., A rekurzív CTE segítségével megjelenítheti a munkavállalói adatok hierarchiáját, amint az a szervezeti diagramon megjelenik.

vegye figyelembe, hogy a helytelenül létrehozott CTE végtelen hurkot adhat meg. Ennek megakadályozása érdekében a maxrecursion tippet az elsődleges kijelölés, beszúrás, frissítés, törlés vagy egyesítési utasítás OPCIÓZÁRADÉKÁBA is beillesztheti. A lekérdezési tanácsok használatával kapcsolatos információkért lásd az SQL Server Online könyveiben a “Query Hints (Transact-SQL)” témát.,

annak bizonyítására, Hogy a rekurzív CTE működik, használtam a következő Transact-SQL-utasítások létrehozásához, majd feltölti az Alkalmazottak táblázat a AdventureWorks2008 adatbázis:

Mint gondolnád, a AdventureWorks2008 adatbázis már tartalmaz az Emberi.Alkalmazott asztal. Ez a táblázat azonban most a hierarchikus adattípust használja a hierarchikus adatok tárolására, ami szükségtelen komplexitást vezetne be a rekurzív CTE bemutatásakor. Ezért készítettem saját asztalt., Ha azonban rekurzív CTE-t szeretne kipróbálni egy új táblázat létrehozása és feltöltése nélkül, használhatja az SQL Server 2005-tel szállított AdventureWorks mintaadatbázist. Az Emberi Erőforrások.Az alkalmazotti táblázat az adatbázisban az adatokat a fenti táblázathoz hasonló módon tárolja.,div>2

mint látható, a CTE öt oszlopot ad vissza: EmpID, FirstName, LastName, Mgrid, and EmpLevel. Az EmpLevel oszlop a hierarchia azon szintjére utal, amelyben az alkalmazottak illeszkednek. A hierarchia legmagasabb szintje 1, A következő szint 2, majd 3 stb.

a CTE lekérdezés maga két kiválasztott kijelentésből áll, amelyek az UNION ALL operátorhoz kapcsolódnak., A rekurzív CTE lekérdezésnek legalább két tagot (kijelentéseket) kell tartalmaznia, amelyeket az Unió All, UNION, metszik, vagy az operátor kivételével. Ebben a példában az első SELECT utasítás a horgonytag, a második állítás pedig a rekurzív tag. Minden anchor tagnak meg kell előznie a rekurzív tagokat, és csak a rekurzív tagok hivatkozhatnak a CTE-re. Ezenkívül minden tagnak ugyanannyi oszlopot kell visszaküldenie a megfelelő adattípusokkal.

most nézzük meg közelebbről a nyilatkozatokat., Az első nyilatkozat, A horgonytag, letölti a munkavállalói azonosítót, a keresztnevet, a vezetéknevet és a menedzser azonosítót az alkalmazottak asztaláról, ahol a menedzser azonosítója null. Ez lenne a munkavállaló a hierarchia tetején, ami azt jelenti, hogy ez a személy senkinek sem jelent. Következésképpen a menedzser azonosító értéke null. Annak tükrözése érdekében, hogy ez a személy a hierarchia tetején van, hozzárendelek egy 1 értéket az EmpLevel oszlophoz.

a CTE lekérdezés második kijelentése-a rekurzív tag-a munkavállalói táblázatban szereplő alkalmazottak személyi igazolványát, keresztnevét, vezetéknevét és menedzser azonosítóját is lekérdezi., Vegye figyelembe azonban, hogy csatlakozom az alkalmazottak asztalához a CTE-hez. Ezenkívül a Csatlakozás az alkalmazotti táblázatban szereplő menedzser azonosítón, a CTE-ben pedig a munkavállalói azonosítón alapul. Ezzel a CTE végigjárja az alkalmazottak tábláját, amíg vissza nem adja a teljes hierarchiát.

egy másik elem, amelyet a második állítással kapcsolatban észre kell venni, az EmpLevel oszlop esetében az 1 értéket hozzáadom az EmpLevel értékhez, amint az a CTE-ben megjelenik. Így minden alkalommal, amikor a kijelentés a hierarchián keresztül hurkol, a következő helyes szintet alkalmazzák az alkalmazottak szintjén.,

miután meghatároztam a WITH záradékot, létrehozok egy SELECT nyilatkozatot, amely beolvassa az adatokat a CTE-ből. Vegye figyelembe azonban, hogy a menedzser oszlopban a CTE-ben a menedzser azonosítóhoz társított alkalmazott első és vezetéknevét kapom meg. Ez lehetővé teszi számomra, hogy megjelenítsem a menedzser teljes nevét minden alkalmazott számára. Az alábbi táblázat a SELECT utasítás és a CTE által visszaadott eredményhalmazt mutatja.,

mint látható, a CTE, akár rekurzív, akár nem recurzív, hasznos eszköz lehet, ha ideiglenes eredménykészleteket kell létrehoznia, amelyek elérhetők egy SELECT, INSERT, UPDATE, DELETE vagy MERGE nyilatkozatban. Bizonyos értelemben a CTE olyan, mint egy származtatott tábla: nem objektumként van tárolva, csak az elsődleges utasítás végrehajtása során érvényes. A származtatott táblával ellentétben azonban egy CTE-re többször hivatkozhatunk egy lekérdezés során, és lehet önhivatkozás is. A legjobb az egészben, hogy a CTE-k viszonylag könnyen végrehajthatók.,

észre fogod venni, hogy Bob AdventureWorks2008-ot használ a AdventureWorks helyett. Ha ezeket a példákat inkább a AdventureWorks adatbázis ellen szeretné futtatni, mint a AdventureWorks2008 adatbázist, akkor a BusinessEntityID oszlopot a SalesPersonID oszlopra kell módosítania.