Простые подзапросы в предложении WITH
Производные таблицы, возвращаемые табличным подзапросом, могут быть определены в предложении WITH, которое записывается перед основным запросом SELECT. Синтаксис использования предложения WITH следующий: WITH [RECURSIVE] имя_производной_таблицы1 [(<список_столбцов>)] AS (<табличный_подзапрос> ) [, имя_производной_таблицы2 [(<список_столбцов>)] AS (<табличный_подзапрос>)]. . .
необходимо вывести по каждому абоненту номер его лицевого счета, ФИО и общие суммы оплат за 2000 и 2001 годы. WITH Year_Abon_Pay AS (SELECT Pay. Year, Account. CD, SUM(Pay. Sum) AS Total_Sum FROM Pay. Summa GROUP BY Pay. Year, Account. CD) SELECT A. Account. CD, A. Fio, God_2000. Total_Sum AS Total_2000, God_2001. Total_Sum AS Total_2001 FROM Abonent A LEFT JOIN Year_Abon_Pay God_2000 ON A. Account. CD = God_2000. Account. CD AND God_2000. Pay. Year = 2000 LEFT JOIN Year_Abon_Pay God_2001 ON A. Account. CD = God_2001. Account. CD AND God_2001. Pay. Year = 2001
ACCOUNTCD FIO TOTAL_2000 TOTAL_2001 005488 АКСЕНОВ С. А. 108, 13 58, 70 115705 МИЩЕНКО Е. В. 290, 00 346, 50 015527 КОНЮХОВ В. С. <null> 443690 ТУЛУПОВА М. И. <null> 136159 СВИРИНА З. А. <null> 443069 СТАРОДУБЦЕВ Е. В. <null> 156, 82 136160 ШМАКОВ С. В. 18, 30 20, 00 126112 МАРКОВА В. П. 15, 30 25, 30 136169 ДЕНИСОВА Е. К. <null> 107, 02 080613 ЛУКАШИНА Р. М. 35, 46 56, 00 080047 ШУБИНА Т. П. <null> 112, 56 080270 ТИМОШКИНА 58, 10 106, 10
- производные таблицы, определенные в предложении WITH, могут ссылаться друг на друга; - ссылка на производную таблицу (имя_производной_таблицы) может использоваться в любой части основного запроса (в предложениях SELECT, FROM и т. д. ); - одна и та же производная таблица может использоваться несколько раз в основном запросе под разными псевдонимами; - в многострочных запросах на обновление (INSERT, UPDATE и DELETE) подзапросы могут включать предложение WITH, определяющее производные таблицы; - производные таблицы могут использоваться в процедурном языке. Рекурсивные производные таблицы в предложении WITH позволяют создавать рекурсивные запросы.
Предположим, что в таблицу Abonent был добавлен еще один столбец HEAD_ACCOUNT (это можно сделать с помощью запроса ALTER TABLE). В этом столбце для каждого абонента указан лицевой счет управляющего по дому, в котором проживает абонент. Если абонент сам является управляющим, то в столбце HEAD_ACCOUNT указывается NULL.
Необходимо вывести ту же информацию, что и в предыдущем примере, но вывод представить в виде дерева: перед ФИО каждого управляющего по дому поставить '+', а ниже вывести всех абонентов, проживающих в данном доме, с указанием перед их ФИО 4 символов пробела.
WITH RECURSIVE Year_Abon_Pay AS (SELECT Pay. Year, Account. CD, SUM(Pay. Sum) AS Total_Sum FROM Pay. Summa GROUP BY Pay. Year, Account. CD), Abonent_Tree AS (SELECT Account. CD, Head_Account. CD, Fio, CAST ('+' AS VARCHAR(4)) AS Indent FROM Abonent WHERE Head_Account. CD IS NULL UNION ALL SELECT A. Account. CD, A. Head_Account. CD, A. Fio, CAST (' ' AS VARCHAR(4)) AS Indent FROM Abonent A JOIN Abonent_Tree Tr ON A. Head_Account. CD = Tr. Account. CD) SELECT A. Account. CD, A. Indent || A. Fio AS Fio, God_2000. Total_Sum AS Total_2000, God_2001. Total_Sum AS Total_2001 FROM Abonent_Tree A LEFT JOIN Year_Abon_Pay God_2000 ON A. Account. CD = God_2000. Account. CD AND God_2000. Pay. Year = 2000 LEFT JOIN Year_Abon_Pay God_2001 ON A. Account. CD = God_2001. Account. CD AND God_2001. Pay. Year = 2001
- рекурсивная производная таблица имеет ссылку на саму себя; - рекурсивная производная таблица – это объединение в одном запросе (UNION) рекурсивных и нерекурсивных частей; - должна присутствовать, по крайней мере, одна нерекурсивная часть; - нерекурсивные части располагаются в начале запроса, содержащего объединение; - рекурсивные части отделяются от нерекурсивных и от самих себя с помощью конструкции UNION ALL; - использование предложений DISTINCT, GROUP BY, HAVING, а также использование агрегатных функций не допускается в рекурсивных частях запроса; - рекурсивная часть может иметь только одну ссылку на саму себя и только в предложении FROM; - рекурсивная ссылка не может участвовать во внешнем соединении таблиц.
Запросы с рекурсивными производными таблицами выполняются следующим образом: выбирается первая строка из нерекурсивной части запроса; для данной строки выполняется каждая рекурсивная часть с учетом текущих значений строки как параметров; если текущая рекурсивная часть не возвращает строк, происходит возврат на шаг назад и получение следующей строки из результирующего набора нерекурсивной части запроса
Как следует из приведенного синтаксиса, в предложении WITH может быть определено несколько подзапросов. Сами предложения WITH не могут быть вложенными. Подзапросы в предложении WITH могут быть нерекурсивными и рекурсивными. Если используется рекурсивный подзапрос, то в предложении WITH указывается ключевое слово RECURSIVE. В качестве табличного подзапроса может использоваться любой запрос SELECT, причем при использовании рекурсии <табличный_подзапрос> обязательно содержит в себе объединение результатов нескольких запросов.
Скачать презентацию Простые подзапросы в предложении WITH Производные таблицы
Простые подзапросы в предложении WITH.pptx