Язык sql запросов access
Вложенные запросы SQL (Microsoft Access SQL) SQL Subqueries (Microsoft Access SQL)
Область применения: Access 2013, Office 2013 Applies to: Access 2013, Office 2013
Синтаксис Syntax
Вы можете использовать три формы синтаксиса для создания вложенного запроса: You can use three forms of syntax to create a subquery:
сравнение [ANY | ALL | SOME ] (sqlstatement) comparison [ANY | ALL | SOME] (sqlstatement)
выражение [NOT] IN (sqlstatement) expression [NOT] IN (sqlstatement)
[NOT] EXISTS (sqlstatement) [NOT] EXISTS (sqlstatement)
Вложенный запрос состоит из следующих частей: A subquery has these parts:
Выражение и оператор сравнения, который сравнивает выражение с результатами вложенного запроса. An expression and a comparison operator that compares the expression with the results of the subquery.
Выражение, для которого выполняется поиск по набору результатов для вложенного запроса. An expression for which the result set of the subquery is searched.
Оператор SELECT с тем же форматом и правилами, что и любой другой оператор SELECT. A SELECT statement, following the same format and rules as any other SELECT statement. Его необходимо включать в скобки. It must be enclosed in parentheses.
Примечания Remarks
Вы можете использовать вложенный запрос вместо выражения в списке полей оператора SELECT или предложении WHERE или HAVING. You can use a subquery instead of an expression in the field list of a SELECT statement or in a WHERE or HAVING clause. Во вложенном запросе вы используете оператор SELECT для предоставления набора одного или нескольких определенных значений для оценки в выражении для предложения WHERE или HAVING. In a subquery, you use a SELECT statement to provide a set of one or more specific values to evaluate in the WHERE or HAVING clause expression.
Используйте предикат ANY или SOME, которые являются синонимами, для получения записей в основном запросе, который удовлетворяет сравнению с любыми записями, полученными во вложенном запросе. Use the ANY or SOME predicate, which are synonymous, to retrieve records in the main query that satisfy the comparison with any records retrieved in the subquery. Следующий пример возвращает все продукты, для которых цена за единицу выше, чем у любого продукта, продаваемого со скидкой 25 процентов или более: The following example returns all products whose unit price is greater than that of any product sold at a discount of 25 percent or more:
Используйте предикат ALL для получения записей в основном запросе, который удовлетворяет сравнению со всеми записями, полученными во вложенном запросе. Use the ALL predicate to retrieve only those records in the main query that satisfy the comparison with all records retrieved in the subquery. Если вы изменили предикат с ANY на ALL в предыдущем примере, запрос будет возвращать только те продукты, у которых цена за единицу больше, чем у всех продуктов, проданных со скидкой 25 процентов или более. If you changed ANY to ALL in the previous example, the query would return only those products whose unit price is greater than that of all products sold at a discount of 25 percent or more. Это гораздо более строгое ограничение. This is much more restrictive.
Используйте предикат IN для получения только тех записей в основном запросе, для которых определенная запись во вложенном запросе содержит одинаковое значение. Use the IN predicate to retrieve only those records in the main query for which some record in the subquery contains an equal value. В примере ниже возвращаются все продукты со скидкой 25 процентов или больше: The following example returns all products with a discount of 25 percent or more:
С другой стороны, вы можете использовать NOT IN для получения только тех записей в основном запросе, для которых ни одна запись во вложенном запросе не содержит одинаковое значение. Conversely, you can use NOT IN to retrieve only those records in the main query for which no record in the subquery contains an equal value.
Используйте предикат EXISTS (с необязательным зарезервированным словом NOT) в сравнениях ИСТИНА/ЛОЖЬ, чтобы определить, возвращает ли вложенный запрос какие-либо записи. Use the EXISTS predicate (with the optional NOT reserved word) in true/false comparisons to determine whether the subquery returns any records.
Также можно использовать псевдонимы имени таблицы во вложенном запросе для ссылки на таблицы, указанные в предложении FROM за пределами вложенного запроса. You can also use table name aliases in a subquery to refer to tables listed in a FROM clause outside the subquery. Пример ниже возвращает имена сотрудников, чья заработная плата равна или выше средней заработной платы всех сотрудников на аналогичной должности. The following example returns the names of employees whose salaries are equal to or greater than the average salary of all employees having the same job title. Для таблицы «Сотрудники» присваивается псевдоним «T1»: The Employees table is given the alias «T1»:
В приведенном выше примере зарезервированное слово AS не является обязательным. In the preceding example, the AS reserved word is optional.
Некоторые вложенные запросы поддерживаются в перекрестных запросах, в частности, в качестве предикатов (например в предложении WHERE). Some subqueries are allowed in crosstab queries— specifically, as predicates (those in the WHERE clause). Вложенные запросы в виде выходных данных (в списке SELECT) не поддерживаются в перекрестных запросах. Subqueries as output (those in the SELECT list) are not allowed in crosstab queries.
Пример Example
В данном примере перечислены имена и контактные данные каждого клиента, разместившего заказ во втором квартале 1995 года. This example lists the name and contact of every customer who placed an order in the second quarter of 1995. В этом примере выполняется вызов процедуры EnumFields, которую можно найти в примере для оператора SELECT. It calls the EnumFields procedure, which you can find in the SELECT statement example.
Как сделать запрос в sql access?
Приложение СУБД MS Access – это полноценный помощник для создания и ведения баз данных, заключенных в таблицы и массивы. Если база имеет слишком большой объем, быстро найти необходимые значения довольно сложно.
Именно поэтому в Access существует такая функция, как запросы. Рассмотрим, что это такое, как работает, какие имеет особенности.
Создание запросов в Microsoft Access
Чтобы разобраться, как создавать запросы в Access, нужно знать основные положения работы с СУБД.
Существует два способа выполнить данную процедуру:
Первый способ дает возможность создать любой из всех доступных запросов в ручном режиме, но с небольшой оговоркой, заключающейся в том, что пользователь имеет опыт работы с приложением Access. Также он должен разбираться хотя бы в основных его задачах. Что касается второго способа, то его нужно рассмотреть более подробно.
Легкий путь для новичков
Знающий человек за несколько кликов мышью выбирает те компоненты, которые потребуются пользователю для выполнения запроса, а затем быстро формирует реестр, в соответствии с собранными ключевыми значениями. Если это первое знакомство с СУБД, и пользователь не представляет, как создавать запросы в Access, то выбирается программа Мастер.
В данном режиме можно ознакомиться и разобраться со следующими типами запросов:
Данный выбор осуществляется уже на первом этапе работы с Мастером. А в дальнейшем, следуя четким указаниям, даже начинающий пользователь легко создаст запрос. Познакомимся с его разновидностями.
Простой запрос
Этот инструмент работы с таблицами собирает нужные данные из указанных пользователем полей. Уже по названию видно, что это самый популярный тип запросов для новичков. Его удобство заключается в том, что такая процедура открывается в новой вкладке. Поэтому ответ на вопрос, как создать запрос в Access 2010, становится очевидным уже после открытия первого меню Мастера.
Перекрестный запрос
Этот тип выборки более сложный. Чтобы разобраться, как создать перекрестный запрос в Access с помощью «Мастера» в данном режиме, нужно кликнуть по этой функции в первом окне.
На экране появится таблица, в которой можно выбрать до трех столбцов, расположенных в оригинале.
Одно из оставшихся не выбранных полей может быть использовано в качестве заголовков таблицы запроса. На третьем этапе процедуры (пересечение) выбирается еще одно значение с вариативностью функцию (среднее значение, сумма, первый, последний).
На фото показано, что перекрестный запрос создан, и что по заданным параметрам совершены необходимые действия.
Повторяющиеся записи
Как понятно из названия, основное предназначение данного запроса – выборка всех одинаковых строк в таблице по указанным параметрам. Выглядит это так:
Кроме того, доступен выбор дополнительных полей, чтобы подобрать соответствие сразу в нескольких строках.
Чтобы выбрать повторяющиеся записи, нужно раскрыть список запросов и создать там новую папку. Далее в окошке «Новый запрос» выбрать строку «Поиск повторяющихся записей». Далее нужно следовать указаниям Мастера.
Записи без подчиненных
Это последний тип запросов, доступный в режиме «Мастер – Записи без подчиненных».
В этом случае ведется выборка только тех значений, которые не задействованы ни в одном поле таблиц и запросов, но которые уже созданы.
Данный тип актуален только в случаях, когда баз данных несколько.
Все эти четыре типа запросов являются базовой точкой для работы со сложными элементами, но позволяют легко разобраться, как создать запрос в базе данных Access.
Функции запросов в MS Access
Разберемся, зачем нужно выполнять описанные выше действия. Задача всех простых и сложных запросов в СУБД Access заключается в следующем:
Запрос на выборку
Этот тип работы с базами данных является сложным, так как требует участия нескольких таблиц.
Необходимо, чтобы во всех таблицах были общие ключевые поля. В противном случае совершить операцию не получится.
Повторим, как создать запрос на выборку в Access. Сначала нужно создать простой запрос с выбором нужных полей. Уже здесь можно редактировать данные, чтобы привести их в желаемый вид. К слову, внесенные изменения перенесутся и в исходные таблицы, так что этот момент нужно учитывать.
В открывшемся окне конструктора заполняется окно «Добавление таблиц». Здесь нужно добавить те таблицы или запросы, из которых нужно вытаскивать исходные значения.
После добавления можно приступить к заполнению условий запроса. Для этого нам нужна строка «Поле». В ней нужно подобрать те значения из таблиц, которые будут отображаться при запросе.
Чтобы завершить операцию, нужно нажать на кнопку «Выполнить».
Запрос с параметрами
Это еще одна разновидность сложной процедуры, которая потребует от пользователя определенных навыков работы с базами данных. Одним из главных направлений такого действия является подготовка к созданию отчетов с объемными данными, а также получение сводных результатов. Как создавать запросы в Access 2007 с помощью конструктора, будет рассмотрено ниже.
Начинать данную процедуру по выборке данных нужно с создания простого запроса, чтобы выбрать нужные поля. Далее через режим Конструктора обязательно нужно заполнить поле «Условие отбора» и, уже исходя из внесенного значения, будет осуществляться отбор.
Таким образом, на вопрос о том, как создать запрос с параметром в Access, ответ простой — внести исходные параметры для выборки. Чтобы работать с Конструктором необходимо пользоваться Мастером запросов. Там создается первичные данные для фильтрации, которые служат основой дальнейшей работы.
Расширенный перекрестный запрос
Продолжаем усложнять ситуацию. Еще труднее для понимания является информация о том, как создавать запросы в Access, если присутствует несколько таблиц с данными. Перекрестный запрос уже рассматривался выше, как один из вариантов работы с Мастером. Однако, и в режиме «Конструктора» можно создавать подобный запрос.
Для этого необходимо нажать «Конструктор запросов» — «Перекрестный».
Открывается меню добавления исходных таблиц, а также возможность заполнения выборочных полей. Единственное, на что следует обратить внимание, – пункты «групповая операция» и «перекрестная таблица». Их нужно заполнять правильно, иначе процедура не будет выполнена корректно.
Перекрестные запросы – это наиболее простой способ поиска и выборки информации из нескольких источников данных, плюс с возможностью формирования диаграмм и графиков.
Более того, при использовании данной процедуры быстрее выполняется поиск, даже с несколькими вариантами развития.
Конечно, присутствуют и «подводные камни», которые могут помешать в работе. Например, при создании запроса на сортировку базы данных по значению столбцов система выдает ошибку. То есть доступна только сортировка по стандартным пунктам – «возрастание и убывание».
Краткие рекомендации
Подводя итоги, нужно сказать, что решить, как создавать запросы в Access – с помощью Мастера или Конструктора, должен сам пользователь. Хотя, для большинства людей, которые используют СУБД MS Access, больше подойдет первый вариант. Ведь Мастер сам сделает всю работу, оставив для пользователя только несколько кликов мышью, при выборе условий запроса.
Чтобы использовать расширенные настройки, явно необходим опыт работы с базами данных на уровне профессионала. Если в работе задействованы большие базы, лучше всего обратиться к специалистам, дабы избежать нарушения работы СУБД и возможных потерь данных.
Есть один момент, который доступен лишь программистам. Так как основным языком СУБД является SQL, то нужный запрос можно написать в виде программного кода. Чтобы работать в данном режиме, достаточно нажать на строку уже созданного запроса, и в открывшемся контекстном меню выбрать «Режим SQL».
В простейшем случае запрос реализует выбор из одной таблицы нужных полей, записей, соответствующих заданным условиям отбора, и просмотр результатов выполнения запроса.
Конструирование запросов на выборку с условиями отбора
Рассмотрим запросы на выборку в Access на примере получения информации из таблицы ТОВАР базы данных Поставка товаров.
Задача 1. Пусть необходимо выбрать ряд характеристик товара по его наименованию.
В результате выполненных действий в окне конструктора запросов (рис. 4.1) в верхней панели появится схема данных запроса, которая включает выбранные для данного запроса таблицы. В данном случае одну таблицу ТОВАР. Таблица представлена списком полей. Первая строка в списке полей таблицы, отмеченная звездочкой (*), обозначает все множество полей таблицы. Нижняя панель является бланком запроса, который нужно заполнить.
Кроме того, на ленте появляется и автоматически активизируется новая вкладка Работа с запросами | Конструктор (Query Tools | Design) (на рис. 4.3 представлена на часть этой вкладки), на которой цветом выделен тип созданного запроса — Выборка (Select). Таким образом, по умолчанию всегда создается запрос на выборку. Команды этой вкладки представляют инструментарий для выполнения необходимых действий при создании запроса. Эта вкладка открывается, когда в режиме конструктора создается новый запрос или редактируется существующий.
ЗАМЕЧАНИЕ
Окно запроса в режиме таблицы аналогично окну просмотра таблицы базы данных. Через некоторые таблицы запроса может производиться изменение данных базовой таблицы, лежащей в основе запроса. Запрос, просматриваемый в режиме таблицы, в отличие от таблицы базы данных Access 2010, не имеет столбца Щелкнуть для добавления (Click to Add), предназначенного для изменения структуры таблицы. В этом режиме на вкладке ленты Главная (Home) доступны те же кнопки, что и при открытии таблицы базы данных.
Задача 2. Пусть надо выбрать товары, цена которых не более 1000 руб., и НДС не более 10%, а также выбрать товары, цена которых более 2500 руб. Результат должен содержать наименование товара (НАИМ_ТОВ), его цену (ЦЕНА) и НДС (СТАВКА_НДС).
Задача 3. Пусть надо выбрать все накладные за заданный период. Результат должен содержать номер накладной (НОМ_НАК), код склада (КОД_СК), дату отгрузки (ДАТА_ОТГР) и общую стоимость отгруженного товара (СУММА_НАКЛ).
Для закрепления смотрим видеоурок:
Вычисляемые поля в запросах Access далее.
В MS Access можно создавать базы данных, таблицы, формы и другие отчеты. Эта статья поможет пользователю запускать SQL-запросы в MS Access. Можно выполнять те же запросы, которые используются в SQL для выборки данных из базы данных. Эта статья предназначена для пользователей, которые только что начали изучать MS Access и хотят выполнять SQL запросы в MS Access. Единственное условие, которое необходимо перед тем как приступить – это наличие доступа к базе данных, использующейся в организации.
Кликните Пуск—> Программы—> Microsoft Office—> Microsoft Office Access 2007. Приложение запуститься и откроется главное окно приложения.
Нажмите на кнопку Создать. Появится следующая страница. Нажмите на кнопку «Создать» выделенную зеленым цветом.
Пользователю предоставляются соответствующие варианты, после нажатия нажмите на кнопку «Создать» на странице выше. Теперь нажмите на выделенный пункт «Конструктор запросов»
Кликните «Закрыть» во всплывающем окне «Показать таблицы».
Перейдите к Инструментам запросов-> вкладка Конструктор и нажмите кнопку «Сквозная», как показано на рисунке ниже.
Нажмите на «Свойства», это одна из опций в «Инструментах запросов».
Свойство «Окно» появится на правой стороне экрана. Нажмите на текстовое поле «Строка подключения ODBC» и затем нажмите кнопку рядом с ODBC
Запрос SQL. Примеры в MS Access. SELECT: 11-20
Запрос Q011. Выводится информация о товарах из таблицы m_product, коды которых есть и в таблице m_income:
SELECT *
FROM m_product
WHERE id IN (SELECT product_id FROM m_income);
Запрос Q012. Выводится список товаров из таблицы m_product, кодов которых нет в таблице m_outcome:
SELECT *
FROM m_product
WHERE id NOT IN (SELECT product_id FROM m_outcome);
Запрос Q013. В этом запросе SQL выводится уникальный список кодов и названий товаров, коды которых есть в таблице m_income, но которых нет в таблице m_outcome:
SELECT DISTINCT product_id, title
FROM m_income INNER JOIN m_product
ON m_income.product_id=m_product.id
WHERE product_id NOT IN (SELECT product_id FROM m_outcome);
Запрос Q014. Выводится из таблицы m_category уникальный список категорий, названия которых начинаются на букву М:
SELECT DISTINCT title
FROM m_product
WHERE title LIKE ‘М*’;
Запрос Q015. Пример выполнения арифметических операций над полями в запросе и переименования полей в запросе (alias). В этом примере для каждой записи о расходе товара подсчитываются сумма расхода = количество*цена и размер прибыли, при предположении, что прибыль составляет 7 процентов от суммы продаж:
SELECT dt, product_id, amount, price, amount*price AS outcome_sum,
amount*price/100*7 AS profit
FROM m_outcome;
Запрос Q016. Проанализировав и упростив арифметические операции, можно увеличить скорость выполнения запроса:
SELECT dt, product_id, amount, price, amount*price AS outcome_sum,
outcome_sum*0.07 AS profit
FROM m_outcome;
Запрос Q017. При помощи инструкции INNER JOIN можно объединить данные нескольких таблиц. В следующем примере, в зависимости от значения ctgry_id, каждой записи таблицы m_income, сопоставляется название категории из таблицы m_category, к которой принадлежит товар:
SELECT c.title, b.title, dt, amount, price, amount*price AS income_sum
FROM (m_income AS a INNER JOIN m_product AS b ON a.product_id=b.id)
INNER JOIN m_category AS c ON b.ctgry_id=c.id
ORDER BY c.title, b.title;
SELECT SUM(amount*price) AS Total_Sum
FROM m_income;
Запрос Q019. Пример использования нескольких агрегатных функций:
SELECT Sum(amount) AS Amount_Sum, AVG(amount) AS Amount_AVG,
MAX(amount) AS Amount_Max, Min(amount) AS Amount_Min,
Count(*) AS Total_Number
FROM m_income;
Запрос Q020. В этом примере подсчитана сумма всех товаров с кодом 1, оприходованных в июне 2011 года:
SELECT Sum(amount*price) AS income_sum
FROM m_income
WHERE product_id=1 AND dt BETWEEN #6/1/2011# AND #6/30/2011#;.
Запрос Q021. Следующий запрос SQL вычисляет на какую сумму было продано товаров, имеющих код 4 или 6:
SELECT Sum(amount*price) as outcome_sum
FROM m_outcome
WHERE product_id=4 OR product_id=6;
Запрос Q022.Вычисляется на какую сумму было продано 12 июня 2011 года товаров, имеющих код 4 или 6:
Запрос Q023. Задача такова. Вычислить на какую общую сумму было оприходовано товаров категории «Хлебобулочные изделия».
Для решения этой задачи нужно оперировать тремя таблицами: m_income, m_product и m_category, потому что:
— количество и цена оприходованных товаров хранятся в таблице m_income;
— код категории каждого товара хранится в таблице m_product;
— название категории title хранится в таблице m_category.
Для решения данной задачи воспользуемся следующим алгоритмом:
— определение кода категории «Хлебобулочные изделия» из таблицы m_category посредством подзапроса;
— соединение таблиц m_income и m_product для определения категории каждого оприходованного товара;
— вычисление суммы прихода( = количество*цена) для товаров, код категории которых равен коду, определенному вышеуказанным подзапросом.
SELECT Sum(amount*price) AS income_sum
FROM m_product AS a INNER JOIN m_income AS b ON a.id=b.product_id
WHERE ctgry_id = (SELECT id FROM m_category WHERE title=’Хлебобулочные изделия’);
Запрос Q024. Задачу вычисления общей суммы оприходованных товаров категории «Хлебобулочные изделия» решим следующим алгоритмом:
— каждой записи таблицы m_income, в зависимости от значения его product_id, из таблицы m_category, сопоставить название категории;
— выделить записи, для которых категория равна «Хлебобулочные изделия»;
— вычислить сумму прихода = количество*цена.
SELECT Sum(amount*price) AS income_sum
FROM (m_product AS a INNER JOIN m_income AS b ON a.id=b.product_id)
INNER JOIN m_category AS c ON a.ctgry_id=c.id
WHERE c.title=’Хлебобулочные изделия’;
Запрос Q025. В этом примере вычисляется сколько наименований товаров было израсходовано:
SELECT COUNT(product_id) AS product_cnt
FROM (SELECT DISTINCT product_id FROM m_outcome) AS t;
Запрос Q026. Инструкция GROUP BY используется для группировки записей. Обычно записи группируются по значению одного или нескольких полей, и относительно каждой группы применяется какая-либо агрегатная операция. Например, следующий запрос состявляет отчет о продаже товаров. То есть генерируется таблица, в которой будут названия товаров и сумма, на которую они проданы:
SELECT title, SUM(amount*price) AS outcome_sum
FROM m_product AS a INNER JOIN m_outcome AS b
ON a.id=b.product_id
GROUP BY title;
Запрос Q027. Отчет о продажах по категориям. То есть генерируется таблица, в которой будут названия категорий товаров, общая сумма, на которую проданы товары данных категорий, и средняя сумма продаж. Функция ROUND использована для округления среднего значения до сотой доли (второй знак после разделителя целой и дробной частей):
SELECT c.title, SUM(amount*price) AS outcome_sum,
ROUND(AVG(amount*price),2) AS outcome_sum_avg
FROM (m_product AS a INNER JOIN m_outcome AS b ON a.id=b.product_id)
INNER JOIN m_category AS c ON a.ctgry_id=c.id
GROUP BY c.title;
Запрос Q028. Вычисляется для каждого товара общее и среднее количество его поступлений и выводит информацию о товарах, общее количество поступления которых не менее 500:
SELECT product_id, SUM(amount) AS amount_sum,
Round(Avg(amount),2) AS amount_avg
FROM m_income
GROUP BY product_id
HAVING Sum(amount)>=500;
Запрос Q029. В этом запросе вычисляется для каждого товара сумма и среднее значение его поступлений, осуществленных во втором квартале 2011 года. Если общая сумма прихода товара не менее 1000, то отображается информация об этом товаре:
SELECT title, SUM(amount*price) AS income_sum
FROM m_income a INNER JOIN m_product b ON a.product_id=b.id
WHERE dt BETWEEN #4/1/2011# AND #6/30/2011#
GROUP BY title
HAVING SUM(amount*price)>=1000;
Запрос Q030. В некоторых случаях нужно сопоставлять каждой записи некоторой таблицы каждую запись другой таблицы; что называется декартовым произведением. Таблица, образующаяся в результате такого соединения, называется таблицей Декарта. Например, если некоторая таблица А имеет 100 записей и таблица В имеет 15 записей, то их таблица Декарта будет состоять из 100*15=150 записей. Следующий запрос соединяет каждую запись таблицы m_income с каждой записью таблицы m_outcome:
SELECT *FROM m_income, m_outcome;
Запрос Q031. Пример группирования записей по двум полям. Следующий запрос SQL вычисляет по каждому поставщику сумму и количество поступивщих от него товаров:
SELECT supplier_id, product_id, SUM(amount) AS amount_sum,
SUM(amount*price) AS income_sum
FROM m_income AS a INNER JOIN m_product AS b ON a.product_id=b.id
GROUP BY supplier_id, product_id;
Запрос Q032. Пример группирования записей по двум полям. Следующий запрос вычисляет для каждого поставщика сумму и количество его продуктов, проданных нами:
SELECT supplier_id, product_id, SUM(amount) AS amount_sum,
SUM(amount*price) AS outcome_sum
FROM m_outcome AS a INNER JOIN m_product AS b ON a.product_id=b.id
GROUP BY supplier_id, product_id;
Запрос Q033. В этом примере два вышеприведенных запроса (q031 и q032) использованы как подзапросы. Результаты этих запросов методом LEFT JOIN объединены в один отчет. Следующий запрос выводит отчет о количестве и сумме поступивщих и реализованных продуктов по каждому поставщику. Следует обратить внимание на то, что если какой-то товар уже поступил, но еще не реализован, то клетка outcome_sum для этой записи будет пустой. Также необходимо отметить, что данный запрос служит только примером использования относительно сложных запросов в качестве подзапроса. Производительность данного запроса SQL при большом объеме данных сомнительна:
SELECT *
FROM
(SELECT supplier_id, product_id, SUM(amount) AS amount_sum,
SUM(amount*price) AS income_sum
FROM m_income AS a INNER JOIN m_product AS b
ON a.product_id=b.id GROUP BY supplier_id, product_id) AS a
LEFT JOIN
(SELECT supplier_id, product_id, SUM(amount) AS amount_sum,
SUM(amount*price) AS outcome_sum
FROM m_outcome AS a INNER JOIN m_product AS b
ON a.product_id=b.id GROUP BY supplier_id, product_id) AS b
ON (a.product_id=b.product_id) AND (a.supplier_id=b.supplier_id);
Запрос Q034. В этом примере два вышеприведенных запроса (q031 и q032) использованы как подзапросы. Результаты этих запросов методом RIGTH JOIN объединены в один отчет. Следующий запрос выводит отчет о сумме платежей каждого клиента по использованным им платежным системам и сумме сделанных им инвестиций. Следующий запрос выводит отчет о количестве и сумме поступивщих и реализованных продуктов по каждому поставщику. Следует обратить внимание на то, что если какой-то товар уже реализован, но еще не поступил, то клетка income_sum для этой записи будет пустой. Наличие таких пустых клеток является показателем ошибки в учете продаж, так как до продажы сначала необходимо, чтобы соответствующий товар поступил:
SELECT *
FROM
(SELECT supplier_id, product_id, SUM(amount) AS amount_sum,
SUM(amount*price) AS income_sum
FROM m_income AS a INNER JOIN m_product AS b ON a.product_id=b.id
GROUP BY supplier_id, product_id) AS a
RIGHT JOIN
(SELECT supplier_id, product_id, SUM(amount) AS amount_sum,
SUM(amount*price) AS outcome_sum
FROM m_outcome AS a INNER JOIN m_product AS b ON a.product_id=b.id
GROUP BY supplier_id, product_id) AS b
ON (a.supplier_id=b.supplier_id) AND (a.product_id=b.product_id);
Запрос Q035. Выводится отчет о сумме доходов и расходов по продуктам. Для этого создается список продуктов по таблицам m_income и m_outcome, затем для каждого продукта из этого списка вычисляется сумма его приходов по таблице m_income и сумма его расходов по таблице m_outcome:
SELECT product_id, SUM(in_amount) AS income_amount,
SUM(out_amount) AS outcome_amount
FROM
(SELECT product_id, amount AS in_amount, 0 AS out_amount
FROM m_income
UNION ALL
SELECT product_id, 0 AS in_amount, amount AS out_amount
FROM m_outcome) AS t
GROUP BY product_id;
Запрос Q036. Функция EXISTS возвращает значение TRUE, если переданное ей множество содержит элементы. Функция EXISTS возвращает значение FALSE, если переданное ей множество пустое, то есть не содержит элементов. Следующий запрос выводит коды товаров, которые содержатся как в таблице m_income, так и в таблице m_outcome:
SELECT DISTINCT product_id
FROM m_income AS a
WHERE EXISTS(SELECT product_id FROM m_outcome AS b
WHERE b.product_id=a.product_id);
Запрос Q037. Выводятся коды товаров, которые содержатся как в таблице m_income, так и в таблице m_outcome:
SELECT DISTINCT product_id
FROM m_income AS a
WHERE product_id IN (SELECT product_id FROM m_outcome)
Запрос Q038. Выводятся коды товаров, которые содержатся как в таблице m_income, но не содержатся в таблице m_outcome:
SELECT DISTINCT product_id
FROM m_income AS a
WHERE NOT EXISTS(SELECT product_id FROM m_outcome AS b
WHERE b.product_id=a.product_id);
Запрос Q039. Выводится список товаров, сумма продаж которых максимальная. Алгоритм таков. Для каждого товара вычисляется сумма его продаж. Затем, определяется максимум этих сумм. Затем, для каждого товара снова вычисляется сумма его продаж, и выводятся код и сумма продаж товаров, сумма продаж которых равна максимальной:
SELECT product_id, SUM(amount*price) AS amount_sum
FROM m_outcome
GROUP BY product_id
HAVING SUM(amount*price) = (SELECT MAX(s_amount)
FROM (SELECT SUM(amount*price) AS s_amount FROM m_outcome GROUP BY product_id));
Запрос Q040. Зарезервированное слово IIF (условный оператор) используется для оценки логического выражения и выполнения того или иного действия в зависимости от результата (TRUE или FALSE). В следующем примере поставка товара считается «малой», если количество меньше 500. В противном случае, то есть количество поступления больше или равно 500, поставка считается «большой»:
SELECT dt, product_id, amount,
IIF(amount =500,»большая») AS mark
FROM m_income;
Запрос Q042. В следующем запросе если количество товара в поступившей партии меньше 300, то партия считается «малой». В противном случае, то есть если условие amount =1000,»большая») AS mark
FROM m_income;
Запрос SQL Q044. В следующем запросе продажи разделяются на три группы: малые (до 150), средние (от150 до 300), большие (300 и более). Далее, для каждой группы вычисляется итоговая сумма:
SELECT Category, SUM(outcome_sum) AS Ctgry_Total
FROM (SELECT amount*price AS outcome_sum,
IIf(amount*price lifedays,»Да»,»Нет») AS date_expire
FROM m_income a INNER JOIN m_product b
ON a.product_id=b.id;
Запрос SQL Q049. Вычисляются количество месяцев со дня поступления товара до текущей даты. В столбце month_last1 вычисляется абсолютное количество месяцев, в столбце month_last2 вычисляется количество полных месяцев:
SELECT dt, DateDiff(«m»,dt,Date()) AS month_last1,
DateDiff(«m»,dt,Date())-iif(day(dt)>day(date()),1,0) AS month_last2
FROM m_income;
Запрос SQL Q050. Выводится поквартальный отчет о количестве и сумме оприходованных товаров за 2011 год:
SELECT kvartal, SUM(outcome_sum) AS Total
FROM (SELECT amount*price AS outcome_sum, month(dt) AS m,
SWITCH(m =10,4) AS kvartal
FROM m_income WHERE year(dt)=2011) AS t
GROUP BY kvartal;
Запрос Q051. Следующий запрос помогает выяснить, удалось ли пользователям ввести в систему информацию о расходе товара на сумму большую, чем сумма прихода товара: