Перекрестный запрос – это специальный вид агрегированного запроса, в котором после выполнения всех вычислений производится дополнительное разбиение строк на два набора, после чего формируется таблица, у которой слева располагаются значения одного набора, формируя заголовки строк, сверху – значения другого набора, формируя заголовки столбцов, и в центре – результаты вычислений. Благодаря такой структуре таблица с результатами перекрестного запроса оказывается гораздо проще для восприятия, чем результаты обычного агрегированного запроса. Так, на рис. 138 изображены одни и те же данные, полученные с помощью обычного агрегированного запроса (вверху) и с помощью перекрестного запроса (внизу). Очевидно, что второй вариант дает более наглядную картину ситуации.
Рис. 138. Результат агрегированного (вверху) и перекрестного (внизу) запросов,
построенных по одним и тем же данным
Для построения перекрестного запроса необходимо открыть конструктор запросов, после чего сменить тип запроса на перекрестный одноименной командой в меню Запрос. В результате в бланке запроса появятся две строки: Групповая операция и Перекрестная таблица (рис. 139).
Рис. 139. Выпадающий список строки Перекрестная таблица позволяет указать
местоположение значений выбранных столбцов
Функция строки Групповая операция осталась прежней (см. раздел "Агрегированные запросы"). Выпадающий список в строке Перекрестная таблица позволяет указать для каждого столбца расположение в результирующей таблице: слева (Заголовки строк), сверху (Заголовки столбцов), в центре (Значение); или скрыть значения столбца в результатах.
Замечание: В запросе может быть только один столбец для заголовков столбцов и только один столбец для значений.
Пример №7. База данных (файл Пример 7.mdb) содержит четыре таблицы: ГРУППА, СТУДЕНТ, ПРЕДМЕТ, ОЦЕНКА. Схема данных базы изображена на рис. 140, содержимое таблиц – на рис. 141.
Рис. 140. Схема данных для примера №7
Рис. 141. Содержимое таблиц базы данных для примера №7
Задача №1. Построить перекрестный запрос, возвращающий результаты экзаменов студентов.
Решение:
Рис. 142. Запрос для решения задачи №1 (пример №7)
Результат выполнения запроса:
Рис. 143. Результат выполнения запроса для решения задачи №1 (пример №7)
Комментарий: Для решения задачи необходимы данные из таблиц СТУДЕНТ, ПРЕДМЕТ и ОЦЕНКА. В бланке запроса указанные таблицы объединяются согласно схеме данных, в результате чего получается одна таблица, содержащая имя студента, название предмета и его экзаменационную оценку по этому предмету. Строки полученной таблицы группируются по столбцам ФИО и Предмет, а для столбца Оценка вычисляется значение функции SUM. Так как в таблице ОЦЕНКА столбцы СтудентID и ПредметID являются первичным ключом, то можно быть уверенным, что функция SUM будет применяться только к одной оценке.
Замечание: Рассмотренный вариант решения строит таблицу, в которую попадают студенты, имеющие хотя бы одну оценку, и предметы, по которым сдан хотя бы один экзамен. Студенты, не сдавшие ни одного экзамена, и предмет, по которому не было сдано ни одного экзамена, в результирующий набор не попадают. Для устранения этого недостатка необходимо использовать внешние объединения. Решение, получаемое в этом случае, достаточно громоздко и здесь не приводится.
Задача №2. Построить перекрестный запрос, рассчитывающий средний балл по каждому предмету для всех специальностей.
Решение:
Рис. 144. Запрос для решения задачи №2 (пример №7)
Результат выполнения запроса:
Рис. 145. Результат выполнения запроса для решения задачи №2 (пример №7)