Redgate Hub (Čeština)

zavedeno v SQL Server 2005, common table expression (CTE) je dočasná pojmenovaná sada výsledků, na kterou můžete odkazovat v příkazu SELECT, INSERT, UPDATE nebo DELETE. Můžete také použít CTE v prohlášení vytvořit pohled, jako součást výběru dotazu view. Kromě toho můžete od SQL Server 2008 Přidat CTE do nového příkazu sloučení.

SQL Server podporuje dva typy CTE-rekurzivní a nerecurzivní. V tomto článku vysvětluji, jak vytvořit oba typy., Příklady, které poskytuji, jsou založeny na místní instanci SQL Serveru 2008 a načítají data ze vzorové databáze AdventureWorks2008.

práce s běžnými výrazy tabulky

definujete CTEs přidáním klauzule s přímo před příkazem SELECT, INSERT, UPDATE, DELETE nebo MERGE.,“>

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., Kromě toho musíte pro každou CTE zadat jméno, Klíčové slovo AS a příkaz SELECT. Můžete také zadat názvy sloupců (oddělené čárkami), pokud počet jmen odpovídá počtu sloupců vrácených sadou výsledků.

příkaz SELECT ve vašem dotazu CTE musí splňovat stejné požadavky jako požadavky použité pro vytvoření zobrazení. Podrobnosti o těchto požadavcích naleznete v tématu “ vytvořit pohled (Transact-SQL)“ v knihách serveru SQL online. Další podrobnosti o CTEs obecně naleznete v tématu “ s common_table_expression (Transact-SQL).,“

poté, co definujete klauzuli s potřebnými CTE, můžete tyto CTE odkazovat jako na jakoukoli jinou tabulku. Můžete však odkazovat na CTE pouze v rozsahu provádění prohlášení, které okamžitě následuje klauzuli s. Po spuštění příkazu není sada výsledků CTE dostupná jiným příkazům.

vytvoření Nerecursivního společného tabulkového výrazu

nerecursivní CTE je ten, který se v rámci CTE neodkazuje. Nonrecursive CTEs bývají jednodušší než rekurzivní CTEs, což je důvod, proč začínám s tímto typem.,cteTotalSales:

Poté, co jsem zadat CTE jméno, jsem poskytovat dva názvy sloupců, SalesPersonID a NetSales, které jsou uzavřeny v závorkách a odděleny čárkou. To znamená, že sada výsledků vrácená dotazem CTE musí vrátit dva sloupce.

dále poskytuji Klíčové slovo AS a pak sadu závorek, které uzavírají dotaz CTE. V tomto případě výkaz SELECT vrátí celkový prodej pro každou prodejní osobu (celkový prodej seskupený podle ID prodejce)., Jak můžete vidět, dotaz CTE může zahrnovat funkce Transact-SQL, skupinu podle doložek nebo všechny prvky, které může příkaz SELECT v definici zobrazení obsahovat.

nyní mohu odkazovat na cteTotalSales v prohlášení, které okamžitě následuje. Pro tento příklad vytvořím příkaz SELECT, který se připojí k prodeji.vSalesPerson pohled na cteTotalSales, na základě ID prodejce. Poté vytáhnu jména a umístění z pohledu a čistého prodeje z CTE. Následující tabulka ukazuje výsledky vrácené tímto Prohlášením.,

Jak jste viděli dříve v syntaxi, můžete zahrnout více CTE v klauzuli s.,

první CTE-cteTotalSales-je podobné jako v předchozím příkladě, kromě toho, že tam, KDE ustanovení byla dále kvalifikovaný patří prodej pouze od roku 2003. Poté, co definuji cteTotalSales, přidám čárku a poté definuji cteTargetDiff, který vypočítá rozdíl mezi celkovou prodejní kvótou a prodejní kvótou.

nová definice CTE určuje tři sloupce pro sadu výsledků: SalesPersonID, SalesQuota a QuotaDiff., Jak byste očekávali, dotaz CTE vrátí tři sloupce. První je ID prodejce. Druhým je prodejní kvóta. Nicméně, protože prodejní kvóta není definována pro některé prodejce, používám případové prohlášení. Pokud je hodnota null, je tato hodnota nastavena na 0, jinak se použije skutečná hodnota SalesQuota.

vrácený konečný sloupec je rozdíl mezi čistou prodejní a prodejní kvótou. Opět používám případové prohlášení. Pokud je hodnota SalesQuota nulová, použije se hodnota NetSales, jinak se prodejní kvóta odečte od čistého prodeje, aby se dosáhlo rozdílu.,

něco zajímavého k poznámce o druhém dotazu CTE je, že jsem se připojil k prodeji.Tabulka prodejce k prvnímu CTE-cteTotalSales-takže jsem mohl vypočítat rozdíl mezi celkovým prodejem a prodejní kvótou. Kdykoli definujete více CTE v jednom s klauzulí, můžete odkazovat na předchozí CTE (ale ne naopak).

jakmile jsem definoval své CTE, mohu je odkazovat v prvním prohlášení, které následuje po CTE, jak jste viděli v předchozím příkladu. V tomto případě se připojuji k prodeji.,vSalesPerson pohled na cteTotalSales a pak se připojit k cteTargetDiff, vše na základě ID prodejce. Můj seznam SELECT pak obsahuje sloupce ze všech tří zdrojů. Příkaz vrátí výsledky uvedené v následující tabulce.

Jak můžete vidět, prodeje dat je k dispozici u všech prodejců, včetně města, v němž mají bydliště, jejich čisté prodeje, jejich prodejní kvóty, a vypočítá rozdíl mezi dvěma čísly. V tomto případě každý dobře překračuje kvótu, kde byla definována kvóta.,

vytvoření rekurzivního společného tabulkového výrazu

rekurzivní CTE je ten, který se v tomto CTE odkazuje. Rekurzivní CTE je užitečné při práci s hierarchickými daty, protože CTE pokračuje v provádění, dokud dotaz nevrátí celou hierarchii.

typickým příkladem hierarchických dat je tabulka, která obsahuje seznam zaměstnanců. Pro každého zaměstnance poskytuje tabulka odkaz na manažera této osoby. Tento odkaz je sám o sobě ID zaměstnance ve stejné tabulce., Rekurzivní CTE můžete použít k zobrazení hierarchie dat zaměstnanců, jak by se objevilo v organizačním grafu.

Všimněte si, že nesprávně vytvořený CTE může vstoupit do nekonečné smyčky. Chcete-li tomu zabránit, můžete zahrnout nápovědu MAXRECURSION do klauzule OPTION v primárním příkazu SELECT, INSERT, UPDATE, DELETE nebo MERGE. Informace o použití rad dotazů naleznete v tématu “ Rady dotazů (Transact-SQL)“ v knihách serveru SQL online.,

prokázat, jak rekurzivní CTE funguje, použil jsem následující Transact-SQL příkazy vytvořit a naplnit tabulku Zaměstnanci v AdventureWorks2008 databáze:

Jak jste si možná uvědomíte, AdventureWorks2008 databáze již obsahuje lidských zdrojů.Zaměstnanecký stůl. Nicméně, že tabulka se používá hierarchyid typ dat pro ukládání hierarchických dat, které by zbytečnou složitost, když se snaží prokázat, rekurzivní CTE. Z tohoto důvodu jsem vytvořil svůj vlastní stůl., Pokud však chcete vyzkoušet rekurzivní CTE bez vytvoření a vyplnění nové tabulky, můžete použít ukázkovou databázi AdventureWorks, která byla dodána s SQL Serverem 2005. Lidské Zdroje.Zaměstnanecká tabulka v této databázi ukládá data způsobem podobným tabulce, kterou jsem vytvořil výše.,div>2

Jak můžete vidět, CTE se vrací pět sloupců: EmpID, FirstName, LastName, MgrID, a EmpLevel. Sloupec EmpLevel odkazuje na úroveň v hierarchii, do které zaměstnanci zapadají. Nejvyšší úroveň hierarchie je 1, další úroveň je 2, následovaná 3 a tak dále.

dotaz CTE je sám o sobě tvořen dvěma výběrovými prohlášeními, spojenými s Union ALL operator., Rekurzivní dotaz CTE musí obsahovat alespoň dva členy (příkazy), propojené unií All, UNION, INTERSECT nebo s výjimkou operátora. V tomto příkladu je prvním příkazem SELECT člen kotvy a druhým příkazem je rekurzivní člen. Všichni členové kotvy musí předcházet rekurzivním členům a pouze rekurzivní členové mohou odkazovat na samotný CTE. Kromě toho musí všichni členové vrátit stejný počet sloupců s odpovídajícími typy dat.

nyní se podívejme blíže na samotná prohlášení., První prohlášení, člen kotvy, načte ID zaměstnance, jméno, příjmení a ID manažera z tabulky zaměstnanců, kde je ID manažera null. To by byl zaměstnanec v horní části hierarchie, což znamená, že tato osoba se nikomu nehlásí. V důsledku toho je hodnota ID Správce null. Chcete-li odrážet, že tato osoba je v horní části hierarchie, přiřadím hodnotu 1 do sloupce EmpLevel.

druhé prohlášení v dotazu CTE-rekurzivní člen-také načte ID zaměstnance, křestní jméno, příjmení a ID manažera pro zaměstnance v tabulce zaměstnanců., Všimněte si však, že se připojuji ke stolu zaměstnanců k samotnému CTE. Kromě toho je spojení založeno na ID manažera v tabulce zaměstnanců a ID zaměstnance v CTE. Tímto způsobem bude CTE procházet tabulkou zaměstnanců, dokud nevrátí celou hierarchii.

jedna další položka, která si všimne druhého příkazu, je, že pro sloupec EmpLevel přidám hodnotu 1 k hodnotě EmpLevel, jak se objeví v CTE. Tímto způsobem, pokaždé, když se prohlášení protáhne hierarchií, se na zaměstnance na úrovni použije další správná úroveň.,

poté, co definuji svou klauzuli s, vytvořím příkaz SELECT, který načte data z CTE. Všimněte si však, že pro sloupec Správce načtu jméno a příjmení zaměstnance spojené s ID správce v CTE. To mi umožňuje zobrazit celé jméno manažera pro každého zaměstnance. Následující tabulka zobrazuje sadu výsledků vrácenou příkazem SELECT a jeho CTE.,

Jak můžete vidět, CTE, zda rekurzivní nebo nonrecursive, může být užitečný nástroj, když potřebujete vytvořit dočasné sady výsledků, které mohou být přístupné v SELECT, INSERT, UPDATE, DELETE, nebo SLOUČIT prohlášení. V jistém smyslu je CTE jako odvozená tabulka: není uložena jako objekt a je platná pouze při provádění primárního příkazu. Nicméně, na rozdíl od odvozené tabulky, CTE může být odkazováno vícekrát v rámci dotazu a to může být self-odkazování. A nejlepší ze všeho je, že CTE jsou relativně snadno implementovatelné.,

jste si všimli, že Bob používá AdventureWorks2008 spíše než AdventureWorks. Pokud dáváte přednost spuštění těchto příkladů proti databázi AdventureWorks spíše než databázi AdventureWorks2008, měli byste změnit sloupec BusinessEntityID na sloupec SalesPersonID.