В качестве табличного подзапроса может использоваться любой запрос SELECT, причем при использовании рекурсии <табличный подзапрос> обязательно содержит в себе объединение результатов нескольких запросов.

Рассмотрим пример использования нерекурсивной производной таблицы, определенной в предложении WITH. Допустим, необходимо вывести по каждому абоненту номер его лицевого счета, ФИО и общие суммы оплат за 2000 и 2001 годы. Запрос будет выглядеть следующим образом: WITH Year Abon Pay AS (SELECT PayYear, AccountCD, SUM(PaySum) AS Total Sum FROM PaySumma GROUP BY PayYear, AccountCD) SELECT A.AccountCD, 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.AccountCD = God 2000.AccountCD

AND God 2000. PayYear = 2000

LEFT JOIN Year Abon Pay God 2001 ON A.AccountCD = God 2001.AccountCD AND God 2001. PayYear = 2001;.

Результат выполнения запроса представлен на рис. 3.78.

Рис. 3.78. Результат выполнения запроса с нерекурсивной производной

таблицей

использования нерекурсивных

предложении WITH, могут

Следует отметить следующие особенности подзапросов в предложении WITH:

- производные таблицы, определенные в ссылаться друг на друга;

- ссылка на производную таблицу (имя производной таблицы) может использоваться в любой части основного запроса (в предложениях SELECT, FROM и т.д.);

- одна и та же производная таблица может использоваться несколько раз в основном запросе под разными псевдонимами;

- в многострочных запросах на обновление (INSERT, UPDATE и DELETE) подзапросы могут включать предложение WITH, определяющее производные таблицы;

- производные таблицы могут использоваться в процедурном языке. Рекурсивные производные таблицы в предложении WITH позволяют

создавать рекурсивные запросы.

Рассмотрим пример использования рекурсивной производной таблицы, определенной в предложении WITH. Предположим, что в таблицу Abonent был добавлен еще один столбец HEADACCOUNT (это можно сделать с помощью запроса ALTER TABLE, использование которого будет рассмотрено позднее). В этом столбце для каждого абонента указан лицевой счет управляющего по дому, в котором проживает абонент. Если абонент сам является управляющим, то в столбце HEADACCOUNT указывается NULL. Допустим, в таблице Abonent имеются данные, представленные на рис. 3.79.

Рис. 3.79. Данные таблицы Abonent с дополнительным столбцом

Допустим, необходимо вывести ту же информацию, что и в предыдущем примере, но вывод представить в виде дерева: перед ФИО каждого управляющего по дому поставить +, а ниже вывести всех абонентов, проживающих в данном доме, с указанием перед их ФИО 4 символов пробела. Запрос будет выглядеть следующим образом:

WITH RECURSIVE

Year Abon Pay AS

(SELECT PayYear, AccountCD, SUM(PaySum) AS Total Sum FROM PaySumma GROUP BY PayYear, AccountCD), Abonent Tree AS

(SELECT AccountCD, Head AccountCD, Fio,

CAST (+ AS VARCHAR(4)) AS Indent

FROM Abonent

WHERE Head AccountCD IS NULL

UNION ALL

SELECT A.AccountCD, A.Head AccountCD, A.Fio,

CAST ( AS VARCHAR(4)) AS Indent FROM Abonent A JOIN Abonent Tree Tr

ON A. Head AccountCD = Tr.AccountCD) SELECT A.AccountCD, 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.AccountCD = God 2000.AccountCD AND God 2000. PayYear = 2000

LEFT JOIN Year Abon Pay God 2001 ON A.AccountCD = God 2001.AccountCD

AND God 2001. PayYear = 2001;.

Результат выполнения запроса представлен на рис. 3.80.

Рис. 3.80. Результат выполнения запроса с рекурсией

Необходимо указать следующие особенности использования рекурсивных производных таблиц:

- рекурсивная производная таблица имеет ссылку на саму себя;

- рекурсивная производная таблица - это объединение в одном запросе (UNION) рекурсивных и нерекурсивных частей;

- должна присутствовать, по крайней мере, одна нерекурсивная часть;

- нерекурсивные части располагаются в начале запроса, содержащего объединение;

- рекурсивные части отделяются от нерекурсивных и от самих себя с помощью конструкции UNION ALL;

- использование предложений DISTINCT,GROUP BY, HAVING, а также

использование агрегатных функций не допускается в рекурсивных частях запроса;