Использование критериев отбора

2.2. Использование критериев отбора

Допустим, у нас есть таблица «Заказчики» со следующими полями:

№ п/п

Название

Город

Адрес

К_во_заказов

Если необходимо выбрать из нее не все записи, а только те, которые удовлетворяют некоторым условиям. Например, только тех из них, которые размещены в Харькове. Для этого используется инструкция WHERE. Условий может быть несколько, тогда они объединяются логическими операциями И, ИЛИ, ИСКЛЮЧАЮЩЕЕ ИЛИ (XOR). Для инвертирования части или всего условия (т.е. отбора тех записей, которые данному условию не удовлетворяют) используется операция NOT.

Общий синтаксис простого запроса с условием таков:

SELECT Список_полей FROM Список_Таблиц

WHERE (условие1) лог_оп (условие2) лог_оп (условие3);

Пример:

SELECT * FROM Заказчики WHERE (Город=’Харьков’);

SELECT * FROM Заказчики WHERE (Город=’Харьков’) AND (К_во_заказов > 5);

2.3. Объединение нескольких источников данных

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

Допустим, к нашей базе данных присоединена внешняя таблица «Реестр», в которой хранятся сведения обо всех субъектах предпринимательской деятельности в Украине. А нам надо просмотреть все сведения только о наших заказчиках. Опять-таки, всех. Для таких случаях существует инструкция JOIN – объединение таблиц по одному полю. Различают внутренние (INNER) и внешние (LEFT, RIGHT) объединения. Мы рассмотрим лишь внутренние – это наиболее жизненная ситуация.

Общий вид объединения таков:

SELECT (список_полей_главной_таблицы) FROM (Главная_таблица) <Вид_объединения> JOIN (Подчиненная_таблица) ON (Главная.Поле1 = Подчиненная.Поле1)

Итак, таблица «Реестр» имеет следующую структуру:

Название

Код_ЕДРПОУ

Расч_счет

ИНН

Теперь присоединим ее к таблице «Заказчики»:

SELECT * FROM Заказчики INNER JOIN Реестр ON (Заказчики.Название = Реестр.Название)

В результате мы будем иметь источник записей с количеством записей, равным размеру таблицы «Заказчики», и содержащий все поля таблиц «Заказчики» и «Реестр». Так же, как и в конструкции WHERE, объединение может быть по нескольким полям, с использованием различных условий, однако следует иметь в виду, что использование нестандартных (неподдерживаемых конструктором запросов) действий может привести к непредсказуемым результатам.

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

SELECT * FROM Заказчики INNER JOIN Реестр ON (Заказчики.Название = Реестр.Название)

WHERE (Город=’Харьков’) AND (К_во_заказов > 5);

будет воспринят MS Access вполне нормально.

Возможна и другая ситуация.

Допустим, у нас есть таблица «Сотрудники_офиса» следующей структуры:

Таб_номер

ФИО

Телефон

Также есть таблица «Сотрудники_филиала» с точно такой же структурой. Необходимо в каком-нибудь итоговом отчете представить эти таблицы вместе. Для этого используется конструкция UNION. При ее использовании можно склеить сколько угодно таблиц. Точнее, таблицы необязательно должны иметь одинаковую структуру. Необходимо лишь, чтобы из всех таблиц выбиралось одинаковое количество полей, и чтобы эти поля были полностью эквивалентны.

Общая структура такого запроса такова:

SELECT Таблица1.Поле1, Таблица1.ПолеN FROM Таблица1

UNION SELECT Таблица2.Поле1, Таблица2.ПолеN FROM Таблица2

UNION SELECT ТаблицаK.Поле1,ТаблицаK.ПолеN FROM ТаблицаK;

В нашем случае это будет выглядеть так:

SELECT Сотрудники_офиса.* FROM Сотрудники_офиса

UNION SELECT Сотрудники_филиала.* FROM Сотрудники_филиала

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

Иногда бывает необходимо объединить несколько таблиц (более 2) с помощью операции JOIN. Сделать это в рамках одного запроса весьма проблематично, однако не следует забывать, что MS Access в состоянии использовать запросы как источник записей. Т.е. объедините две таблицы в одном запросе, а затем обхедините третью таблицу и запрос во втором.

2.4. Групповые операции и вычисляемые поля

Допустим, есть у нас таблица «Постоянные_клиенты». И нам, в зависимости от количества покупок надо дать им скидки – например, те, кто сделал более 5 покупок, получает скидку 6%. Исходная таблица имеет такой вид:

Номер

ФИО

Покупки

Чтобы выполнять подобные операции, не прибегая к искусству программирования, в запросах Microsoft Jet (именно так называется ядро баз данных Access) реализована возможность реализации вычисляемых полей.

Объявляются эти поля точно так же, как и обычные, в конструкции SELECT:

SELECT(список_обычных_полей), (операция_с_полями_или_ числа-ми) AS вычисляемое_поле FROM (список_таблиц);

Применительно к нашей ситуации имеет смысл использовать операцию IIf – Если. Ее формат таков: IIf(условие;действие_если_истина; действие_ если_ложь). Можно также использовать вложенные условия – их нужно вставлять вместо действий – IIf(условие; IIf(вложенное_условие; действие_если_истина;действие_если_ложь); действие_если_ложь)

Наш запрос будет выглядеть так:

SELECT *, (IIf(Покупки > 5; 0.06; 0)) AS Скидка From Постоянные_клиенты;

Он даст нам источник записей, который содержит все поля и записи таблицы «Постоянные_клиенты», а в придачу к ним – уже рассчитанные скидки в соответствующем поле.

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

Access поддерживает также групповые (итоговые) операции. Это значит, что можно рассчитать, исходя из набора данных, некоторые статистические и другие характеристики, такие как сумма, дисперсия, математическое ожидание и другие. В запросах этим пользуются не очень часто, но пользуются. Поля с групповыми вычислениями объявляются так же, как и обычные вычисляемые поля. Например, запрос

SELECT Sum(Покупки) AS Количество FROM Постоянные_клиенты;

Вернет нам одну-единственную запись с общим числом покупок, сделанным всеми постоянными клиентами.

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

Итак, есть две таблицы: «Ассортимент», содержащая список товаров, которыми торгует гипотетическая фирма «Рога_и_копыта», и безграмотно составленная таблица «Склад», в которую при нахождении каждого экземпляра товара добавляли запись. Надо сделать так, чтобы данные из этих таблиц отобразались наглядно.

Таблица «Ассортимент» содержит поля «ID_товара» и «Наименование», а связанная с ней таблица «Склад» - только «ID_товара».

Вначале создадим запрос «Склад_гр», который сгруппирует повторяющиеся записи в таблице «Склад» и выведет количество повторений для каждой записи. Для этого применим групповую операцию Count и конструкцию GROUP BY.

В общем виде группировка выглядит так:

SELECT (список_полей) FROM (список_таблиц) GROUP BY (список_полей);

В нашем случае мы получим такой запрос:

SELECT ID_товара, Count(ID_товара) AS Количество FROM Склад

GROUP BY ID_товара;

Этот запрос даст нам уже сгруппированную таблицу, и мы легко сможем увидеть, сколько экземпляров каждого товара лежит на складе. Но теперь желательно было бы просмотреть наименования товаров, лежащих на складе. Для этого воспользуемся уже знакомой операцией INNER JOIN.

SELECT Количество FROM Склад_гр INNER JOIN Ассортимент ON (Склад_гр.ID_товара = Ассортимент.ID_товара);

Возвращенный таким запросом источник записей будет иметь следующую структуру:

Количество

ID_товара

Наименование

Как раз то, что нужно.