With as sql: WITH обобщенное_табличное_выражение (Transact-SQL)

Содержание

Обобщённое табличное выражение, оператор WITH

Обобщённое табличное выражение или CTE (Common Table Expressions) — это временный результирующий набор данных, к которому можно обращаться в последующих запросах. Для написания обобщённого табличного выражения используется оператор WITH.

-- Пример использования конструкции WITH
WITH Aeroflot_trips AS
    (SELECT TRIP.* FROM Company
        INNER JOIN Trip ON Trip.company = Company.id WHERE name = "Aeroflot")
SELECT plane, COUNT(plane) AS amount FROM Aeroflot_trips GROUP BY plane;

Выражение с WITH считается «временным», потому что результат не сохраняется где-либо на постоянной основе в схеме базы данных, а действует как временное представление, которое существует только на время выполнения запроса, то есть оно доступно только во время выполнения операторов SELECT, INSERT, UPDATE, DELETE или MERGE. Оно действительно только в том запросе, которому он принадлежит, что позволяет улучшить структуру запроса, не загрязняя глобальное пространство имён.

WITH название_cte [(столбец_1 [, столбец_2 ] …)] AS (подзапрос)
    [, название_cte [(столбец_1 [, столбец_2 ] …)] AS (подзапрос)] …

Порядок использования оператора WITH:

Ввести оператор WITH
Указать название обобщённого табличного выражения
Опционально: определить названия для столбцов получившегося табличного выражения, разделённых знаком запятой
Ввести AS и далее подзапрос, результат которого можно будет использовать в других частях SQL запроса, используя имя, определённое на 2 этапе
Опционально: если необходимо более одного табличного выражения, то ставится запятая и повторяются шаги 2-4

Создаём табличное выражение Aeroflot_trips, содержащие все полёты, совершенные авиакомпанией «Aeroflot»

WITH Aeroflot_trips AS
    (SELECT plane, town_from, town_to FROM Company
        INNER JOIN Trip ON Trip.company = Company.id WHERE name = "Aeroflot")
SELECT * FROM Aeroflot_trips;

plane	town_from	town_to
IL-86	Moscow	Rostov
IL-86	Rostov	Moscow

Аналогично, создаём табличное выражение Aeroflot_trips, но с переименованными колонками

WITH Aeroflot_trips (aeroflot_plane, town_from, town_to) AS
    (SELECT plane, town_from, town_to FROM Company
        INNER JOIN Trip ON Trip. company = Company.id WHERE name = "Aeroflot")
SELECT * FROM Aeroflot_trips;

aeroflot_plane	town_from	town_to
IL-86	Moscow	Rostov
IL-86	Rostov	Moscow

С помощью оператора WITH определяем несколько табличных выражений

WITH Aeroflot_trips AS
    (SELECT TRIP.* FROM Company
        INNER JOIN Trip ON Trip.company = Company.id WHERE name = "Aeroflot"),
    Don_avia_trips AS
    (SELECT TRIP.* FROM Company
        INNER JOIN Trip ON Trip.company = Company.id WHERE name = "Don_avia")
SELECT * FROM Don_avia_trips UNION SELECT * FROM  Aeroflot_trips;

id	company	plane	town_from	town_to	time_out	time_in
1181	1	TU-134	Rostov	Moscow	1900-01-01T06:12:00. 000Z	1900-01-01T08:01:00.000Z
1182	1	TU-134	Moscow	Rostov	1900-01-01T12:35:00.000Z	1900-01-01T14:30:00.000Z
1187	1	TU-134	Rostov	Moscow	1900-01-01T15:42:00.000Z	1900-01-01T17:39:00.000Z
1188	1	TU-134	Moscow	Rostov	1900-01-01T22:50:00.000Z	1900-01-02T00:48:00.000Z
1195	1	TU-154	Rostov	Moscow	1900-01-01T23:30:00.000Z	1900-01-02T01:11:00.000Z
1196	1	TU-154	Moscow	Rostov	1900-01-01T04:00:00.000Z	1900-01-01T05:45:00.000Z
1145	2	IL-86	Moscow	Rostov	1900-01-01T09:35:00.000Z	1900-01-01T11:23:00.000Z
1146	2	IL-86	Rostov	Moscow	1900-01-01T17:55:00.000Z	1900-01-01T20:01:00. 000Z

Обобщённые табличные выражения были добавлены в SQL для упрощения сложных длинных запросов, особенно с множественными подзапросами. Их главная задача – улучшение читабельности, простоты написания запросов и их дальнейшей поддержки. Это происходит за счёт сокрытия больших и сложных запросов в созданные именованные выражения, которые потом используются в основном запросе.

Конструкция WITH в T-SQL или обобщенное табличное выражение (ОТВ) | Info-Comp.ru

Всем привет! Тема сегодняшнего материала будет посвящена обобщенным табличным выражениям языка T-SQL, мы с Вами узнаем, что это такое, а также рассмотрим примеры написания запросов с использованием этих самых обобщённых табличных выражений.

Для начала, конечно же, давайте поговорим о том, что вообще из себя представляют обобщенные табличные выражения, какие они бывают, рассмотрим синтаксис, для чего их можно использовать и в заключение разберем несколько примеров.

Заметка! Профессиональный видеокурс по T-SQL для начинающих.

Содержание

Что такое обобщенное табличное выражение?
Какие бывают обобщенные табличные выражения?
Пример простого обобщенного табличного выражения
Пример рекурсивного обобщенного табличного выражения

Что такое обобщенное табличное выражение?

Common Table Expression (CTE) или обобщенное табличное выражение (OTB) – это временные результирующие наборы (т.е. результаты выполнения SQL запроса), которые не сохраняются в базе данных в виде объектов, но к ним можно обращаться.

Главной особенностью обобщенных табличных выражений является то, что с помощью них можно писать рекурсивные запросы, но об этом чуть ниже, а сейчас давайте поговорим о том, в каких случаях нам могут пригодиться OTB, в общем, для чего они предназначены:

Основной целью OTB является написание рекурсивных запросов, можно сказать для этого они, и были созданы;

OTB можно использовать также и для замены представлений (VIEW), например, в тех случаях, когда нет необходимости сохранять в базе SQL запрос представления, т. е. его определение;
Обобщенные табличные выражения повышают читаемость кода путем разделения запроса на логические блоки, и тем самым упрощают работу со сложными запросами;
Также OTB предназначены и для многократных ссылок на результирующий набор из одной и той же SQL инструкции.

Обобщенное табличное выражение определяется с помощью конструкции WITH, и определить его можно как в обычных запросах, так и в функциях, хранимых процедурах, триггерах и представлениях.

Синтаксис:

 
   WITH common_table_expression_name [ ( column_name [ ,...n ] ) ]
    AS
     ( CTE_query_definition )

Где,

common_table_expression_name – это псевдоним или можно сказать идентификатор обобщенного табличного выражения. Обращаться к OTB мы будем, как раз используя этот псевдоним;
column_name – имя столбца, который будет определен в обобщенном табличном выражении. Использование повторяющихся имен нельзя, а также их количество должно совпадать с количеством столбцов возвращаемых запросом CTE_query_definition. Указывать имена столбцов необязательно, но только в том случае, если всем столбцам в запросе CTE_query_definition присвоены уникальные псевдонимы;
CTE_query_definition — запрос SELECT, к результирующему набору которого, мы и будем обращаться через обобщенное табличное выражение, т.е. common_table_expression_name.

После обобщенного табличного выражения, т.е. сразу за ним должен идти одиночный запрос

SELECT, INSERT, UPDATE, MERGE или DELETE.

Какие бывают обобщенные табличные выражения?

Они бывают простые и рекурсивные.

Простые не включают ссылки на самого себя, а рекурсивные соответственно включают.

Рекурсивные ОТВ используются для возвращения иерархических данных, например, классика жанра это отображение сотрудников в структуре организации (чуть ниже мы это рассмотрим).

Примечание! Все примеры ниже будут рассмотрены в MS SQL Server 2008 R2.

В качестве тестовых данных давайте использовать таблицу TestTable, которая будет содержать идентификатор сотрудника, его должность и идентификатор его начальника.

Где,

UserID — идентификатор сотрудника;
Post — должность;
ManagerID — идентификатор начальника.

Как видите, у директора отсутствует ManagerID, так как у него нет начальника. А теперь переходим к примерам.

Заметка! Обзор Azure Data Studio. Что это за инструмент и для чего он нужен.

Пример простого обобщенного табличного выражения

Для примера давайте просто выведем все содержимое таблицы TestTable с использованием обобщенного табличного выражения

 
   WITH TestCTE (UserID, Post, ManagerID)
        AS
        (
                SELECT UserID, Post, ManagerID FROM TestTable 
        )
   SELECT * FROM TestCTE

Где TestCTE это и есть псевдоним результирующего набора, к которому мы и обращаемся.

В данном случае мы могли и не перечислять имена столбцов, так как они у нас уникальны. Можно было просто написать вот так:

 
   WITH TestCTE
        AS
        (
                SELECT UserID, Post, ManagerID FROM TestTable 
        )
   SELECT * FROM TestCTE

Пример рекурсивного обобщенного табличного выражения

Теперь допустим, что нам необходимо вывести иерархический список сотрудников, т.е. мы хотим видеть, на каком уровне работает тот или иной сотрудник. Для этого пишем рекурсивный запрос:

 
   WITH TestCTE(UserID, Post, ManagerID, LevelUser)
   AS
   (    
        -- Находим якорь рекурсии
        SELECT UserID, Post, ManagerID, 0 AS LevelUser 
        FROM TestTable WHERE ManagerID IS NULL
        UNION ALL
        --Делаем объединение с TestCTE (хотя мы его еще не дописали)
        SELECT t1.UserID, t1.Post, t1.ManagerID, t2.LevelUser + 1 
        FROM TestTable t1 
        JOIN TestCTE t2 ON t1. ManagerID=t2.UserID
   )
   SELECT * FROM TestCTE ORDER BY LevelUser

В итоге, если мы захотим, мы можем легко получить список сотрудников определенного уровня, например, нам нужны только начальники отделов, для этого мы просто в указанный выше запрос добавим условие WHERE LevelUser = 1

При написании рекурсивного ОТВ нужно быть внимательным, так как неправильное его составление может привести к бесконечному циклу. Поэтому для этих целей есть опция MAXRECURSION, которая может ограничивать количество уровней рекурсии. Давайте представим, что мы не уверены, что написали рекурсивное обобщенное выражение правильно и для отладки напишем инструкцию OPTION (MAXRECURSION 5), т.е. отобразим только 5 уровня рекурсии, и если уровней будет больше, SQL инструкция будет прервана.

 
   WITH TestCTE(UserID, Post, ManagerID, LevelUser)
   AS
   (    
        --Находим якорь рекурсии
        SELECT UserID, Post, ManagerID, 0 AS LevelUser 
        FROM TestTable WHERE ManagerID IS NULL
        UNION ALL
        --Делаем объединение с TestCTE (хотя мы его еще не дописали)
        SELECT t1. UserID, t1.Post, t1.ManagerID, t2.LevelUser + 1 
        FROM TestTable t1 
        JOIN TestCTE t2 ON t1.ManagerID=t2.UserID
   )
   SELECT * FROM TestCTE ORDER BY LevelUser
   OPTION (MAXRECURSION 5)

Запрос у нас отработал, что говорит о том, что мы написали его правильно и соответственно OPTION (MAXRECURSION 5) можно смело убрать.

Заметка! Для комплексного изучения языка SQL и T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, в которых используется последовательная методика обучения специально для начинающих.

На этом у меня все, удачи!

Можем ли мы иметь несколько «WITH AS» в одном sql — Oracle SQL

спросил 9 лет, 4 месяца назад

Изменено 5 лет, 4 месяца назад

Просмотрено 278 тысяч раз

У меня был очень простой вопрос: разрешает ли oracle несколько «WITH AS» в одном выражении sql.

Пример:

 С abc AS(выбрать ......)
WITH XYZ AS(select ....) /* Здесь несколько раз используется "abc"*/
Выберите .... /*используя XYZ несколько раз*/

Я могу заставить запрос работать, повторяя один и тот же запрос несколько раз, но не хочу этого делать, и использую «С КАК». Вроде простое требование, но оракул мне не позволяет:

ORA-00928: отсутствует ключевое слово SELECT

sql
оракул

Вы можете сделать это как:

 С абв КАК(выбрать
             ОТ ...)
, XYZ КАК(выберите
         From abc ....) /*В этом случае "abc" используется несколько раз*/
  Выбирать
  Из XYZ.... /*используя abc, XYZ несколько раз*/

правильный синтаксис —

 с t1
как
(выберите * на вкладке 1
где условия...
),
т2
как
(выберите * на вкладке 2
где условия...
(вы также можете получить доступ к столбцам t1 здесь)
)
выберите * из t1, t2
где t1. col1=t2.col2;

Да, вы можете…

 WITH SET1 AS (SELECT SYSDATE FROM DUAL), -- SET1 инициализирован
     SET2 AS (SELECT * FROM SET1) -- Доступ к SET1
ВЫБЕРИТЕ * ИЗ НАБОРА 2; -- Спроектирован SET2
29.10.2013 10:43:26

Следуйте порядку, в котором он должен быть инициализирован в Common Table Expressions

Адитья или другие, можете ли вы соединить или сопоставить t2 с t1 в вашем примере, т.е. перевести в мой код,

 с t1 как (выберите * из AA, где ИМЯ, например, «Кермит»),
     t2 as (выберите * из BB B, присоедините t1 к t1.FIELD1 = B.FIELD1)

Мне не ясно, поддерживается ли соединение только WHERE или какой подход к объединению поддерживается во втором объекте WITH. В некоторых примерах WHERE A=B находится внизу в теле select «ниже» предложений WITH.

Ошибка, которую я получаю после этих объявлений WITH, заключается в том, что идентификаторы (имена полей) в B не распознаются в теле остальной части SQL. Таким образом, синтаксис WITH работает нормально, но не может получить доступ к результатам t2.

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя электронную почту и пароль

Опубликовать как гость

Электронная почта

Требуется, но никогда не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

SQL-разработчик | Oracle

Нажмите, чтобы ознакомиться с нашей Политикой доступности
Перейти к содержимому

Штирлиц — Сеть печатных салонов в Перми

With as sql: WITH обобщенное_табличное_выражение (Transact-SQL) — SQL Server

Обобщённое табличное выражение, оператор WITH

Конструкция WITH в T-SQL или обобщенное табличное выражение (ОТВ) | Info-Comp.ru

Что такое обобщенное табличное выражение?

Какие бывают обобщенные табличные выражения?

Пример простого обобщенного табличного выражения

Пример рекурсивного обобщенного табличного выражения

Можем ли мы иметь несколько «WITH AS» в одном sql — Oracle SQL

Зарегистрируйтесь или войдите в систему

Опубликовать как гость

Опубликовать как гость

SQL-разработчик | Oracle

Интерфейсы приложений SQL Developer

Oracle SQL Developer

Oracle Database Actions

Oracle SQLcl

Узнайте больше

Возможности SQL Developer

Основные функции

Рабочий стол

Командная строка

Браузер

Добавить комментарий Отменить ответ