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

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

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

Создадим запрос на выборку по имени ЗАПИТОГ с тремя полями. Первое поле выбирается из таблицы Tabv и содержит название изделия – это поле по имени Izd. Второе поле – новое, оно создается в результате вычисления итоговой функции SUM. Суммировать будем годовую стоимость выпуска одного и того же изделия на разных предприятиях. Присвоим этому полю имя ST_GOD. Третье поле тоже вычисляемое. С помощью итоговой функции COUNT поместим в него количество различных предприятий, выпускающих каждое изделие. Присвоим ему имя KOLPR.

Источником данных для запроса является таблица данных о выпуске изделий Tabv. В запросе используется группировка записей по полю Izd.

На языке SQL запрос выглядит следующим образом:

SELECT Izd, Sum ([zn1]*[vp1]+[zn2]*[vp2]+[zn3]*[vp3]+[zn4]*[vp4]) AS St_god, Count(Kodp) as Kolpr

FROM Tabv GROUP BY Tabv.Izd;

Вид запроса в режиме конструктора представлен на рисунке 16.

Рис. 16. Вспомогательный запрос ЗАПИТОГ в режиме конструктора.

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

Рис. 17. Результат выполнения запроса ЗАПИТОГ.

В этих данных только одно изделие имеет максимальную стоимость и одно изделие имеет минимальную стоимость. Однако при других исходных данных возможны варианты, когда несколько изделий будет иметь одну и ту же максимальную или минимальную стоимость. На следующих этапах нужно определить максимальную и минимальную стоимость из результатов запроса ЗАПИТОГ и вывести сведения об изделиях с этой стоимостью.

Реализовать эти действия можно тремя способами:

• с помощью двух запросов на выборку, один из которых определяет максимальную и минимальную стоимости, а второй отбирает из результатов запроса ЗАПИТОГ изделия с этими значениями суммарной стоимости;

• создав запрос с подчиненным запросом;

• с помощью одного запроса на выборку, использующего в критериях отбора статистические функции по подмножеству.

Рассмотрим все три способа.

Создание двух запросов.

Создадим вспомогательный запрос по имени ЗАПМАКС, подсчитывающий максимальную и минимальную стоимость годового выпуска изделий по результатам выполнения запроса ЗАПИТОГ. Результат запроса – это строка из двух чисел: максимальной и минимальной стоимости. В запросе создаются поля Maxst и Minst, рассчитываемые с помощью итоговых функций.

Запрос в режиме конструктора показан на рисунке 18.

Рис. 18. Вспомогательный запрос ЗАПМАКС в режиме конструктора.

На языке SQL этот запрос имеет вид:

SELECT Max( St_god) AS Maxst, Min(St_god) AS Minst

FROM ЗАПИТОГ;

Здесь группировка не нужна, так как определяется максимум и минимум среди всех строк виртуальной таблицы, полученной в результате выполнения запроса ЗАПИТОГ. Результат выполнения запроса представлен на рисунке 19.

Рис. 19. Результат выполнения запроса ЗАПМАКС.

Теперь создаем запрос, выводящий окончательный результат. Исходными данными для него являются два ранее созданных запроса : ЗАПИТОГ и ЗАПМАКС. Присвоим новому запросу имя ЗАПСПР1. Запрос в режиме конструктора представлен на рисунке 20.

Рис. 20. Запрос ЗАПСПР1 в режиме конструктора.

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

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

Создание запроса с подчиненным запросом.

Получить искомую справку на базе результатов запроса ЗАПИТОГ можно с помощью одного запроса, использующего в своем составе подчиненный запрос (или подзапрос). Подчиненный запрос может выступать в качестве источника данных для основного запроса или же входить в критерий отбора.

Создадим запрос на выборку ЗАПСПР2, источником данных для которого служит запрос ЗАПИТОГ, а в условии отбора используются подчиненные запросы, заданные на языке SQL. Подчиненные запросы находят максимальную и минимальную годовую стоимость выпуска среди различных изделий. Вид запроса в режиме конструктора представлен на рисунке 22.

Рис. 22. Запрос на выборку, использующий подзапросы.

Этому запросу соответствует следующая команда SQL:

SELECT Izd, Kolpr AS [Число предпр-й], St_god AS [Ст-ть год-го выпуска]

FROM ЗАПИТОГ WHERE (St_god=(SELECT Max(St_god) FROM ЗАПИТОГ;)) OR

(St_god=(SELECT Min(St_god) FROM ЗАПИТОГ;));

В результате выполнения этого запроса будут получены такие же сведения, как и в результате выполнения двух описанных ранее запросов. Они показаны на рисунке 21.

Использование статистических функций по подмножеству.

Вместо подчиненного запроса при отборе нужных данных из запроса ЗАПИТОГ можно использовать статистические функции по подмножеству dMax и dMin.

Как и итоговая функция Max, функция dMax определяет максимальное значение поля или выражения во всей таблице или в группе записей, но, кроме того, она может использоваться в условии отбора. Обычные же итоговые функции в условиях отбора не могут быть использованы.

Обращение к функции имеет вид:

DMax (“выражение”;”имя источника данных”[; “условие”]).

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

Обращение к остальным функциям по подмножеству имеет аналогичную структуру.

Создадим запрос ЗАПСПР3, который создает на базе результатов запроса ЗАПИТОГ нужную нам справку, используя функции dMax и dMin. Запрос в режиме конструктора представлен на рисунке 23.

Рис.23. Запрос ЗАПСПР3 в режиме конструктора.

На языке SQL этот запрос имеет следующий вид:

SELECT Izd, Kolpr AS [Число предпр-й], St_god AS [Ст-ть год-го выпуска]

FROM ЗАПИТОГ

WHERE (St_god=dMax(“St_god”,”ЗАПИТОГ”))

OR (St_god=dMin(“St_god”,”ЗАПИТОГ”));

Для запуска из меню будем использовать последний вариант – запрос ЗАПСПР3.