6. Формирование сложных запросов. Использование представлений

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

Для хранения вспомогательных запросов удобно использовать представления.

Представления, или просмотры (VIEW), представляют собой виртуальные таблицы. Информация в них не хранится постоянно, как в базовых таблицах, а формируется динамически при обращении к ним. Представление - это фактически тот же запрос, который выполняется всякий раз при участии представления в каком-либо запросе. У пользователя создается впечатление, что он работает с настоящей, реально существующей таблицей.

Для создания представления следует в ObjectExplorerвыбрать среди объектов БД узелViewи выполнить пункт контекстного менюNewView(рисунок 27).

Рисунок 27 – Создание представления

В появившемся окне AddTableследует добавить таблицы, которые будут участвовать в создании представления (рисунок 28).

Рисунок 28 – Добавление таблицы к запросу

После закрытия данного окна на правой панели SQLServerManagement Studio открывается новая вкладкаView-dbo.Viewс конструктором запросов (рисунок 29), работа с которым была рассмотрена ранее.

Рисунок 29 – Создание представления

Сохранить представления можно, выполнив пункт меню File–SaveViewили нажав кнопку.

Работа с представлениями в SQLServerManagement Studio осуществляется аналогично работе с базовыми таблицами. Открыть представление можно, выполнив пункт контекстного менюOpenView. Изменить запрос, на котором основано представление можно, выполнив пункт контекстного менюModify.

Следует отметить, что при изменении данных в базовых таблицах, на которых основывается представление, будут изменяться и данные в самом представлении, и, наоборот.

Пример 9:Вывести номер и наименование поставщика, который выполнил максимальное количество поставок.

Шаг 1.Для выполнения такого запроса сначала необходимо создать представление, вычисляющее для каждого поставщика количество выполненных поставок. Такое представление изображено на рисунке 30 и называется КоличествоПоставок.

Рисунок 30 – Создание представления КоличествоПоставок

В представлении используются две таблицы: Поставщик и Поставка. Производится группировка по поставщикам, а к коду товара применяется групповая операция Count, которая выполняет подсчет количества строк для каждой отдельной группы. Результат представляется в вычисляемом поле Количество.

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

Шаг 2.Определим максимальное количество поставок, выполненное одним поставщиком. Для этого создадим новое представление МаксКоличество, основанное на представлении КоличествоПоставок. Обратите внимание, что в данном случае при в область диаграммы добавляется не таблица, а представление, которое выбирается на вкладкеViewsокна Add Table (рисунок 31).

Рисунок 31 – Добавление представления к запросу

Для вычисления максимума следует применить агрегатную функцию Maxк значениям столбца Количество представления КоличествоПоставок. Результаты представлены в вычисляемом поле Максимум (рисунок 32).

Рисунок 32 – Создание представления МаксКоличество

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

Для этого создадим новое представление МаксПоставщик, основанное на двух представлениях: КоличествоПоставок и МаксКоличество.

Из представления МаксКоличество мы получаем максимальное количество поставок. По этому значению из представления КоличествоПоставок находим поставщиков, которым соответствует это значение.

Следует обратить внимание, что при добавлении в область диаграммы представлений КоличествоПоставок и МаксКоличество, СУБД не создает между ними связи. Если оставить представления несвязанными, то в результирующей таблице получим декартовое произведение представлений. Для получения правильного результата следует создать связь по одинаковым атрибутам в представлениях. Это атрибуты Количество из представления КоличествоПоставок и Максимум из представления МаксКоличество. Окончательный вид представления Максимум показан на рисунке 33.

Рисунок 33 – Создание представления Максимум

При необходимости можно добавлять в область диаграммы, из которых требуется выбрать данные. Например, для получения полной информации о поставщике, следует добавить таблицу Поставщик в область диаграммы. Между добавленной таблицей и представлением КоличествоПоставок автоматически создается внутреннее соединение по одинаковым атрибутам. Это атрибуты с именем Табельный номер. Результат представлен на рисунке 34.

Рисунок 34 – Добавление строки в таблицу Поставка

Следует учитывать, что одному значению максимума могут соответствовать несколько различных поставщиков. Например, при добавлении строки, выделенной на рисунке 35, в таблицу Поставка, произойдет изменение представления. В этом случае максимальное значение количества поставок, равное 3, будет соответствовать двум поставщикам: Иванову и Петрову. Результат представлен на рисунке 36.

Рисунок 35 – Добавление строки в таблицу Поставка

Рисунок 36 – Представление Максимум

Практическая часть:

С помощью конструктора запросов разработать представления в соответствии с вариантом задания.