Объединит ли база данных основной и вложенный запросы? Ответ зависит от того, с какой точки зрения подойти: реляционной теории, стандарта SQL или конкретной реализации SQL отдельным производителем. Больше информации можно найти по адресу http: gennick.com/madness.html.

В данном случае проблема решена, по крайней мере, в Oracle, благодаря введению ROWNUM в список оператора SELECT внутреннего запроса. ROWNUM - это функция, возвращающая последовательно возрастающее число для каждой строки, возвращаемой запросом. Здесь важны последние слова. Последовательно возрастающее число, получившее название номер строки, не может вычисляться вне контекста возвращения строки запросом. Таким образом, Oracle вынужден материализовать результат подзапроса, что означает, что Oracle вынужден выполнить сначала подзапрос, чтобы возвратить строки из этого подзапроса для правильного присвоения номеров строк. Итак, использование в запросе ROWNUM - один из механизмов заставить Oracle полностью выполнить подзапрос перед выполнением основного запроса (т. е. слияние запросов не допускается). Если требуется установить принудительный порядок выполнения подзапроса в какой-то другой СУБД, не Oracle, проверьте, не поддерживает ли эта база данных функции, аналогичной ROWNUM Oracle.

Проверка существования значения в группе Задача

Требуется создать для строки логический флаг, указывающий на наличие в любой строке ее группы определенного значения. Возьмем для примера студента, который сдает определенное количество экзаменов за определенный промежуток времени. Студент будет сдавать три экзамена за три месяца. Если он сдает один из этих экзаменов, требование считается удовлетворенным, и для выражения этого факта должен быть возвращен флаг. Если студент не сдает ни одного из трех экзаменов за трехмесячный период, должен быть возвращен дополнительный флаг, указывающий и на этот факт. Рассмотрим следующий пример (для создания строк используется синтаксис Oracle; для DB2 и SQL Server понадобятся минимальные изменения, поскольку обе СУБД поддерживают оконные функции):

create view V as

select 1 student id,

1 test id,

2 grade id,

1 period id,

to dateC 02/01/2005,MM/DD/YYYY) test date, 0 pass fail

from dual union all

select 1, 2, 2, 1, to date(03/01/2005,MM/DD/YYYY), 1 from dual union all

select 1, 3, 2, 1, to date(04/01/2005,MM/DD/YYYY), 0 from dual union all

select 1, 4, 2, 2, to date(05/01/2005,MM/DD/YYYY), 0 from dual union all

select 1, 5, 2, 2, to date(06/01/2005,MM/DD/YYYY), 0 from dual union all

select 1, 6, 2, 2, to date(07/01/2005,MM/DD/YYYY), 0 from dual

select * from V

STUDENT ID TEST ID GRADE ID PERIOD ID TEST DATE PASS FAIL

1 1 2 1 01-FEB-2005 0

1 2 2 1 01-MAR-2005 1

1 3 2 1 01-APR-2005 0

1 4 2 2 01-MAY-2005 0

1 5 2 2 01-JUN-2005 0

1 6 2 2 01-JUL-2005 0

Рассмотрев приведенное выше результирующее множество, видим, что студент должен сдать шесть экзаменов за два трехмесячных периода. Студент сдал один экзамен (1 означает сдал , 0 - не сдал ), таким образом, требование удовлетворено для всего первого периода. Поскольку студент не сдал ни одного экзамена в течение второго периода (следующие три месяца), PASS FAIL равен 0 для всех трех экзаменов. Хотелось бы получить результирующее множество, из которого видно, сдал ли студент хотя бы один экзамен за данный период. В итоге должно быть представлено следующее результирующее множество:

STUDENT ID TEST ID GRADE ID PERIOD ID TEST DATE METREQ IN PROGRESS

1 1 2 1 01-FEB-2005 + 0

1 2 2 1 01-MAR-2005 + 0

1 3 2 1 01-APR-2005 + 0

1 4 2 2 01-MAY-2005 - 0

1 5 2 2 01-JUN-2005 - 0

1 6 2 2 01-JUL-2005 - 1

В столбце METREQ ( met requirement 1) представлены значения + и - , обозначающие, выполнил или нет студент требование о сдаче, по крайней мере, одного экзамена за период (три месяца) соответственно. Значение поля IN PROGRESS должно быть равным 0, если студент уже сдал какой-то экзамен за данный период. Если студент не сдал ни одного экзамента, в поле IN PROGRESS строки с датой последнего экзамена этого студента будет располагаться значение 1.

Решение

Немного усложняет эту задачу тот факт, что строки необходимо рассматривать не индивидуально, а в группе. Посмотрим на значения

Требование выполнено. - Примеч. перев.

PASS FAIL в разделе Задача . Если бы записи обрабатывались последовательно, строка за строкой, значение поля METREQ во всех строках, кроме TEST ID 2, было бы - , хотя это неправильно. Мы должны обеспечить групповую обработку строк. Оконная функция MAX OVER позволяет без труда определить, сдал ли студент хотя бы один экзамен в течение конкретного периода. После получения этой информации расстановка логических значений - просто вопрос применения выражений CASE:

1 select student id,

2 test id,

3 grade id,

4 period id,

5 test date,

6 decode( grp p f,1,lpad(+,6),lpad(-,6) ) metreq,

7 decode( grp p f,1,0,

8 decode( test date,last test,1,0 ) ) in progress

9 from (

10 select V.*,

11 max(pass fail)over(partition by

12 student id,grade id,period id) grp p f,

13 max(test date)over(partition by

14 student id,grade id,period id) last test

15 from V

16 ) x

Обсуждение

Ключ к решению - применение оконной функции MAX OVER и возвращение с ее помощью наибольшего значения PASS FAIL для каждой группы. Поскольку значениями PASS FAIL могут быть только 1 или 0, в случае, если студент сдал хотя бы один экзамен, MAX OVER возвращает 1 для всей группы. Как это получается, показано ниже:

select V.*,

max(pass fail)over(partition by

student id,grade id,period id) grp pass fail

from V

STUDENT ID TEST ID GRADE ID PERIOD ID TEST DATE PASS FAIL GRP PASS FAIL 1 1 2 1 01-FEB-2005 0 1

1 2 2 1 01-MAR-2005 1 1

1 3 2 1 01-APR-2005 0 1

1 4 2 2 01-MAY-2005 0 0

1 5 2 2 01-JUN-2005 0 0

1 6 2 2 01-JUL-2005 0 0

Из приведенного выше результирующего множества видно, что студент сдал, по крайней мере, один экзамен в течение первого периода. Таким образом, вся группа получает значение 1 или pass (сдал). Следующее требование - если студент не сдал ни одного экзамена за период,