Запросы, содержащие вычисления на выделенных записях таблицы (по столбцам)
Часто требуется выполнить вычисления над определенным набором данных и, основываясь на них, составить статистику. Для этого нужно добавить в запрос условия для выборки нужного набора записей.
Например, мы можем изменить предыдущий запрос таким образом, чтобы получить данные не по всем клиентам, а только по тем, которые находятся в Германии. Для этого:
В предыдущем запросе, добавляем поле "Страна" из таблицы в бланк запроса. В строке Групповая операция (Total) для этого поля появляется элемент Группировка (Group by).
Заменяем элемент Группировка (Group by) на элемент Условие (Where), выбрав его из списка. При этом автоматически сбрасывается флажок (Show) в данном столбце. Это означает, что в результат запроса поле "Страна" не попадет. Если вы попытаетесь установить этот флажок, то при выполнении запроса будет выведено сообщение об ошибке. Действительно, если мы хотим, чтобы поле "Страна" отобразилось в результате запроса, его нужно добавить в бланк запроса второй раз, при этом в строке Групповая операция (Total) данного столбца нужно оставить элемент Группировка (Group by) и установить флажок Вывод на экран (Show).
Вводим слово Германия в ячейку Условие отбора того столбца "Страна", который не выводится на экран.
Для вычисления каких-либо значений по столбцам используются статистические функции по подмножеству из построителя выражений (DCount, DSum, DMax и др.)
Рассмотрим пример обработки запроса сотрудника с максимальным числом хобби
Такой запрос к БД возможно реализовать двумя способами – программно и с помощью MS Access
1ый способ – программно.
Для начала необходимо создать запрос, который будет считать количество хобби сотрудников (конечно, «программно» можно обойтись и без запроса вообще – в Delphi посчитать количество хобби каждого сотрудника, но это будет, достаточно трудоемко, поэтому необходимо просто пользоваться уже созданными инструментами).
Создадим запрос в режиме конструктора – добавим в него 3 таблицы (HOBBY, HOBBY_SOTR, SOTR) – рисунок 29.
Рисунок 29 – Создание запроса в режиме конструктора
Из таблицы “Сотрудники” выберем поля – S_ID S_Fam, S_Name, S_Otch, а из таблицы HOBBY – поле H_Name.
Далее из контекстного меню правой клавиши мыши выбираем «Групповые операции» и в поле H_Name выбираем Count (рисунок 30).
Рисунок 30 – Подсчет количества хобби сотрудников
Завершающий этап построения запроса – сортировка. Т.к. нам необходимо найти максимальное число хобби сотрудников, то при сортировке записей по убыванию поля H_Name самое верхняя запись (или несколько верхних при их равенстве) и будет искомой.
Теперь обработаем программно результаты запроса (из Delphi).
При обращении к запросу сначала выполняем стандартные действия по обращению к БД:
//подключение к БД DM.ADOConnection1.Connected:=true; //очистка текста запроса DM.RepADOQuery.SQL.Clear; |
Далее выводим, например в DBGrid1 результат запроса (имя которого – «Запрос6»):
DM.RepADOQuery.SQL.Text:='Select * FROM [Запрос6]'; DM.RepADOQuery.Open; |
После этого посчитаем количество записей, имеющих одинаковое значение числа хобби:
dbgrid1.DataSource.DataSet.First; sum:=DBGrid1.Fields[4].AsInteger; //sum – максимальное число хобби count:=0; //count – количество сотрудников с максимальным числом хобби while dbgrid1.Fields[4].AsInteger=sum do begin count:=count+1; dbgrid1.DataSource.DataSet.Next; end; |
Имея значение числа первых строк, которые удовлетворяют запросу, выводим их на экран:
DM.RepADOQuery.SQL.Clear; DM.RepADOQuery.SQL.Text:='Select TOP '+inttostr(count)+' * FROM [Запрос6];'; DM.RepADOQuery.Open; |
Результаты запроса представлены на рисунке 31.
Рисунок 31 – Результат запроса сотрудников с максимальным числом хобби
2ой способ – с помощью MS Access.
1. Реализация конечного запроса в MS Access, а затем просто извлечение результата запроса в Delphi является менее трудоемкой задачей.
Для этого, после создания вышеописанного “Запроса 6”, необходимо создать еще один запрос, который будет обращаться к этому и находить записи с максимальным количеством хобби. Назовем его DMax (т.к. выполняться он будет через эту функцию).
В режиме Конструктора создания запроса выберем все записи из “Запроса 6” и в поле “Count-H_Name” строки Условие отбора из контекстного меню правой клавиши выбираем “Построить”. Далее выполняем действия, показанные на рисунке 32 (выбираем функцию DMax, прописываем строку выражения).
Запускаем запрос и проверяем его на правильность работы.
Рисунок 32 – Реализация функции DMax
Теперь необходимо просто обратиться к созданному запросу из Delphi. Делаем это уже привычным нам способом:
//подключение к БД DM.ADOConnection1.Connected:=true; //очистка текста запроса DM.RepADOQuery.SQL.Clear; DM.RepADOQuery.SQL.Text:='Select * FROM DMax'; DM.RepADOQuery.Open; |
Результат выполнения запроса представлен на рисунке 33.
Рисунок 33 – Результат выполнения запроса, реаолизованного с помощью функции DMax
Создание запросов за определенный промежуток времени
При необходимости выводить отчет за определенный промежуток времени, можно создать еще одну форму, на которую поместить два компонента DateTimePicker (вкладка Win32), соответствующие дате начала и конца, а также кнопку, при нажатии на которую, во-первых, происходит проверка корректности ввода дат, а, во-вторых, появляется форма основного отчета. Форма может выглядеть, например, так, как на рисунке 34.
Рисунок 34 – Форма для ввода диапазона дат