Commontableexpressions (cte). Их назначение и синтаксис описания. Ограничения при описании cte. Рекурсивные cteи ограничения при их описании. Примеры

Секция WITH позволяет описывать common table expressions (CTE), которые по сути являются поименованными запросами SELECT (аналогично представлениям), к которым можно обращаться в последующей команде DML. Использование CTE допустимо, если уровень совместимости для БД выставлен в значение 90. Следует отметить, что CTE может использовать рекурсию, т.е. команду SELECT, которая обращается к самому CTE. В общем виде секция WITH выглядит следующим образом:

 

WITH имя_CTE [(перечень_имен_столбцов)] AS (перечень_запросов_SELECT)

Таким образом, в этой секции можно объединять несколько CTE. Перечень_запросов_SELECT является набором команд SELECT, объединенных каким-либо из операторов EXCEPT, INTERCEPT или UNION [ALL] (в случае нерекурсивного CTE). В случае рекурсивного CTE Перечень_запросов_SELECT выглядит следующим образом:

Следует отметить, что количество столбцов объединяемых запросов должно быть одинаково, а их типы данных – совместимы.

Создание CTE подчиняется следующим правилам:

1. CTE может ссылаться само на себя или на ранее описанные CTE, но не может ссылаться на CTE, которые еще не описаны.

2. Нельзя использовать секцию WITH в CTE.

3. В запросах SELECT, которые образуют CTE, не могут быть использованы следующие секции:

· COMPUTE [BY].

· ORDER BY (кроме случая, когда используется опция TOP).

· INTO.

· OPTION с указанием настроек поведения запроса.

· FOR XML.

· FOR BROWSE.

4. Если команда, содержащая секцию WITH идет не 1-ой в пакете команд, то предшествующая ей команда должна завершаться символом;.

Для рекурсивных CTE существует ряд дополнительных правил и ограничений:

1. Секция FROM рекурсивных запросов должна ссылаться на имя CTE, к которому они принадлежат, только 1 раз.

2. Следующие секции, операторы и конструкции не могут использоваться в запросах SELECT, которые образуют CTE:

· Опции DISTINCT и TOP.

· GROUP BY.

· HAVING.

· Операторы {LEFT | RIGHT | FULL} [OUTER] JOIN (INNER JOIN и CROSS JOIN- допустимы).

· Вложенные запросы SELECT.

· Скалярные агрегационные функции.

3. Все столбцы, возвращаемые рекурсивным CTE, имеют свойство NULL.

4. Представление, содержащее рекурсивные CTE, не может быть использовано для изменения данных в связанных с ним таблицах.

5. Если рекурсивный CTE содержится в команде SELECT, которая описывает курсор, то тип курсора может быть только STATIC или FAST_FORWARD. В противном случае - тип курсора будет приведен к типу STATIC.

6. В рекурсивном запросе должно содержаться условия остановки, прерывающее его выполнение. В противном случае получается бесконечный цикл выполнения рекурсивного запроса SELECT, что приводит к ошибке.

Пример

with cte1as (select subject, left(group_name, 2) [group_name] from GROUPS, SUBJECT)

select distinct * from cte1 order by kurs, group_name

Команда извлечения данных из таблицы. Секция From, синтаксис, подробное описание. Использование горизонтальных и вертикальных объединений.Примеры.

WITH common_table_expression

SELECT выражение

INTO имя_таблицы

FROM имя_источника_данных

WHERE условие поиска

GROUP BY перечень_упорядочиваемых_колонок

HAVING условие_поиска

ORDER BY

COMPUTE выражение

FOR выражение

OPTION параметры_запроса

Секция FROM

FROM выражение [IN внешняяБазаДанных ]

Выражение, определяющее одну или несколько таблиц, откуда извлекаются данные. Это выражение может быть именем отдельной таблицы, именем сохраненного запроса или результатом операции JOIN:

· INNER JOIN - для каждой сроки таблицы из правой части оператора объединения по очереди выбирается строка из таблицы левой части оператора объединения, удовлетворяющая условию.

· LEFT OUTER JOIN - для каждой сроки таблицы из левой части оператора объединения по очереди выбираются строка из таблицы правой части оператора объединения, удовлетворяющая условию. Если такой строки нет, то берутся значения NULL для всех выбираемых столбцов «правой» таблицы.

· RIGHT OUTER JOIN - для каждой сроки таблицы из правой части оператора объединения по очереди выбирается строка из таблицы левой части оператора объединения, удовлетворяющая условию. Если такой строки нет, то берутся значения NULL для всех выбираемых столбцов «левой» таблицы.

· FULL OUTER JOIN - для каждой сроки таблицы из левой части оператора объединения по очереди выбирается строка из таблицы правой части оператора 3объединения, удовлетворяющая условию. Если такой строки нет, то берутся значения NULL для всех выбираемых столбцов «правой» таблицы. Далее, берутся строки «правой» таблицы, не удовлетворяющие условию, и дополняются значениями NULL для выбираемых столбцов «левой» таблицы.

CROSS JOIN – выполняется следующим образом: для каждой сроки таблицы из правой части оператора объединения по очереди выбирается строка из таблицы левой части оператора объединения. Эквивалентно перечислению источников данных через запятую.

o объединение с помощью оператора {CROSS | OUTER} APPLY.

· CROSS – результатом является все пары строк полученных в результате объединения множества строк, полученных функцией, с множеством строк «левой» таблицы.

· OUTER – результатом является все пары строк полученных в результате объединения множества строк, полученных функцией, с множеством строк «левой» таблицы.

2. Группировка (с «разворотом») строк таблицы (оператор PIVOT). Осуществляет группировку по столбцу, имя которого указывается после ключевого слова FOR. Затем, для каждой группы, значения которых должны быть указаны в select_list и в перечне имен столбцов после ключевого слова IN генерируется значение, которые возвращает агрегационная функция.

Обратный «разворот» столбцов таблицы (оператор UNPIVOT). Выполняет, по сути, действие обратное оператору PIVOT (за исключением получения наборов строк, которые образовывали каждую группу). Его использование допустимо, если уровень совместимости для БД выставлен в значение 90. Он выполняет «разворот» таблицы, так что столбцы становятся строками, а строки – столбцами (подобно транспонированию матрицы). Таким образом, значения столбца, имя которого указывается после оператора UNPIVOT формируют различные значения для групп.