With as sql: SQL WITH: Organize Complex Queries

Содержание

Конструкция 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, в которых используется последовательная методика обучения специально для начинающих.

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

SQL Manager for SQL server

Ускорьте процесс разработки базы данных SQL Server!

Проектируйте, анализируйте и обслуживайте базы данных, создавайте сложные SQL-запросы, управляйте правами пользователей базы данных и работайте с данными быстро и удобно.

Cкачать бесплатную пробную версию

Простое управление объектам БД и сервера

Удобное создание объектов и настройка параметров в визуальных редакторах. Быстрый поиск любого объекта в дереве, гибкие настройки отображения, группировка любимых объектов сделают работу с сервером максимально эффективной.

Автодополнение и форматирование кода в редакторе SQL

Полезные функции автодополнения кода и подсказки параметров процедур сэкономят время при написании запросов, а форматирование кода и группировка по блокам улучшат читаемость и навигацию по коду. Также вы всегда можете детально настроить интерфейс редактора под ваши требования.

Графическое построение сложных запросов

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

Экспорт данных в самые распространенные форматы

Экспорт данных из таблиц, представлений и результатов запросов в один из 20 наиболее распространенных форматов файлов: MS Excel, MS Word, MS Access, HTML, PDF, TXT, CSV, DBF, XML, ODF и другие. Удобный мастер поможет детально настроить параметры выходного файла.

Импорт данных в различных режимах вставки

Удобный мастер поможет загрузить данные из 10 популярных форматов файлов: MS Excel, MS Word, XML, MS Access, PDF, TXT, CSV и других. Вы можете задать настройки для обработки исходных данных, изменить соответствие столбцов, выбрать режим вставки или обновления и настроить формат вставляемых данных. Для выполнения однотипных операций используйте сохраненные шаблоны.

Различные режимы просмотра данных

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

Выполнение задач по обслуживанию базы данных

Создавайте резервные копии БД и управляйте индексами при помощи удобных пошаговых мастеров. Используйте встроенные инструменты для отслеживания серверных процессов, анализа и оптимизации БД.

Выгрузка файлов БД для систем управления версиями

Выгружайте определения объектов БД во внешние файлы для использования в системе контроля версий. Гибкие настройки имен файлов и команд помогут настроить контроль версий именно для вашей системы.

Отчеты и различные способы документирования БД

Инструменты для документирования БД справятся даже с большими базами. Всего пара шагов и все свойства, и описания объектов можно выгрузить в удобный для чтения или отображения формат: HTML, PDF, TXT, CSV, Excel и другие. Встроенный дизайнер отчетов поможет создать отчет на основе запросов любой сложности и детально настроить итоговый дизайн.

Мощный отладчик для процедур, функций и скриптов

Встроенный отладчик T-SQL – незаменимый инструмент разработчика баз данных. Он позволяет отслеживать выполнение процедур, функций и скрипта в реальном времени, а также использовать точки останова, стеки вызовов и переменные для быстрого выявления ошибок.

Сравнение и синхронизация баз данных

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

При покупке Вы получите также:

БЕСПЛАТНАЯ подписка на 1 год Сопровождения!
БЕСПЛАТНЫЕ Минорные и Мажорные обновления в период действия Обслуживания!
БЕСПЛАТНАЯ неограниченная техническая поддержка в период действия Сопровождения!
Разумные расценки на продление Сопровождения – всего от 35% в год!

Скидки при покупке двух и более лицензий одного продукта
Скидки на покупку сопутствующих продуктов
Гарантия возврата денег в течение 30 дней

SQL Manager for SQL server

Начните работу с SQL Manager for SQL server

Скачайте полнофункциональную 30-дневную бесплатную пробную версию и уже сегодня начните экономить время при управлении базами данных.

Скачать бесплатную пробную версию

Есть вопросы?

Если вам требуется какая-либо помощь, если у вас есть вопросы по нашим продуктам или по вариантам приобретения, просто свяжитесь с нами.

Система Support Ticket
[email protected]
Общие вопросы

SQL Manager является частью SQL Management Studio

Выполняйте еще больше задач по разработке, администрированию и обслуживанию вашей базы данных, экономя до 55%!

Узнать подробнее

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

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

Пример:

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

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

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

sql
оракул

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

 С abc AS (выберите
             ИЗ ...)
, 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

Aditya или другие, можете ли вы соединить или сопоставить 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 Server WITH оператор –

спросил 7 лет, 8 месяцев назад

Изменено 7 лет, 8 месяцев назад

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

Моя цель — выбрать результат из одного CTE и вставить в другую таблицу с другим CTE в той же процедуре. Как это сделать?

Моя ошибка…

неверное имя объекта xy.

Мой запрос

 С ds
(
    Выберите а, б, в
    из теста1
),
ху
(
    выберите д, е, е
    из теста2
    где (использует условия из ds)
)
Выбирать *
из ds (набор результатов ds, я экспортирую его в csv)
Вставить в AuditTest
(
  Выберите * из xy
)

sql
sql-server-2008
хранимые процедуры

CTE подходит только для одного запроса, но похоже, что вы можете использовать CTE в каждом запросе:

 WITH ds AS
(
  Выберите a, b, c из test1
)
Выберите * из ds (результирующий набор ds, я экспортирую его в csv)
С ХУ КАК
(
 выберите d,e,f из теста2, где (использует условия из теста1)
)
Вставить в AuditTest
(
  Выберите * из xy
)

На самом деле вы можете выполнять как вставку, так и вывод результатов, используя предложение OUTPUT для возврата вставленных строк.

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

With as sql: SQL WITH: Organize Complex Queries

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

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

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

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

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

SQL Manager for SQL server

Ускорьте процесс разработки базы данных SQL Server!

Простое управление объектам БД и сервера

Автодополнение и форматирование кода в редакторе SQL

Графическое построение сложных запросов

Экспорт данных в самые распространенные форматы

Импорт данных в различных режимах вставки

Различные режимы просмотра данных

Выполнение задач по обслуживанию базы данных

Выгрузка файлов БД для систем управления версиями

Отчеты и различные способы документирования БД

Мощный отладчик для процедур, функций и скриптов

Сравнение и синхронизация баз данных

При покупке Вы получите также:

SQL Manager for SQL server

Начните работу с SQL Manager for SQL server

Есть вопросы?

SQL Manager является частью SQL Management Studio

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

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

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

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

SQL Server WITH оператор –

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