Расширенный поиск

По большому счету, вся эта книга посвящена поиску. Мы уже ознакомились со всеми необходимыми видами запросов, использующими объединения, предикаты WHERE и методики группировки для выявления и возвращения результатов. Однако некоторые типы операций поиска стоят особняком, поскольку представляют другой взгляд на поиск. Например, требуется выводить результирующее множество постранично. Одна половина этой задачи состоит в определении (поиске) всего множества записей, которые должны быть представлены. Другая - в осуществлении повторного поиска следующей страницы результатов для вывода на экран пользователя, когда он просматривает записи. На первый взгляд кажется, что разбиение результатов на страницы не является задачей поиска, но эту задачу можно рассматривать с этой точки зрения, и она может быть решена таким образом. О таких решениях и пойдет речь в данной главе.

Разбиение результирующего множества на страницы

Задача

Требуется разбить на страницы или обеспечить возможность прокрутки результирующего множества. Например, необходимо возвратить первые пять заработных плат из таблицы EMP, затем следующие пять и т. д. Наша цель - предоставить пользователю возможность просматривать по пять строк, переходя к следующей порции результатов по нажатию кнопки Далее .

Решение

Поскольку в SQL нет понятия первой, последней или следующей строки, необходимо каким-то образом упорядочить рассматриваемые стро-

ки. Только упорядочив их, мы сможем возвращать строки строго соответственно диапазонам значений.

DB2, Oracle и SQL Server

С помощью ранжирующей функции ROW NUMBER OVER упорядочьте строки. В предикате WHERE определите окно записей, которое требуется возвращать. Например, чтобы возвратить строки от 1 до 5:

select sal from (

select row number() over (order by sal) as rn, sal from emp ) x

where rn between 1 and 5

950 1100 1250 1250

Затем, чтобы возвратить строки 6-10:

select sal from (

select row number() over (order by sal) as rn, sal from emp ) x

where rn between 6 and 10

1300 1500 1600 2450 2850

Можно выбрать любой диапазон строк, просто меняя предикат WHERE внешнего запроса.

MySQL и PostgreSQL

Данные продукты поддерживают операторы LIMIT и OFFSET, что сильно упрощает задачу по просмотру результирующего множества. С помощью LIMIT задайте, какое количество строк должно быть возвращено, и с помощью OFFSET определите, сколько строк необходимо пропустить. Например, чтобы возвратить первые пять строк в порядке возрастания заработных плат:

select sal from emp order by sal limit 5 offset 0

800 950

1100 1250 1250

Чтобы возвратить следующие пять строк:

select sal from emp

order by sal limit 5 offset 5

1300 1500 1600

2450 2850

LIMIT и OFFSET не только упрощают написание решения для MySQL и PostgreSQL, но также делают их предельно понятными.

Обсуждение

DB2, Oracle и SQL Server

Ранжирующая функция ROW NUMBER OVER во вложенном запросе Х присвоит каждой заработной плате уникальный номер (в порядке возврастания, начиная с 1). Ниже представлено результирующее множество вложенного запроса Х:

select row number() over (order by sal) as rn,