Ми будемо використовувати загальні табличні вирази SQL Server або CTE, щоб полегшити складні об’єднання та підзапити. Він також надає спосіб запиту ієрархічних даних, таких як організаційна ієрархія. У цій статті наведено повний огляд CTE, типи CTE, переваги, недоліки та способи їх використання в SQL Server.

Що таке CTE у SQL Server?

CTE (Common Table Expression) — це одноразовий набір результатів, який існує лише протягом запиту . Це дозволяє нам посилатися на дані в межах одного оператора SELECT, INSERT, UPDATE, DELETE, CREATE VIEW або MERGE. Він є тимчасовим, оскільки його результат не може бути ніде збережений і буде втрачено, щойно виконання запиту буде завершено. Він уперше з’явився разом із версією SQL Server 2005. Адміністратор бази даних завжди віддавав перевагу використанню 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-запит, який можна виконувати незалежно в SQL. А друга частина — це запит, який використовує CTE для відображення результату.

приклад

Давайте розберемося, як 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 та об’єднати їх, щоб побачити результати. У цьому сценарії ми можемо використовувати концепцію кількох 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?

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

Деякі випадки його використання наведено нижче:

• Це корисно, коли нам потрібно визначити похідну таблицю кілька разів в одному запиті.
• Це корисно, коли нам потрібно створити альтернативу перегляду в базі даних.
• Це корисно, коли нам потрібно виконати те саме обчислення кілька разів для кількох компонентів запиту одночасно.
• Це корисно, коли нам потрібно використовувати такі функції ранжирування, як ROW_NUMBER(), RANK() і NTILE().

Деякі з його переваг наведено нижче:

алгоритм rr

• CTE полегшує обслуговування коду.
• CTE підвищує читабельність коду.
• Це підвищує продуктивність запиту.
• CTE дозволяє легко реалізувати рекурсивні запити.

Типи CTE в SQL Server

SQL Server поділяє CTE (загальні табличні вирази) на дві великі категорії:

1. Рекурсивний КТР
2. Нерекурсивний КТР

Рекурсивний КТР

Загальний табличний вираз відомий як рекурсивний 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. Нерекурсивний CTE простий і легший для розуміння, оскільки він не використовує концепцію рекурсії. Відповідно до синтаксису CTE, кожен запит CTE починатиметься з ' с ', за яким іде назва CTE та список стовпців, потім AS із дужками.

Недоліки КТР

Нижче наведено обмеження використання CTE у SQL Server:

• Учасники CTE не можуть використовувати ключові пропозиції, такі як Distinct, Group By, Having, Top, Joins тощо.
• Рекурсивний член може посилатися на CTE лише один раз.
• Ми не можемо використовувати змінні таблиці та CTE як параметри в збережених процедурах.
• Ми вже знаємо, що CTE можна використовувати замість представлення, але CTE не може бути вкладеним, тоді як Views може.
• Оскільки це лише ярлик для запиту чи підзапиту, його не можна повторно використовувати в іншому запиті.
• Кількість стовпців в аргументах CTE та кількість стовпців у запиті мають бути однаковими.