logo

TechCodeview

Цоммон Табле Екпрессион (ЦТЕ) у СКЛ Серверу

Користићемо СКЛ Серверове заједничке табеларне изразе или ЦТЕ да бисмо олакшали сложене спојеве и потупите. Такође пружа начин за испитивање хијерархијских података, као што је хијерархија организације. Овај чланак даје потпун преглед ЦТЕ-а, типова ЦТЕ-а, предности, недостатака и начина њиховог коришћења у СКЛ Сервер-у.

Шта је ЦТЕ у СКЛ Серверу?

ЦТЕ (Цоммон Табле Екпрессион) је једнократни скуп резултата који постоји само током трајања упита . Омогућава нам да се позивамо на податке унутар једног обима извршења израза СЕЛЕЦТ, ИНСЕРТ, УПДАТЕ, ДЕЛЕТЕ, ЦРЕАТЕ ВИЕВ или МЕРГЕ. То је привремено јер се његов резултат не може нигде сачувати и биће изгубљен чим се заврши извршење упита. Прво је дошао са верзијом СКЛ Сервера 2005. ДБА је увек преферирао да ЦТЕ користи као алтернативу за подупит/приказ. Они прате АНСИ СКЛ 99 стандард и компатибилни су са СКЛ-ом.

ЦТЕ синтакса у СКЛ Серверу

ЦТЕ синтакса укључује ЦТЕ име, опциону листу колона и изјаву/упит који дефинише израз заједничке табеле (ЦТЕ). Након дефинисања ЦТЕ-а, можемо га користити као приказ у упиту СЕЛЕЦТ, ИНСЕРТ, УПДАТЕ, ДЕЛЕТЕ и МЕРГЕ.

Следеће је основна синтакса ЦТЕ у СКЛ Серверу:

 WITH cte_name (column_names) AS (query) SELECT * FROM cte_name;

У овој синтакси:

  • Прво смо навели ЦТЕ име на које ће се касније позивати у упиту.
  • Следећи корак је креирање листе колона раздвојених зарезима. Осигурава да број колона у аргументима ЦТЕ дефиниције и број колона у упиту морају бити исти. Ако нисмо дефинисали колоне ЦТЕ аргумената, користиће колоне упита које дефинишу ЦТЕ.
  • Након тога, користићемо кључну реч АС иза имена израза, а затим дефинисати СЕЛЕЦТ наредбу чији скуп резултата попуњава ЦТЕ.
  • Коначно, користићемо ЦТЕ име у упиту као што је СЕЛЕЦТ, ИНСЕРТ, УПДАТЕ, ДЕЛЕТЕ и МЕРГЕ изјава.

То треба имати на уму док пишете дефиницију ЦТЕ упита; не можемо користити следеће клаузуле:

  1. ОРДЕР БИ осим ако не користите и као ТОП клаузулу
  2. У
  3. ОПТИОН клаузула са саветима за упите
  4. ЗА БРОВСЕ

Слика испод је приказ дефиниције ЦТЕ упита.

ЦТЕ у СКЛ Серверу

Овде је први део ЦТЕ израз који садржи СКЛ упит који се може покренути независно у СКЛ-у. А други део је упит који користи ЦТЕ за приказ резултата.

Пример

Хајде да разумемо како ЦТЕ функционише у СКЛ Серверу користећи различите примере. Овде ћемо користити табелу ' купац ' за демонстрацију. Претпоставимо да ова табела садржи следеће податке:

ЦТЕ у СКЛ Серверу

У овом примеру, назив ЦТЕ је цустомерс_ин_невиорк , потупит који дефинише ЦТЕ враћа три колоне име клијента, имејл, и држава . Као резултат тога, ЦТЕ цустомерс_ин_невиорк ће вратити све купце који живе у држави Њујорк.

Након што смо дефинисали ЦТЕ цустомерс_ин_невиорк, референцирали смо га у СЕЛЕЦТ изјаву да добијете детаље о тим купцима који се налазе у Њујорку.

 WITH customers_in_NewYork AS (SELECT * FROM customer WHERE state = 'New York') SELECT c_name, email, state FROM customers_in_NewYork;

Након извршења горње изјаве, она ће дати следећи излаз. Овде можемо видети да резултат враћа само оне информације о клијентима који се налазе у држави Њујорк.

ЦТЕ у СКЛ Серверу

Вишеструки ЦТЕ

У неким случајевима, мораћемо да креирамо више ЦТЕ упита и да их спојимо да бисмо видели резултате. У овом сценарију можемо користити вишеструки ЦТЕ концепт. Морамо да користимо оператор зареза да креирамо више ЦТЕ упита и да их спојимо у једну наредбу. Оператору зареза ',' мора претходити име ЦТЕ да би се разликовало више ЦТЕ.

Вишеструки ЦТЕ нам помажу у поједностављивању сложених упита који се на крају спајају. Сваки сложени комад имао је свој ЦТЕ, на који се тада могло референцирати и спојити ван клаузуле ВИТХ.

НАПОМЕНА: Вишеструка ЦТЕ дефиниција се може дефинисати помоћу УНИОН, УНИОН АЛЛ, ЈОИН, ИНТЕРСЕЦТ или ЕКСЦЕПТ.

Синтакса у наставку то јасније објашњава: 

 WITH cte_name1 (column_names) AS (query), cte_name2 (column_names) AS (query) SELECT * FROM cte_name UNION ALL SELECT * FROM cte_name;

Пример

Хајде да разумемо како вишеструки ЦТЕ функционише у СКЛ Серверу. Овде ћемо користити горе наведено ' купац ' сто за демонстрацију.

У овом примеру смо дефинисали два ЦТЕ имена цустомерс_ин_невиорк и цустомерс_ин_цалифорниа . Затим скуп резултата потупита ових ЦТЕ попуњава ЦТЕ. Коначно, користићемо ЦТЕ имена у упиту који ће вратити све купце који се налазе у Њу Јорк и држава Калифорнија .

 WITH customers_in_NewYork AS (SELECT * FROM customer WHERE state = 'New York'), customers_in_California AS (SELECT * FROM customer WHERE state = 'California') SELECT c_name, email, state FROM customers_in_NewYork UNION ALL SELECT c_name, email, state FROM customers_in_California;

Њујорк и држава Калифорнија.

ЦТЕ у СКЛ Серверу

Зашто нам је потребан ЦТЕ?

Попут приказа базе података и изведених табела, ЦТЕ могу олакшати писање и управљање сложеним упитима тако што ће их учинити читљивијим и једноставнијим. Ову карактеристику можемо постићи разбијањем сложених упита на једноставне блокове који се могу поново користити у поновном писању упита.

Неки од случајева његове употребе су дати у наставку:

  • Корисно је када треба да дефинишемо изведену табелу више пута у оквиру једног упита.
  • Корисно је када треба да креирамо алтернативу погледу у бази података.
  • Корисно је када треба да извршимо исту калкулацију више пута на више компоненти упита истовремено.
  • Корисно је када треба да користимо функције рангирања као што су РОВ_НУМБЕР(), РАНК() и НТИЛЕ().

Неке од његових предности су наведене у наставку:

Мадхури је рекао хајде
  • ЦТЕ олакшава одржавање кода.
  • ЦТЕ повећава читљивост кода.
  • Повећава перформансе упита.
  • ЦТЕ омогућава лаку имплементацију рекурзивних упита.

Типови ЦТЕ-а у СКЛ Серверу

СКЛ Сервер дели ЦТЕ (Цоммон Табле Екпрессионс) у две широке категорије:

  1. Рекурзивни ЦТЕ
  2. Нерекурзивни ЦТЕ

Рекурзивни ЦТЕ

Уобичајени табеларни израз је познат као рекурзивни ЦТЕ који се позива на себе. Његов концепт је заснован на рекурзији, која је дефинисана као ' примена рекурзивног процеса или дефиниције више пута .' Када извршимо рекурзивни упит, он се више пута понавља преко подскупа података. Једноставно се дефинише као упит који сам себе позива. У неком тренутку постоји крајњи услов, тако да се не назива бесконачно.

Рекурзивни ЦТЕ мора имати а УНИОН АЛЛ изјаву и другу дефиницију упита која упућује на сам ЦТЕ да би била рекурзивна.

Пример

Хајде да разумемо како рекурзивни ЦТЕ функционише у СКЛ Серверу. Размотрите доњу изјаву, која генерише серију првих пет непарних бројева: 

 WITH odd_num_cte (id, n) AS ( SELECT 1, 1 UNION ALL SELECT id+1, n+2 from odd_num_cte where id <5 ) select * from odd_num_cte; < pre> <p>When we execute this recursive CTE, we will see the output as below:</p> <img src="//techcodeview.com/img/sql-server-tutorials/86/common-table-expression-sql-server-5.webp" alt="CTE in SQL Server"> <p>The below example is the more advanced recursive CTE. Here, we are going to use the &apos; <strong>jtp_employees</strong> &apos; table for a demonstration that contains the below data:</p> <img src="//techcodeview.com/img/sql-server-tutorials/86/common-table-expression-sql-server-6.webp" alt="CTE in SQL Server"> <p>This example will display the hierarchy of employee data. Here table provides a reference to that person&apos;s manager for each employee. The reference is itself an employee id within the same table.</p> <pre> WITH cte_recursion (EmpID, FirstName, LastName, MgrID, EmpLevel) AS ( SELECT EmployeeID, FirstName, LastName, ManagerID, 1 FROM jtp_employees WHERE ManagerID IS NULL UNION ALL SELECT emp.EmployeeID, emp.FirstName, emp.LastName, emp.ManagerID, r.EmpLevel + 1 FROM jtp_employees emp INNER JOIN cte_recursion r ON emp.ManagerID = r.EmpID ) SELECT FirstName + &apos; &apos; + LastName AS FullName, EmpLevel, (SELECT FirstName + &apos; &apos; + LastName FROM jtp_employees WHERE EmployeeID = cte_recursion.MgrID) AS Manager FROM cte_recursion ORDER BY EmpLevel, MgrID </pre> <p>This CTE will give the following output where we can see the hierarchy of employee data:</p> <img src="//techcodeview.com/img/sql-server-tutorials/86/common-table-expression-sql-server-7.webp" alt="CTE in SQL Server"> <h3>Non-Recursive CTE</h3> <p>A common table expression that doesn&apos;t reference itself is known as a non-recursive CTE. A non-recursive CTE is simple and easier to understand because it does not use the concept of recursion. According to the CTE Syntax, each CTE query will begin with a &apos; <strong>With</strong> &apos; clause followed by the CTE name and column list, then AS with parenthesis.</p> <h2>Disadvantages of CTE</h2> <p>The following are the limitations of using CTE in SQL Server:</p> <ul> <li>CTE members are unable to use the keyword clauses like Distinct, Group By, Having, Top, Joins, etc.</li> <li>The CTE can only be referenced once by the Recursive member.</li> <li>We cannot use the table variables and CTEs as parameters in stored procedures.</li> <li>We already know that the CTE could be used in place of a view, but a CTE cannot be nested, while Views can.</li> <li>Since it&apos;s just a shortcut for a query or subquery, it can&apos;t be reused in another query.</li> <li>The number of columns in the CTE arguments and the number of columns in the query must be the same.</li> </ul> <hr></5>

Овај ЦТЕ ће дати следећи излаз где можемо видети хијерархију података о запосленима:

ЦТЕ у СКЛ Серверу

Нерекурзивни ЦТЕ

Уобичајени табеларни израз који се не позива на себе познат је као нерекурзивни ЦТЕ. Нерекурзивни ЦТЕ је једноставан и лакши за разумевање јер не користи концепт рекурзије. Према ЦТЕ синтакси, сваки ЦТЕ упит ће почети са ' Витх ' клаузула праћена именом ЦТЕ и листом колона, затим АС са заградама.

Недостаци ЦТЕ

Следећа су ограничења коришћења ЦТЕ у СКЛ Серверу:

  • Чланови ЦТЕ-а не могу да користе клаузуле кључних речи као што су Дистинцт, Гроуп Би, Хавинг, Топ, Јоинс, итд.
  • Рекурзивни члан може само једном да референцира ЦТЕ.
  • Не можемо користити променљиве табеле и ЦТЕ као параметре у ускладиштеним процедурама.
  • Већ знамо да се ЦТЕ може користити уместо погледа, али ЦТЕ не може бити угнежђен, док погледи могу.
  • Пошто је то само пречица за упит или потупит, не може се поново користити у другом упиту.
  • Број колона у ЦТЕ аргументима и број колона у упиту морају бити исти.