Перекрестные запросы

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

Поэтому в QBE-бланке перекрестного запроса всегда имеются 3 поля: одно из них используется для названия строк, второе – для названия столбцов и третье – для выбора соответствующего значения, которое должно размещаться на их пересечении.

Рис. 23. Окно создания перекрестного запроса

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

Например, пусть в таблице «Контракты» необходимо разгруппировать заключенные с кли5коами контракты по типу товаров. Для организации такого запроса необходимо выбрать в QBE-область поля: «Клиент», «Товар», «Количество» и выполнить команду Запрос/Перекрестный, которая добавит в QBE-бланк строки Групповая операция и Перекрестная таблица (см. рис. 23). Затем в строке QBE-бланка Групповая операция для полей «Клиент» и «Товар» задать функцию Группировка, а для поля «Количество» – функцию Sum. В строке Перекрестная таблица задать для поля «Клиент» функцию Заголовки строк, для поля «Товар» – функцию Заголовки столбцов и для поля «Количество» – функцию Значение. В результате выполнения данного запроса должна появиться таблица вида, представленного на рис. 24.

Выполните созданный запрос и сохраните его под именем «Клиент-товар-количество».

4.7. Запрос в Режиме SQL

В Access запросы представляются в трех режимах: Конструктор, Режим таблицы и Режим SQL. Ранее уже упоминалось, что любой запрос является программой, написанной на языке структурированных запросов SQL. Этот язык является общепринятым стандартным языком запросов в реляционных БД. Он был разработан фирмой IBM для реляционной модели данных, предложенной Э.Ф. Коддом. Фактически программа на SQL представляет собой некоторую фразу-запрос к выборке данных на английском языке, записанную в определенной структуре, которую затем СУБД преобразует в требуемый результат. В данном пособии SQL не будет рассматриваться подробно, однако, вообще говоря, знание основных особенностей этого языка поможет лучше понять процесс выполнения запроса, а также при необходимости отредактировать его или даже построить более эффективный запрос.

Рис. 25. Режим SQL для запроса на просмотр с сортировкой

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

Например, после перехода к режиму SQL ранее сформированный мастером Простых запросов запрос «Клиенты» будет выглядеть так, как это показано на рис. 25. Как видно, текст SQL программы запроса достаточно прост и вполне доступен для понимания с первого раза. В первой строке после оператора SELECT (выбрать, англ.) указываются поля таблицы «Клиенты», которые выбраны для запроса. Во второй строке после оператора FROM (из, англ.) указывается таблица, из которой эти поля выбираются, а в третьей строке после оператора ORDER BY (упорядочить по, англ.) указано поле, по которому проводится сортировка результирующих записей.

Запрос с заданным критерием отбора записей «Контракты после 1 марта», в котором производится выборка по условию, имеет программу с аналогичной структурой. Однако в программу запроса (см. рис. 26) добавлена строка с условием, которое сформировано после оператора WHERE (где, англ.) и по которому из таблицы «Контракты» выбираются все записи с контрактами, заключенными после 1 марта 2001 года.

При задании запроса с группировкой записей и вычислением итоговых значений используется оператор группировки GROUP BY (группировать по, англ.) и соответствующая вычислительная операция Sum по числовым полям для сгруппированных данных (см. рис. 27).

Рис. 26. Режим SQL для запроса с условием на выборку записей

Рис. 27. Режим SQL для запроса с группировкой и вычислением

Если запрос строится на основе полей из нескольких связанных между собой таблиц, то это, естественно, усложняет программу, в которой нужно обеспечить определенное согласование отбираемых для запроса данных. В частности, в более сложном запросе «Товары Запрос», основанном на трех взаимосвязанных таблицах «Товары», «Контракты» и «Сотрудники», появляются операторы INNER JOIN (внутреннее объединение, англ.) для внутреннего объединения таблиц по связующему их полю и DISTINCTROW (отличающаяся запись, англ.) для ограничения выбираемых данных однозначными записями (см. рис. 28).

Перекрестный запрос всегда преобразует таблицу запроса к форме электронных таблиц Excel, поэтому первым оператором в таком запросе является оператор TRANSFORM (преобразовать, англ.), который определяет вычисляемые значения в ячейках результирующей таблицы запроса. После операторов выбора SELECT … FROM и оператора группировки GROUP BY следует оператор PIVOT (опора, англ.), где указывается поле, которое должно использоваться при создании заголовков

столбцов для набора записей перекрестного запроса.

Рис. 28. Запрос в Режиме SQL на основе связанных таблиц

Рис. 29. Перекрестный запрос в Режиме SQL

Перечислим в заключение назначение некоторых основных операторов языка SQL:

SELECT – выбирает поля из таблиц по запросу;

FROM – указывает таблицу, из которой были выбраны поля;

WHERE – создает условие на выборку данных в записях;

ORDER BY – сортирует записи в заданном порядке;

GROUP BY – группирует совпадающие записи при выполнении итоговых запросов;

INNER JOIN – объединяет таблицы по связующим полям;

DISTINCTROW – исключает из результирующего набора повторяющиеся записи;

TRANSFORM – вычисляет выражения в перекрестных запросах;

PIVOT – определяет заголовки столбцов в таблице перекрестного запроса.

Просмотрите и проанализируйте в Режиме SQL другие созданные с помощью Конструктора либо Мастера запросы.

Отредактируйте программы запросов на основе проанализированных выше свойств операторов языка SQL (предварительно скопируйте в буфер обмена SQL-программу запроса, чтобы была возможность вернуться к ее первоначальному варианту) и просмотрите полученные после редактирования результаты.

На основе запроса «Контракты» создайте в Режиме SQL программу запроса выборки из таблицы «Контракты» полей «Товар», «Клиент»,

«Количество» с условием по количеству > 100, группировкой по полю «Клиент» и сортировкой по полю «Товар».

Контрольные вопросы

Назначение запросов.

Типы запросов.

Процедура создания простого запроса с помощью Мастера.

Структура QBE-бланка Конструктора.

Создание запроса на выборку по условию.

Создание сложного запроса со связками «и» и «или».

Структура Построителя выражений.

Назначение и процедура создания запроса с параметром.

Назначение и процедура создания запроса с вычисляемым полем.

Способы переименования полей в таблице запроса.

Назначение и процедура создания итогового запроса.

Назначение функций групповых операций.

Назначение и процедура создания перекрестного запроса

Режим SQL для создания запросов.

Назначение операторов SELECT, FROM, WHERE в программе запроса.

Назначение операторов ORDER BY, GROUP BY, TRANSFORM в программе запроса.

Дата добавления: 2015-05-09