Другие примеры в этой главе показывают, что подзапросы очень полезны для сценариев, где надо просмотреть или сравнить запрос по условию, для вычисления которого требуется собственный запрос. Как вы уже знаете из главы 1, в каждой таблице есть одно уникальное поле, первичный ключ, содержащее уникальный номер для каждой записи, но другие поля могут содержать избыточную информацию. Для удобства работы с избыточной информацией стоит отфильтровать данные, чтобы они отображали только уникальные значения. В этом случае полезно условие DISTINCT. Лучше понять подзапросы и использование условия DISTINCT нам помогут таблицы tracks и invoice_items.
Таблица invoice_items показывает, какие треки какому счету соответствуют. Если мы создадим запрос, отображающий поля InvoiceId и TrackId, упорядоченные по TrackId, мы увидим, что определенные номера треков были заказаны несколько раз в разных счетах.
SELECT
InvoiceId,
TrackId
FROM
invoice_items
ORDER BY
TrackId
Так, треки № 2 и № 8 появляются в нескольких счетах, что означает, что они были заказаны несколько раз (рис. 121). Однако для трека № 7 счет отсутствует, поэтому мы можем сделать вывод, что никто не приобретал его. Руководство sTunes хочет знать о треках, которые не продаются. Нам необходимо найти таблицу, связывающую поля TrackId с InvoiceId. Для перечисления всех треков (по композитору и названию), которые не отображаются в таблице invoice_items, мы можем использовать подзапросы.
Рис. 121
Рис. 122
Если мы снова запустим тот же запрос, только на этот раз с ключевым словом DISTINCT, мы получим список только тех треков, которые появляются в счетах, без дубликатов.
SELECT
DISTINCT TrackId
FROM
invoice_items
ORDER BY
TrackId
Из примера видно, что некоторые номера TrackId (например, № 7) не появляются ни в одном счете, но списки треков, которые появляются в нескольких счетах, сокращены до одного. Теперь нам необходимо написать запрос, перечисляющий все треки из таблицы tracks, которые не входят (NOTIN) в созданный первым запросом список.
SELECT
TrackId,
Composer,
Name
FROM
tracks
WHERE
TrackId NOT IN
(select distinct
TrackId
from
invoice_items)
Итак, у нас есть список песен, которых не было ни в одном счете (рис. 123). Анализируя полученные результаты, мы видим, что трек № 7 находится в самой верхней части списка непродаваемых треков. Теперь отдел продаж sTunes имеет четкое представление о том, какие песни непопулярны.
Рис. 123
Примеры подзапросов из этой главы не являются исчерпывающими. Существует еще множество вариантов использования подзапросов, и все они имеют общие особенности. Подзапросы могут выполнять сложные многоступенчатые вычисления с помощью всего одного запроса. Они позволяют вычислить конкретное условие, а затем сравнить новый набор данных с тем же условием.
Примечание
Подзапросы — это альтернативный способ взаимодействия таблиц, имеющих общие ключевые поля. Но если нам нужно много работать с обеими таблицами, то вместо подзапросов эффективнее создать join-соединение. Например, гораздо эффективнее создать соединение между полем TrackId и таблицей tracks (вместо использования подзапросов), чтобы отображать всю информацию рядом.
1. Сколько счетов превышают среднюю сумму счетов, выставленных в 2010 году?
2. Какие клиенты получили эти счета?
3. Сколько клиентов живут в США?
• Подзапросы позволяют выполнять в одном запросе несколько операторов SQL.
• Подзапросы состоят из двух или более отдельных операторов SQL, которые образуют внутренние и внешние запросы.
• Подзапросы обычно используются для сравнения существующих данных с данными, полученными с помощью агрегатов или других функций.
• Условие DISTINCT позволяет игнорировать избыточные данные в записях и искать только уникальные значения.
Представление является виртуальной таблицей. Это просто сохраненный SQL-запрос, который может выполняться многократно или на него (как подзапрос) могут ссылаться другие запросы. Представления полезны, если постоянно требуется один и тот же запрос, особенно когда он сложен. Предположим, руководство компании sTunes запрашивает одни и те же данные о продажах каждую неделю или квартал. Значит, имеет смысл подготовить представление — запрос на искомую информацию. Рассмотрим варианты использования представлений в SQL.
Все операторы SQL, показанные в предыдущих главах, можно сохранить и использовать повторно, когда нам потребуется создать представление. Рассмотрим запрос, показанный в начале главы 8.
SELECT
ROUND(AVG(Total), 2) AS [Average Total]
FROM
invoices
Мы можем преобразовать этот оператор в представление, добавив над верхней строкой запроса команду CREATEVIEWV_AvgTotalAS:
CREATE VIEW V_ AvgTotal AS
SELECT
ROUND(AVG(Total), 2) AS [Average Total]
FROM
invoices
Итак, мы создали представление V_AvgTotal.
Примечание
