logo

TechCodeview

Общ табличен израз (CTE) в SQL Server

Ще използваме общите таблични изрази или CTE на SQL Server, за да улесним сложните съединения и подзаявки. Той също така предоставя начин за запитване към йерархични данни, като например организационна йерархия. Тази статия дава пълен преглед на CTE, видове CTE, предимства, недостатъци и как да ги използвате в SQL Server.

Какво е CTE в SQL Server?

CTE (Common Table Expression) е еднократен набор от резултати, който съществува само за продължителността на заявката . Позволява ни да се позоваваме на данни в обхвата на изпълнение на един оператор SELECT, INSERT, UPDATE, DELETE, CREATE VIEW или MERGE. Той е временен, защото резултатът от него не може да бъде съхранен никъде и ще бъде загубен веднага щом приключи изпълнението на заявката. За първи път дойде с версия на SQL Server 2005. DBA винаги е предпочитал CTE да се използва като алтернатива на подзаявка/изглед. Те следват стандарта ANSI SQL 99 и са съвместими с SQL.

Синтаксис на CTE в SQL Server

CTE синтаксисът включва име на CTE, незадължителен списък с колони и израз/заявка, която дефинира общия табличен израз (CTE). След като дефинираме CTE, можем да го използваме като изглед в заявка SELECT, INSERT, UPDATE, DELETE и MERGE.

Следва основният синтаксис на CTE в SQL Server:

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

В този синтаксис:

  • Първо сме посочили името на CTE, което ще бъде посочено по-късно в заявка.
  • Следващата стъпка е да създадете списък от колони, разделени със запетая. Той гарантира, че броят на колоните в аргументите на CTE дефиницията и броят на колоните в заявката трябва да бъдат еднакви. Ако не сме дефинирали колоните на CTE аргументите, ще използва колоните на заявката, които дефинират CTE.
  • След това ще използваме ключовата дума AS след името на израза и след това ще дефинираме оператор SELECT, чийто набор от резултати попълва CTE.
  • И накрая, ще използваме името на CTE в заявка като SELECT, INSERT, UPDATE, DELETE и MERGE израз.

Трябва да имате предвид, докато пишете дефиницията на CTE заявката; не можем да използваме следните клаузи:

  1. ORDER BY, освен ако не използвате и като TOP клауза
  2. INTO
  3. Клауза OPTION с подсказки за заявка
  4. ЗА РАЗГЛЕЖДАНЕ

Изображението по-долу е представяне на дефиницията на CTE заявка.

CTE в SQL Server

Тук първата част е CTE израз, който съдържа SQL заявка, която може да се изпълнява независимо в SQL. И втората част е заявката, която използва CTE за показване на резултата.

Пример

Нека разберем как работи CTE в SQL Server, използвайки различни примери. Тук ще използваме таблица ' клиент “ за демонстрация. Да предположим, че тази таблица съдържа следните данни:

CTE в SQL Server

В този пример името на CTE е клиенти_в_нюйорк , подзаявката, която дефинира CTE, връща трите колони име на клиента, имейл, и състояние . В резултат на това CTE customers_in_newyork ще върне всички клиенти, които живеят в щата Ню Йорк.

След като дефинирахме CTE customers_in_newyork, ние го посочихме в ИЗБЕРЕТЕ извлечение, за да получите подробности за онези клиенти, които се намират в Ню Йорк.

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

След изпълнение на горния оператор, той ще даде следния изход. Тук можем да видим, че резултатът връща само тази информация за клиенти, които се намират в щата Ню Йорк.

CTE в SQL Server

Множество CTE

В някои случаи ще трябва да създадем множество CTE заявки и да ги обединим, за да видим резултатите. Можем да използваме концепция за множество CTE в този сценарий. Трябва да използваме оператора запетая, за да създадем множество CTE заявки и да ги обединим в един израз. Операторът със запетая „,“ трябва да бъде предшестван от името на CTE, за да се разграничат множество CTE.

Множеството CTE ни помагат да опростим сложни заявки, които в крайна сметка се обединяват. Всяка сложна част имаше свой собствен CTE, който след това можеше да бъде препратен и свързан извън клаузата WITH.

ЗАБЕЛЕЖКА: Дефиницията на множество CTE може да се дефинира с помощта на UNION, UNION ALL, JOIN, INTERSECT или EXCEPT.

Синтаксисът по-долу го обяснява по-ясно: 

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

Пример

Нека разберем как работи множеството CTE в SQL Server. Тук ще използваме горното ' клиент ' маса за демонстрация.

В този пример сме дефинирали двете CTE имена клиенти_в_нюйорк и клиенти_в_калифорния . Тогава резултатният набор от подзаявки на тези CTE попълва CTE. И накрая, ще използваме имената на CTE в заявка, която ще върне всички клиенти, които се намират в Ню Йорк и Щат Калифорния .

 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;

Ню Йорк и щата Калифорния.

CTE в SQL Server

Защо се нуждаем от CTE?

Подобно на изгледите на бази данни и производните таблици, CTE могат да улеснят писането и управлението на сложни заявки, като ги направят по-четими и прости. Можем да постигнем тази характеристика, като разбием сложните заявки на прости блокове, които могат да се използват повторно при пренаписване на заявката.

Някои от случаите на употреба са дадени по-долу:

  • Полезно е, когато трябва да дефинираме извлечена таблица няколко пъти в рамките на една заявка.
  • Полезно е, когато трябва да създадем алтернатива на изглед в базата данни.
  • Полезно е, когато трябва да извършим едно и също изчисление няколко пъти върху множество компоненти на заявка едновременно.
  • Полезно е, когато трябва да използваме функции за класиране като ROW_NUMBER(), RANK() и NTILE().

Някои от неговите предимства са дадени по-долу:

Актьор Реха
  • CTE улеснява поддръжката на кода по-лесно.
  • CTE увеличава четливостта на кода.
  • Повишава производителността на заявката.
  • CTE прави възможно лесното прилагане на рекурсивни заявки.

Видове CTE в SQL Server

SQL Server разделя CTE (Common Table Expressions) на две големи категории:

  1. Рекурсивен CTE
  2. Нерекурсивен CTE

Рекурсивен CTE

Общ табличен израз е известен като рекурсивен CTE, който препраща към себе си. Концепцията му се основава на рекурсия, която се дефинира като ' прилагането на рекурсивен процес или дефиниция многократно .' Когато изпълняваме рекурсивна заявка, тя многократно итерира подмножество от данни. Просто се дефинира като заявка, която се самоизвиква. В даден момент има крайно условие, така че то не се самоизвиква безкрайно.

Рекурсивният CTE трябва да има a СЪЮЗ ВСИЧКИ израз и втора дефиниция на заявка, която препраща към самия CTE, за да бъде рекурсивна.

Пример

Нека разберем как работи рекурсивният CTE в SQL Server. Разгледайте изявлението по-долу, което генерира поредица от първите пет нечетни числа: 

 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>

Този CTE ще даде следния резултат, където можем да видим йерархията на данните за служителите:

CTE в SQL Server

Нерекурсивен CTE

Общ табличен израз, който не препраща към себе си, е известен като нерекурсивен CTE. Нерекурсивният CTE е прост и по-лесен за разбиране, защото не използва концепцията за рекурсия. Според CTE синтаксиса всяка CTE заявка ще започва с ' с ' клауза, последвана от името на CTE и списъка с колони, след това AS със скоби.

Недостатъци на CTE

Следните са ограниченията за използване на CTE в SQL Server:

  • Членовете на CTE не могат да използват клаузите за ключови думи като Distinct, Group By, Having, Top, Joins и др.
  • CTE може да бъде препратен само веднъж от рекурсивния член.
  • Не можем да използваме променливите на таблицата и CTE като параметри в запомнените процедури.
  • Вече знаем, че CTE може да се използва вместо изглед, но CTE не може да бъде вложен, докато Views може.
  • Тъй като това е просто пряк път за заявка или подзаявка, не може да се използва повторно в друга заявка.
  • Броят на колоните в CTE аргументите и броят на колоните в заявката трябва да са еднакви.