|
SELECT 'Наиболее популярное блюдо' , b.Блюдо, SUM(z.Количество_порций)
FROM Блюда b JOIN Заказы z ON b.ID_ Блюдо = z. Блюдо
GROUP BY b. Блюдо
HAVING SUM(z.Количество_порций) = (SELECT MAX (кол)
FROM (SELECT SUM(Количество_порций) as кол
FROM Заказы
GROUP BY Блюдо) m)
UNION ALL
SELECT 'Наименее популярное блюдо', b.Блюдо, SUM(z.Количество_порций)
FROM Блюда b JOIN Заказы z ON b.ID_ Блюдо = z. Блюдо
GROUP BY b. Блюдо
HAVING SUM(z.Количество_порций) = (SELECT MIN (кол)
FROM ( SELECT SUM(Количество_порций) as кол
FROM Заказы
GROUP BY Блюдо) m);
Результат
|
|
|
Блюдо
|
|
Наиболее популярное блюдо
|
Салат летний
|
10
|
Наименее популярное блюдо
|
Драчена
|
1
|
Наименее популярное блюдо
|
Рулет с яблоками
|
1
|
4.6.3. Использование UNION с ORDER BY.
При построении запроса с UNION, часто необходимо чтобы данные выводились в каком- то особом порядке. Для этого необходимо использовать ORDER BY(один раз и в конце последнего предложения SELECT), чтобы упорядочить вывод из объединения, точно так же как это делается в индивидуальных запросах. Давайте пересмотрим наш последний пример, чтобы упорядочить строки по названиям блюд.
Пример 41.
SELECT 'Наиболее популярное блюдо' , b.Блюдо, SUM(z.Количество_порций)
FROM Блюда b JOIN Заказы z ON b.ID_ Блюдо = z. Блюдо
GROUP BY b. Блюдо
HAVING SUM(z.Количество_порций) = (SELECT MAX (кол)
FROM (SELECT SUM(Количество_порций) as кол
FROM Заказы
GROUP BY Блюдо) m)
UNION ALL
SELECT 'Наименее популярное блюдо', b.Блюдо, SUM(z.Количество_порций)
FROM Блюда b JOIN Заказы z ON b.ID_ Блюдо = z. Блюдо
GROUP BY b. Блюдо
HAVING SUM(z.Количество_порций) = (SELECT MIN (кол)
FROM ( SELECT SUM(Количество_порций) as кол
FROM Заказы
GROUP BY Блюдо) m)
ORDER BY b.Блюдо;
4.6.4. Реализация внешнего полного соединения через запросы с UNION.
Если ваша система баз данных не поддерживает синтаксис FULL OUTER JOIN, те же результаты можно получить с помощью более сложного запроса, используя UNION.
Схема такого запроса имеет вид:
SELECT <поля для вывода>
FROM таблица_А LEFT OUTER JOIN таблица_В ON <условие связи>
UNION
SELECT <поля для вывода>
FROM таблица_А RIGHT OUTER JOIN таблица_В ON <условие связи>;
Как вы можете видеть, левое и правое внешние соединения, просто объединяются друг с другом с помощью оператора UNION.
Следует отметить, что предложение GROUP BY с оператором GROUPING SETS может дать такой же результирующий набор, как и несколько простых предложений GROUP BY, объединенных с помощью UNION ALL.
Синтаксис:
GROUP BY GROUPING SETS (<набор группирования> [, ... n ])
Следующие инструкции эквивалентны.
SELECT Блюдо, ID_Заказ, SUM(Количество_порций)
FROM Заказы
GROUP BY GROUPING SETS (Блюдо,ID_Заказ);
SELECT Блюдо, NULL as ID_Заказ, SUM(Количество_порций)
FROM Заказы
GROUP BY Блюдо
UNION ALL
SELECT NULL as Блюдо, ID_Заказ, SUM(Количество_порций)
FROM Заказы
GROUP BY ID_Заказ;
И дают одинаковый результат:
Результат
|
|
Блюдо
|
ID_Заказа
|
|
1
|
NULL
|
10
|
2
|
NULL
|
3
|
4
|
NULL
|
3
|
14
|
NULL
|
4
|
15
|
NULL
|
8
|
16
|
NULL
|
1
|
25
|
NULL
|
1
|
27
|
NULL
|
2
|
33
|
NULL
|
2
|
NULL
|
1
|
20
|
NULL
|
2
|
4
|
NULL
|
3
|
6
|
NULL
|
4
|
1
|
NULL
|
5
|
3
|
Глава 5. Функции ранжирования.
Очень часто возникает вопрос: «Как получить последнюю добавленную в таблицу строку?». Ответом на вопрос будет "никак", если в таблице не предусмотрен столбец, содержащий дату вставки строки, или не используется последовательная нумерация строк, реализуемая во многих СУБД с помощью столбца с автоинрементируемым значением. Тогда можно выбрать строку с максимальным значением даты или счетчика.
Реляционная модель исходит из того факта, что строки в таблице не имеют порядка, являющегося прямым следствием теоретико-множественного подхода. Вопрос о последней строке имеет смысл только в аспекте выдачи результата выполнения запроса, при этом предполагается некоторая сортировка, которая задается с помощью предложения ORDER BY в операторе SELECT. Если никакая сортировка не задана, то полагаться на то, что порядок вывода строк, полученных при выполнении запроса сегодня, останется таким же и завтра, нельзя, т.к. этот порядок зависит от плана, который выбирает оптимизатор запросов для их выполнения. А план может меняться, и зависит это от многих причин, которые мы здесь опустим.
Теоретически каждая строка запроса обрабатывается независимо от других строк. Однако на практике часто требуется при обработке строки соотносить ее с предыдущими или последующими строками (например, для получения нарастающих итогов), выделять группы строк, обрабатываемые независимо от других и т.д. В ответ на потребности практики в ряде СУБД в языке SQL появились соответствующие конструкции, в частности, функции ранжирования и оконные (аналитические) функции, которые де-юре были зафиксированы в стандарте SQL:2003. В SQL Server ранжирующие функции появились в версии 2005.
5.1. Функция ROW_NUMBER.
Функция ROW_NUMBER, нумерует строки, возвращаемые запросом. С ее помощью можно выполнить более сложное упорядочивание строк в отчете, чем то, которое дает предложение ORDER BY в рамках Стандарта SQL-92.
Используя функцию ROW_NUMBER можно:
задать нумерацию, которая будет отличаться от порядка сортировки строк результирующего набора;
создать "несквозную" нумерацию, т.е. выделить группы из общего множества строк и пронумеровать их отдельно для каждой группы;
использовать одновременно несколько способов нумерации, поскольку, фактически, нумерация не зависит от сортировки строк запроса.
Покажем возможности функции ROW_NUMBER на простых примерах.
Пример 42.
Пронумеровать все блюда из таблицы Блюда в алфавитном порядке. Выполнить сортировку по {Основа, Блюдо}.
SELECT row_number() over(ORDER BY Блюдо) as Номер, Блюдо, Основа
FROM Блюда
Do'stlaringiz bilan baham: |
