Если в операторе SELECT требуется добавить дополнительное действие (например, агрегатное вычисление), то для его выполнения нам понадобится подзапрос. В предыдущей главе, посвященной функциям, показано, что для отображения средних значений в счетах для разных городов мы использовали условие GROUPBY. Что будет, если для компании sTunes нам надо узнать показатели продаж в каждом отдельном городе и сравнить их со средними мировыми продажами? Один из способов ответить на это — написать запрос, который будет отображать средний объем продаж в каждом городе рядом со среднемировым показателем.
Запрос для отображения среднего объема продаж BillingCity идентичен запросу, который мы использовали в предыдущей главе, за одним исключением. Для расчета глобального среднего показателя мы включаем подзапрос в условие SELECT. Таким образом, мы можем сравнить два значения.
SELECT
BillingCity,
AVG(Total) AS [City Average],
(SELECT
avg(total)
from
invoices) AS [Global Average]
FROM
invoices
GROUP BY
BillingCity
ORDER BY
BillingCity
Результат этого запроса показывает, как продажи в каждом городе соотносятся со среднемировым уровнем.
Рис. 114
Из рис. 114 видно, что значение для Global Average в каждой возвращаемой записи остается неизменным, что позволяет нам легко сравнивать средние итоговые суммы счетов по городам с мировым средним значением.
• Измените запрос, используя функцию ROUND(), чтобы отображалось только два десятичных знака.
Иногда надо получить более подробный запрос в качестве подзапроса. Внешний запрос может содержать условие WHERE, которое, в свою очередь, содержит подзапрос с собственным условием WHERE. Хороший пример того, когда в подзапросе необходимо использовать условие WHERE, если требуется сравнить все поля с отдельным значением. Предположим, нас попросили найти самые большие продажи за весь период сбора данных (2009–2012 гг.) и проверить, имеются ли какие-либо итоговые суммы счетов за последний отчетный год (2013 г.), превышающие это значение. Чтобы ответить, сначала необходимо узнать самые большие продажи до 2013 года. Для этого воспользуемся функцией MAX().
SELECT
MAX(Total)
FROM
invoices
WHERE
InvoiceDate < '2013-01-01'
Рис. 115
Теперь, когда нам известно это значение, мы заключим запрос в круглые скобки (), а затем добавим внешний запрос и вставим необходимые дополнительные поля.
SELECT
InvoiceDate,
BillingCity,
Total
FROM
invoices
WHERE
InvoiceDate >= '2013-01-01' AND total >
(select
max(Total)
from
invoices
where
InvoiceDate < '2013-01-01')
Из запроса видно, что максимальный счет был выставлен 13 ноября 2013 года.
Рис. 116
• Сколько счетов, значения которых превышали среднюю сумму счета, было зарегистрировано 1 января 2010 года или ранее?
Подзапрос не всегда содержит агрегатную функцию. Следующий запрос отображает дату конкретной транзакции.
SELECT
InvoiceDate
FROM
invoices
WHERE
InvoiceId = 251
Рис. 117
Если необходимо узнать, получены ли какие-либо другие счета после указанного выше счета, мы добавим подзапрос, заключенный в круглые скобки, а затем добавим внешний запрос.
SELECT
InvoiceDate,
BillingAddress,
BillingCity
FROM
invoices
WHERE
InvoiceDate >
(select
InvoiceDate
from
invoices
where
InvoiceId = 251)
Рис. 118
До этого момента мы использовали только подзапросы для вычисления единственного значения, которое затем передается внешнему запросу. Также можно использовать подзапросы, возвращающие несколько записей. Допустим, руководство компании sTunes хочет получить только три конкретных счета. Чтобы их выбрать, нужен следующий запрос:
SELECT
InvoiceDate
FROM
invoices
WHERE
InvoiceId IN (251, 252, 255)
Рис. 119
В предыдущем запросе для возврата трех дат из таблицы invoices используется условие IN: 2012-01-09, 2012-01-22 и 2012-01-24. Теперь предположим, что нам нужна информация о покупках за эти три дня. Если необходимо выбрать все счета за эти три дня, мы можем написать новый запрос или просто использовать предыдущий в качестве подзапроса, например:
SELECT
InvoiceDate,
BillingAddress,
BillingCity
FROM
invoices
WHERE
InvoiceDate IN
(SELECT
InvoiceDate
from
invoices
where
InvoiceId in (251, 252, 255))
Преобразование существующего запроса в подзапрос полезно, когда вы «играете» со своими данными. Чтобы еще больше сузить область поиска, этот метод позволяет повторно использовать существующий запрос и изменять его.
Рис. 120
