2014-02-02
837
WITH
WITH
WITH
WITH
общее_табличное_выражение [,…]
запрос
Правила записи запроса были рассмотрены ранее.
Общее табличное выражение записывается следующим образом:
имя_таблицы [ ( список_колонок ) ] AS ( подзапрос )
Общее табличное выражение позволяет определить таблицу с именем имя_таблицы, которая может быть использована в конструкциях FROM и других следующего за табличным выражением основного запроса. В одной конструкции WITH можно определить несколько табличных выражений; в этом случае имя таблицы может быть указано в любом последующем табличном выражении.
Рассмотрим примеры.
1. В соответствии со структурами таблиц, приведенных в разделе 4.6 (задача Н4), необходимо для каждой торговой базы получить ее название, название и суммарное количество поставленных на базу товаров, а также суммарное количество всех поставленных на данную базу товаров.
С помощью общего табличного выражения данный запрос может быть реализован следующим образом:
ART(WId, ACode, Qty) AS
|
|
|
(
SELECT WId, ACode, SUM(Qty) FROM INVOICE GROUP BY WId, ACode
),
COM(WId, Qty) AS
(
SELECT WId, SUM(Qty) FROM INVOICE GROUP BY WId
)
SELECT WName, AName, Art.Qty AS SubTotal, Com.Qty AS GrandTotal
FROM WAREHOUSE W, ARTICLE A, ART, COM
WHERE W.WId = ART.WId AND A.ACode = ART.ACode AND W.WId = COM.WId
Или так:
ART(WId, ACode, Qty) AS
(
SELECT WId, ACode, SUM(Qty) FROM INVOICE GROUP BY WId, ACode
),
COM(WId, Qty) AS
(
SELECT WId, SUM(Qty) FROM ART GROUP BY WId
)
SELECT WName, AName, ART.Qty AS SubTotal, COM.Qty AS GrandTotal
FROM WAREHOUSE W, ARTICLE A, ART, COM
WHERE W.WId = ART.WId AND A.ACode = ART.ACode AND W.WId = COM.WId
2. Вернемся к задаче H1 из раздела 4.6: найти имена поставщиков, поставляющих максимальное суммарное количество товара. В рассмотренном ранее примере использовалось представление.
Использование табличного выражения в конструкции FROM не позволяет получить требуемый результат, так как к этому табличному выражению приходится ссылаться из подзапроса, используемого в конструкции WHERE основного запроса, что недопустимо:
SELECT SName
FROM Salor S,
(SELECT SId, SUM(Qty) FROM Invoice GROUP BY SId) AS V1(SId, Qty)
WHERE S.SId = V1.SId AND
V1.Qty = (SELECT MAX(Qty) FROM V1) –- здесь в подзапросе имя V1 не определено
Проблему решает общее табличное выражение: определяемая им таблица может быть использована и в подзапросе:
V1(SId, Qty) AS
(
SELECT SId, SUM(Qty) FROM INVOICE GROUP BY SId
)
SELECT SName
FROM SALOR S, V1
WHERE S.SId = V1.SId AND V1.Qty = (SELECT MAX(Qty) FROM V1)
Рекурсивное табличное выражение
Подзапросы, использованные в определении общего табличного выражения, могут ссылаться на это же табличное выражение; в этом случае общее табличное выражение определено как рекурсивное общее табличное выражение.
Рекурсивное общее табличное выражение состоит из двух запросов, объединенных операцией
UNION ALL. Первый запрос в выражении определяется как инициализирующий и не должен ссылаться на это же табличное выражение; второй запрос определяется как итерационный (повторяющийся) и содержит ссылку на это же табличное выражение:
|
|
|
WITH имя_таблицы ( список_колонок ) AS
(
инициализирующий_запрос
итерационный_запрос
)
основной_запрос
Инициализирующий запрос выполняется только один раз и определяет начальное состояние таблицы, ассоциированной с общим табличным выражением. Итерационный запрос выполняется многократно и использует строки из таблицы, ассоциированной с общим табличным выражением; результат запроса также записывается в таблицу и используется при следующей итерации выполнения итерационного запроса. Выполнение запроса заканчивается, когда будут обработаны все записи из таблицы, ассоциированной с общим табличным выражением.
Когда разрабатывается рекурсивное общее табличное выражение, следует помнить, что может быть получен бесконечный рекурсивный цикл. Необходимо тщательно проектировать рекурсивное общее табличное выражение, чтобы избежать бесконечного цикла.
Рекурсивные общие табличные выражения могут быть полезны при написании запросов с таблицами, представляющими отношения, для которых определены рекурсивные связи.
Рассмотрим следующий пример.
Пусть определено некоторое отношение КОМПОНЕНТ, для которого определена рекурсивная связь с этим же отношением: каждый компонент, представляя некоторый агрегат, состоит из нескольких (0 или более) компонентов; компонент, являясь частью, входит в несколько (0 или более) других компонентов.
Такое отношение может быть представлено таблицей PARTLIST, имеющей следующие колонки: Part для представления компонента – агрегата, Subpart для представления компонента – части и Quantity для указания того, в каком количестве компонент-часть включен в компонент-агрегат:
CREATE TABLE PARTLIST (
Part VARCHAR(8),
Subpart VARCHAR(8),
Quantity INTEGER
);
Предположим, что таблица PARTLIST имеет следующий вид:
| Part | Subpart | Quantity |
Пример 1. Написать запрос, позволяющий получить, какие компоненты необходимы для построения некоторого заданного компонента, например, компонента с номером ’01’. Полученный список должен включать все компоненты, входящие в состав заданного (подкомпоненты первого уровня); компоненты, входящие в состав подкомпонентов первого уровня, и т.д. При этом, если некоторый компонент используется несколько раз (как часть разных компонентов), он должен быть включен только один раз.
WITH RPL (Part, Subpart, Quantity) AS
(
SELECT ROOT.Part, ROOT.Subpart, ROOT.Quantity
FROM PARTLIST ROOT
WHERE ROOT.Part = '01'
