Лабораторная работа « Основы языка SQL» (стр. 4 )

FROM Продукты a, Продажи b

WHERE b.ДатаПродажи > #3/25/2003# AND a.КодПрод = b.КодПрод

GROUP BY a.Продукт

HAVING SUM(b. Количество) < 20;

Рисунок 3.12. Группировка продаж, не превышающих заданной величины

Подзапросы в инструкции SELECT

Подзапрос — это запрос, который размещается внутри другого запроса, а точнее, является частью предложения WHERE (или HAVING) основного запроса и заключается в круглые скобки. Иногда подзапросы называют вложенными запросами. Запрос, содержащий подзапрос, называют сложным запросом. При выполнении сложного запроса сначала выполняется подзапрос (он для этого и заключается в скобки!), а затем — основной запрос.

Выходные данные подзапроса обычно используются при оценке выражения в предложении WHERE (или HAVING) основного (по отношению к данному подзапросу) запроса с использованием таких операторов, как =, >, <, о, IN, NOT IN, AND, OR. (Эти операторы можно использовать и внутри подзапроса.)

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

· Предложение SELECT подзапроса обычно содержит только один столбец и, следовательно, может иметь результатом массив однородных (только строковые значения, только целые значения и т. д.) значений или только одно значение. Очень редко встречаются случаи использования нескольких столбцов. Если подзапрос возвращает массив значений, соответствующее этому подзапросу предложение WHERE может содержать только многозначные операторы, например, IN.

· Предложение ORDER BY следует использовать только в основном запросе. В подзапросе для упорядочения данных необходимо применять предложение GROUP BY.

· Недопустимо использование в основном запросе предложения BETWEEN

Простой синтаксис подзапроса для оператора SELECT следующий:

SELECT { * I table.* |  [table.]field1 [AS allas1] [,  [table.]field2 [AS alias2] [,  ...]])

FROM table1 [table1Alias] [, table2 [table2Alias]  [,  ...]

WHERE field1 operator

(SELECT { * I table.* I  [table.]field1 [AS alias1] [,  [table.]field2 [AS alias2] [,  ...]]}

FROM table1 [table1Alias] [,  table2 [table2Alias]  [,  ...]

WHERE [criteria])

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

Напомним, что схема данных (рис. 3.13) БД Lab 3.mdb та же, что и в Lab 2.mdb.

Рисунок 3.13. Схема данных Lab 3.mdb

Запрос, связывающий продажи продуктов с поставщиками выглядит следующим образом:

SELECT a.Продукт AS [Наименование продукта], b.Количество AS [Количество], d.КодПост AS [Поставщик]

FROM Продукты a, Продажи b, Поставки c, Поставщики d

WHERE a.КодПрод = b.КодПрод AND b.КодПрод = c.КодПрод AND c.КодПост = d.КодПост;

Его результат показан на рис. 3.14.

Рисунок 3.14. Продажи, связанные с поставщиками

Для начала нам нужно определить этого поставщика, предоставившего наибольшее количество наименований товара. Создадим запрос, который возвратит код такого поставщика:

SELECT ТОР 1 КодПост

FROM Поставки

GROUP BY КодПост

ORDER BY COUNT(КодПрод) DESC

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

Рисунок 3.15. Код поставщика с наибольшим количеством наименований

Остается подготвить запрос, который возвращает список проданных продуктов для указанного кода поставщика, но если ранее мы бы указывали этот код явно, то теперь определяем его в подзапросе (результат — на рис. 3.16):

SELECT a.Продукт AS [Наименование продукта], b.Количество AS Количество, d.КодПост AS Поставщик

FROM Продукты AS a, Продажи AS b, Поставки AS c, Поставщики AS d

WHERE a.КодПрод = b.КодПрод AND b.КодПрод = c.КодПрод AND c.КодПост = d.КодПост AND c.КодПост =

(SELECT TOP 1 КодПост

FROM Поставки

GROUP BY КодПост

ORDER BY COUNT(КодПрод) DESC);

Рисунок 3.16. Продажи продуктов от поставщика с наибольшим количеством наименований

Анализ такого запроса показывает, что количество проданных наименований существенно меньше количества наименований продуктов, предоставленных данным поставщиком.

Заменив в первой строке запроса d.КодПост на d. Поставщик получим результат  официальным названием фирмы (рис. 3.17).

Рисунок 3.17. Название фирмы в явном виде