Краткое знакомство с возможностями предложения 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, т.е. по калорийности.
Конечно, рассмотренный запрос весьма сложен, но попробуйте написать на любом знакомом вам языке программу, реализующую те же действия, и оцените сложность ее написания и отладки.
Например, пусть требуется получить калорийность и стоимость тех блюд, для которых:
есть все составляющие их продукты;
калорийность не превышает 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, т.е. по калорийности.
Конечно, рассмотренный запрос весьма сложен, но попробуйте написать на любом знакомом вам языке программу, реализующую те же действия, и оцените сложность ее написания и отладки.
Навигация