3.6. Резюме
Краткое знакомство с возможностями предложения
SELECT показало, что с его помощью можно реализовать все реляционные
операции. Кроме того, в предложении SELECT выполняются разнообразные
вычисления, агрегирование данных, их упорядочение и ряд других
операций, позволяющих описать в одном предложении ту работу, для
выполнения которой потребовалось бы написать несколько страниц
программы на алгоритмических языках Си, Паскаль или на внутренних
языках ряда распространенных СУБД.
Например, пусть требуется получить
калорийность и стоимость тех блюд, для которых:
- есть все составляющие их продукты;
- калорийность не превышает 400 ккал;
- стоимость не превышает 1.5 рубля,
а результат надо упорядочить по возрастанию калорийности блюд в рамках
их видов.
Для этого можно дать запрос, показанный на
рис. 3.2, позволяющий получить искомый результат в виде таблицы
Вид |
Блюдо |
Горячее |
Помидоры с луком |
калорий - |
244.6 |
0.44 |
руб |
Горячее |
Бефстроганов |
калорий - |
321.3 |
0.53 |
руб |
Горячее |
Драчена |
калорий - |
333.9 |
0.33 |
руб |
Горячее |
Каша рисовая |
калорий - |
339.2 |
0.27 |
руб |
Горячее |
Омлет с луком |
калорий - |
354.9 |
0.36 |
руб |
Десерт |
Яблоки печеные |
калорий - |
170.2 |
0.30 |
руб |
Десерт |
Крем творожный |
калорий - |
394.3 |
0.27 |
руб |
Закуска |
Салат летний |
калорий - |
155.5 |
0.32 |
руб |
Закуска |
Салат витаминный |
калорий - |
217.4 |
0.37 |
руб |
Закуска |
Творог |
калорий - |
330.0 |
0.22 |
руб |
Закуска |
Мясо с гарниром |
калорий - |
378.7 |
0.62 |
руб |
Напиток |
Кофе черный |
калорий - |
7.1 |
0.05 |
руб |
Напиток |
Компот |
калорий - |
74.4 |
0.14 |
руб |
Напиток |
Кофе на молоке |
калорий - |
154.8 |
0.11 |
руб |
Напиток |
Молочный напиток |
калорий - |
264.9 |
0.34 |
руб |
Суп |
Суп молочный |
калорий - |
396.6 |
0.22 |
руб |
SELECT Вид, Блюдо, 'калорий -', (SUM(INT((Белки+Углев)*4.1+Жиры*9.3)*Вес/1000)), (SUM(Стоимость/К_во*Вес/1000)+MIN(Труд/100)),’руб’ FROM Блюда, Вид_блюд, Состав, Продукты, Наличие WHERE Блюда.БЛ = Состав.БЛ AND Состав.ПР = Продукты.ПР AND Состав.ПР = Наличие.ПР AND Блюда.В = Вид_блюд.В AND БЛ NOT IN ( SELECT БЛ FROM Состав WHERE ПР IN ( SELECT ПР FROM Наличие WHERE К_во = 0)) GROUP BY Вид, Блюдо HAVING SUM(Стоимость/К_во*Вес/1000+MIN(Труд/100)) < 1.5 AND SUM(((Белки+Углев)*4.1+Жиры*9.3)*Вес/1000) < 400 ORDER BY Вид, 4;
Рис. 3.2. Пример сложного запроса
Такой результат, нестрого говоря, строился
следующим образом.
- FROM. Эта фраза инициирует создание в рабочей памяти
таблицы,
являющейся декартовым произведением таблиц Блюда, Вид_блюд, Состав,
Продукты и Наличие.
- WHERE. Эта фраза нужна для преобразования полученного
декартова произведения в естественное соединение и удаления из
последнего строк с кодами блюд, не обеспеченных продуктами.
Естественное соединение образуется путем вычеркивания строк, где не
совпадают: код блюда из таблицы Блюда с кодом блюда из таблицы Состав,
код продукта из таблицы Состав с кодом продукта из таблицы Продукты и
т.д. Обеспеченность блюда всеми продуктами проверяется с помощью
последовательности подзапросов. Внутренний подзапрос выдает перечень
кодов продуктов, которых нет в кладовой пансионата. Следующий подзапрос
выдает коды тех блюд, в состав которых должны входить "отсутствующие"
продукты. И, наконец, из естественного соединения вычеркиваются строки
с кодами полученных блюд (точнее оставляются строки "Где код блюда не
принадлежит перечню кодов блюд, полученному в подзапросе".
- SELECT. Из полученного соединения удаляются столбцы, не
используемые в выражениях SELECT или других фразах. Если в списке
SELECT есть выражения (константы), то для хранения их значений
формируются дополнительные столбцы и инициируются операции по их
заполнению. В рассматриваемом примере будут сохранены столбцы Вид,
Блюдо, Белки, Углев, Жиры, Вес, Стоимость, К_во и созданы
дополнительные столбцы для формирования и хранения значений стоимости и
калорийности составляющих каждого блюда, а также для хранения текстовых
констант 'калорий -' и 'руб'. Обратите внимание на прием,
использованный при суммировании стоимостей продуктов, входящих в состав
блюда, и стоимости его приготовления (Труд): можно ли заменить MIN на
MAX или AVG?
- GROUP BY. Отредактированное естественное соединение
группируется по видам блюд и их названиям. Создаются группы горячих
блюд, десертов и т.д., а внутри каждой группы создаются подгруппы строк
со сведениями о продуктах, относящихся к конкретному блюду группы.
- SELECT. Каждая подгруппа строк, полученная на предыдущем
шаге, преобразуется в единственную строку для результата. В нее
заносится вид блюда (общий для всех подгрупп группы), название блюда
(общее для всех строк подгруппы), две текстовых константы ('калорий -'
и 'руб') и две суммы. Последние формируются путем суммирования тех
значений дополнительных столбцов, которые принадлежат подгруппе.
- HAVING. Сформированные строки, не удовлетворяющие условиям
фразы HAVING
SUM(Стоимость/К_во*Вес/1000+MIN(Труд/100)) < 1.5 и SUM(((Белки+Углев)*4.1+Жиры*9.3)*Вес/1000) < 400
исключаются из результата предыдущего шага.
- ORDER BY. Результат шага 6 упорядочивается в соответствии
со
списком фразы ORDER BY для получения окончательного результата. Сначала
строки группируются по видам блюд (в алфавитном порядке), а затем – по
значению элемента данных, указанного на четвертом месте фразы SELECT,
т.е. по калорийности.
Конечно, рассмотренный запрос весьма сложен,
но
попробуйте написать на любом знакомом вам языке программу, реализующую
те же действия, и оцените сложность ее написания и отладки.
3.5 | Содержание
| 4.1
|