Построение вычисляемых полей

⇐ ПредыдущаяСтр 3 из 4Следующая ⇒

В общем случае для создания вычисляемого (производного) поля в списке SELECT следует указать некоторое выражение языка SQL. В этих выражениях применяются арифметические операции сложения, вычитания, умножения и деления, а также встроенные функции языка SQL. Можно указать имя любого столбца (поля) таблицы или запроса, но использовать имя столбца только той таблицы или запроса, которые указаны в списке предложения FROM соответствующей инструкции. При построении сложных выражений могут понадобиться скобки.

Соединение – это процесс, когда две или более таблицы объединяются в одну. Способность объединять информацию из нескольких таблиц или запросов в виде одного логического набора данных обусловливает широкие возможности SQL.

Операция внутреннего соединения в языке SQL называется INNER JOIN и используется, когда нужно включить все строки из обеих таблиц, удовлетворяющие условию объединения. Внутреннее соединение имеет место и тогда, когда в предложении WHERE сравниваются значения полей из разных таблиц.

В условиях объединения могут участвовать поля, относящиеся к одному и тому же типу данных и содержащие один и тот же вид данных, но они не обязательно должны иметь одинаковые имена. Блоки данных из двух таблиц объединяются, как только в указанных полях будут найдены совпадающие значения.

Если в предложении FROM перечислено несколько таблиц и при этом не употребляется спецификация JOIN, а для указания соответствия полей из таблиц используется условие в предложении WHERE, то некоторые реляционные СУБД (например, Access) оптимизируют выполнение запроса, интерпретируя его как соединение.

Стандарты SQL позволяют явным образом задавать имена столбцов результирующей таблицы, для чего применяется фраза AS.

Пример 6.1. Рассчитать общую стоимость для каждой сделки. Этот запрос использует расчет результирующих столбцов на основе арифметических выражений.

SELECT Товар.Название, Товар.Цена, Сделка.Количество, Товар.Цена*Сделка.Количество AS Стоимость

FROM Товар INNER JOIN Сделка

ON Товар.КодТовара = Сделка.КодТовара

Пример 6.2. Получить список фирм с указанием фамилии и инициалов клиентов.

SELECT Фирма, Фамилия+"

" +Left(Имя, 1)+"." +Left(Отчество, 1)+"."

AS ФИО

FROM Клиент

В запросе использована встроенная функция Left, позволяющая вырезать в текстовой переменной один символ слева.

Пример 6.3. Получить список товаров с указанием года и месяца продажи.

SELECT Товар.Название, Year(Сделка.Дата)

AS Год, Month(Сделка.Дата) AS Месяц

FROM Товар INNER JOIN Сделка

ON Товар.КодТовара = Сделка.КодТовара

В запросе использованы встроенные функции Year и Month для выделения года и месяца из даты.

Использование итоговых функций

С помощью итоговых (агрегатных) функций в рамках SQL-запроса можно получить ряд обобщающих статистических сведений о множестве отобранных значений выходного набора

Пользователю доступны следующие основные итоговые функции:

Count (Выражение) – определяет количество записей в выходном наборе SQL-запроса;

Min/Max (Выражение) – определяют наименьшее и наибольшее из множества значений в некотором поле запроса;

Avg (Выражение) – эта функция позволяет рассчитать среднее значение множества значений, хранящихся в определенном поле отобранных запросом записей. Оно является арифметическим средним значением, т.е. суммой значений, деленной на их количество.

Sum (Выражение) – вычисляет сумму множества значений, содержащихся в определенном поле отобранных запросом записей.

Чаще всего в качестве выражения выступают имена столбцов. Выражение может вычисляться и по значениям нескольких таблиц.

Все эти функции оперируют со значениями в единственном столбце таблицы или с арифметическим выражением и возвращают единственное значение. Функции COUNT, MIN и MAX применимы как к числовым, так и к нечисловым полям, тогда как функции SUM и AVG могут использоваться только в случае числовых полей, за исключением COUNT(*). При вычислении результатов любых функций сначала исключаются все пустые значения, после чего требуемая операция применяется только к оставшимся конкретным значениям столбца. Вариант COUNT(*) — особый случай использования функции COUNT, его назначение состоит в подсчете всех строк в результирующей таблице, независимо от того, содержатся там пустые, дублирующиеся или любые другие значения.

Если до применения обобщающей функции необходимо исключить дублирующиеся значения, следует перед именем столбца в определении функции поместить ключевое слово DISTINCT. Оно не имеет смысла для функций MIN и MAX, однако его использование может повлиять на результаты выполнения функций SUM и AVG, поэтому необходимо заранее обдумать, должно ли оно присутствовать в каждом конкретном случае. Кроме того, ключевое слово DISTINCT может быть указано в любом запросе не более одного раза.

Итоговые функции могут использоваться только в списке предложения SELECT и в составе предложения HAVING. Во всех других случаях это недопустимо. Если список в предложении SELECT содержит итоговые функции, а в тексте запроса отсутствует фраза GROUP BY, обеспечивающая объединение данных в группы, то ни один из элементов списка предложения SELECT не может включать каких-либо ссылок на поля, за исключением ситуации, когда поля выступают в качестве аргументов итоговых функций.

Пример 6.4. Определить первое по алфавиту название товара.

SELECT Min(Товар.Название) AS Min_Название

FROM Товар

Пример 6.5. Определить количество сделок.

SELECT Count(*) AS Количество_сделок

FROM Сделка

Пример 6.6. Определить суммарное количество проданного товара.

SELECT Sum(Сделка.Количество) AS Количество_товара

FROM Сделка

Пример 6.7. Определить среднюю цену проданного товара.

SELECT Avg(Товар.Цена) AS Avg_Цена

FROM Товар INNER JOIN Сделка

ON Товар.КодТовара =Сделка.КодТовара;

Пример 6.8. Подсчитать общую стоимость проданных товаров.

SELECT Sum(Товар.Цена*Сделка.Количество) AS Стоимость

FROM Товар INNER JOIN Сделка

ON Товар.КодТовара = Сделка.КодТовара

Предложение GROUP BY

Часто в запросах требуется формировать промежуточные итоги, что обычно отображается появлением в запросе фразы «для каждого...». Для этой цели в операторе SELECT используется предложение GROUP BY. Запрос, в котором присутствует GROUP BY, называется группирующим запросом, поскольку в нем группируются данные, полученные в результате выполнения операции SELECT, после чего для каждой отдельной группы создается единственная суммарная строка. Стандарт SQL требует, чтобы предложение SELECT и фраза GROUP BY были тесно связаны между собой. При наличии в операторе SELECT фразы GROUP BY каждый элемент списка в предложении SELECT должен иметь единственное значение для всей группы. Более того, предложение SELECT может включать только следующие типы элементов: имена полей, итоговые функции, константы и выражения, включающие комбинации перечисленных выше элементов.

Все имена полей, приведенные в списке предложения SELECT, должны присутствовать и во фразе GROUP BY – за исключением случаев, когда имя столбца используется в итоговой функции. Обратное правило не является справедливым – во фразе GROUP BY могут быть имена столбцов, отсутствующие в списке предложении SELECT.

Если совместно с GROUP BY используется предложение WHERE, то оно обрабатывается первым, а группированию подвергаются только те строки, которые удовлетворяют условию поиска.

Стандартом SQL определено, что при проведении группирования все отсутствующие значения рассматриваютсякак равные. Если две строки таблицы в одном и том же группируемом столбце содержат значение NULL и идентичные значения во всех остальных непустых группируемых столбцах, они помещаются в одну и ту же группу.

Пример 6.9. Вычислить средний объем покупок, совершенных каждым покупателем.

SELECT Клиент.Фамилия, Avg(Сделка.Количество)

AS Среднее_количество

FROM Клиент INNER JOIN Сделка

ON Клиент.КодКлиента = Сделка.КодКлиента

GROUP BY Клиент.Фамилия

Фраза «каждым покупателем» нашла свое отражение в SQL-запросе в виде предложения GROUP BY Клиент.Фамилия.

Пример 6.10. Определить, на какую сумму был продан товар каждого наименования.

SELECT Товар.Название,

Sum(Товар.Цена*Сделка.Количество)

AS Стоимость

FROM Товар INNER JOIN Сделка

ON Товар.КодТовара = Сделка.КодТовара GROUP BY Товар.Название

Пример 6.11. Подсчитать количество сделок, осуществленных каждой фирмой.

SELECT Клиент.Фирма, Count(Сделка.КодСделки)

AS Количество_сделок FROM Клиент INNER JOIN Сделка

ON Клиент.КодКлиента = Сделка.КодКлиента GROUP BY Клиент.Фирма

Пример 6.12. Подсчитать общее количество купленного для каждой фирмы товара и его стоимость.

SELECT Клиент.Фирма,

Sum(Сделка.Количество) AS Общее_Количество,

Sum(Товар.Цена*Сделка.Количество) AS Стоимость

FROM Товар INNER JOIN

(Клиент INNER JOIN Сделка

ON Клиент.КодКлиента = Сделка.КодКлиента)

ON Товар.КодТовара = Сделка.КодТовара

GROUP BY Клиент.Фирма

Пример 6.13. Определить суммарную стоимость каждого товара за каждый месяц.

SELECT Товар.Название,

Month(Сделка.Дата) AS Месяц,

Sum(Товар.Цена*Сделка.Количество) AS Стоимость

FROM Товар INNER JOIN Сделка

ON Товар.КодТовара = Сделка.КодТовара

GROUP BY Товар.Название, Month(Сделка.Дата)

Пример 6.14. Определить суммарную стоимость каждого товара первого сорта за каждый месяц.

SELECT Товар.Название,

Month(Сделка.Дата) AS Месяц,

Sum(Товар.Цена*Сделка.Количество) AS Стоимость

FROM Товар INNER JOIN Сделка

ON Товар.КодТовара = Сделка.КодТовара

WHERE Товар.Сорт=" Первый"

GROUP BY Товар.Название, Month(Сделка.Дата)

Предложение HAVING

При помощи HAVING отражаются все предварительно сгруппированные посредством GROUP BY блоки данных, удовлетворяющие заданным в HAVING условиям. Это дополнительная возможность «профильтровать» выходной набор.

Условия в HAVING отличаются от условий в WHERE:

– HAVING исключает из результирующего набора данных группы с результатами агрегированных значений;

– WHERE исключает из расчета агрегатных значений по группировке записи, не удовлетворяющие условию;

– в условии поиска WHERE нельзя задавать агрегатные функции.

Пример 6.15. Определить фирмы, у которых общее количество сделок превысило 3.

SELECT Клиент.Фирма,

Count(Сделка.Количество) AS Количество_сделок

FROM Клиент INNER JOIN Сделка

ON Клиент.КодКлиента = Сделка.КодКлиента

GROUP BY Клиент.Фирма

HAVING Count(Сделка.Количество)> 3

Пример 6.16. Вывести список товаров, проданных на сумму более 10000 руб.

SELECT Товар.Название,

Sum(Товар.Цена*Сделка.Количество) AS Стоимость

FROM Товар INNER JOIN Сделка

ON Товар.КодТовара = Сделка.КодТовара

GROUP BY Товар.Название

HAVING Sum(Товар.Цена*Сделка.Количество)> 10000

Пример 6.17. Вывести список товаров, проданных на сумму более 10000 без указания суммы.

SELECT Товар.Название

FROM Товар INNER JOIN Сделка

ON Товар.КодТовара = Сделка.КодТовара

GROUP BY Товар.Название

HAVING Sum(Товар.Цена*Сделка.Количество)> 10000

Лекция 7 «Построение подзапросов»

План

1 Понятие подзапроса

2 Использование подзапросов, возвращающих единичное значение

3 Использование операций IN и NOT IN

4 Использование ключевых слов ANY и ALL

5 Использование операций EXISTS и NOT EXISTS

Понятие подзапроса

Часто невозможно решить поставленную задачу путем одного запроса. Это особенно актуально, когда при использовании условия поиска в предложении WHERE значение, с которым надо сравнивать, заранее не определено и должно быть вычислено в момент выполнения оператора SELECT. Внутренний подзапрос представляет собой также оператор SELECT, а кодирование его предложений подчиняется тем же правилам, что и основного оператора SELECT. Внешний оператор SELECT использует результат выполнения внутреннего оператора для определения содержания окончательного результата всей операции. Внутренние запросы могут быть помещены непосредственно после оператора сравнения (=, <, >, < =, > =, < > ) в предложения WHERE и HAVING внешнего оператора SELECT – они получают название подзапросов или вложенных запросов. Кроме того, внутренние операторы SELECT могут применяться в операторах INSERT, UPDATE и DELETE.

Подзапрос – это инструмент создания временной таблицы, содержимое которой извлекается и обрабатывается внешним оператором. Текст подзапроса должен быть заключен в скобки. К подзапросам применяются следующие правила и ограничения:

– фраза ORDER BY не используется, хотя и может присутствовать во внешнем подзапросе;

– список в предложении SELECT состоит из имен отдельных столбцов или составленных из них выражений – за исключением случая, когда в подзапросе присутствует ключевое слово EXISTS;

– по умолчанию имена столбцов в подзапросе относятся к таблице, имя которой указано в предложении FROM. Однако допускается ссылка и на столбцы таблицы, указанной во фразе FROM внешнего запроса, для чего применяются квалифицированные имена столбцов (т.е. с указанием таблицы);

– если подзапрос является одним из двух операндов, участвующих в операции сравнения, то запрос должен указываться в правой части этой операции.

Существует два типа подзапросов:

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

Табличный подзапрос возвращает множество значений, т.е. значения одного или нескольких столбцов таблицы размещенные в более чем одной строке. Он возможен везде, где допускается наличие таблицы.

⇐ Предыдущая 1 234 Следующая ⇒