Читай также Условия отбора данных в запросе 1С
Конструктор запроса 1С - это один из инструментов разработки, который позволяет:
- составить текст запроса на языке запросов исключительно визуальными средствами;
- отредактировать текст имеющегося запроса.
Программный код обычный конструктор не создает.
Конструктор запроса с обработкой результата - это один из инструментов разработки, который позволяет:
- сформировать текст нового запроса;
- сформировать программный код, содержащий создание объекта встроенного языка "Запрос", текста запроса, получение и обработку результата выполнения запроса (фрагмент программного кода, который исполняет запрос и выводит его результаты в табличный документ или диаграмму - отсюда);
- отредактировать текст имеющегося запроса.
Конструктор запросов может использоваться:
- разработчиком - в конфигураторе (необходимо знание языка запросов);
- пользователем - в режиме 1С:Предприятие (как обработка "Консоль запросов").
Использование в режиме 1С:Предприятие позволяет квалифицированным пользователям самостоятельно получать выборки данных, для которых нет типовых отчетов.
Вызов конструктора текста запроса 1С:
- из вкладки "Наборы данных" схемы компоновки данных (кнопка выше поля "Запрос");
- контекстным меню (правой кнопкой мыши) в нужном месте программного кода;
- с помощью команды "Конструктор запроса..." меню "Текст".
С помощью кнопок "Далее" и "Назад" можно перемещаться по закладкам конструктора и указывать, какие данные должны присутствовать в результате запроса, как они связаны, сгруппированы, какие итоги следует рассчитать, работать с временными таблицами, редактировать пакет запросов:
Результатом работы конструктора будет являться синтаксически правильный текст запроса.
Таким образом, разработчик может составить работоспособный запрос, даже не владея синтаксисом языка запросов - необходимые синтаксические конструкции конструктор сгенерирует автоматически. Готовый текст запроса может быть сразу же вставлен в текст модуля или скопирован в буфер обмена.
Кроме этого конструктор запросов позволяет редактировать уже имеющийся в программе текст запроса. Для этого достаточно установить курсор внутри существующего текста запроса и вызвать конструктор. Имеющийся текст запроса будет проанализирован и представлен в конструкторе в виде соответствующих выбранных полей базы данных и набора заданных связей, группировок, условий и т.д.
Текст запроса может содержать описание предопределенных данных конфигурации:
- значения системных перечислений (из определенного перечня);
- предопределенных данных (справочники, перечисления, планы видов характеристик, планов счетов, планов видов расчета);
- пустые ссылки;
- значения точек маршрута бизнес процессов.
Вкладки конструктора запроса 1С
На этой вкладке указывается источник данных и поля, которые необходимо выводить в отчет (по сути описываются конструкции ВЫБРАТЬ … ИЗ).
В качестве источников данных могут служить:
- физическая таблица базы данных;
- виртуальная таблица регистров;
- временные таблицы;
- вложенные запросы и т.д.
В контекстном меню виртуальных таблиц можно задать параметры этих таблиц.
На закладке "Таблицы и поля" конструктора запросов задают:
- источники данных запросов, в качестве которых могут выступать реальные и виртуальные таблицы;
- набор полей из таблиц - источников данных запроса;
- поля в виде произвольного выражения.
Закладка "Связи" становится доступна сразу после добавления двух таблиц.
На закладке "Связи" конструктора запросов можно определить соединение таблиц-источников данных и связи между ними.
Язык запросов предоставляет широкие возможности по построчному соединению нескольких таблиц.
Соединение таблиц может быть:
- безусловным;
- условным (или по ключу), то есть по заданному условию связи:
- полным - все записи обеих таблиц; для тех, которым не нашлось соответствия, запрос возвращает NULL;
- левым/правым - все записи из соответственно левой/правой таблицы (из противоположной только те, которым есть соответствие), для не найденных запрос возвращает NULL. Примечание: консоль запросов всегда преобразует правое соединение в левое, меняя таблицы местами (в случае, если запрос написать вручную, и потом открыть конструктором);
- внутренним - только совпадающие записи обеих таблиц.
В случае безусловного соединения, в конструкторе запросов достаточно определить выбираемые таблицы, не указывая, по каким полям они будут связаны (то есть не заполняя закладку Связи).
На практике чаще встречаются задачи соединения по определенному полю двух таблиц.
В конструкторе запросов тип условного соединения регулируется флажком "Все" - где он стоит, из той таблицы и выбираются все записи.
Флажок "Все", устанавливаемый у таблицы, выбранной на закладке "Связи" конструктора запросов означает что в результат запроса надо включить все записи этой таблицы.
При соединении таблиц-источников данных в конструкторе запросов можно назначить необходимое количество соединений с указанием необходимого количества условий связи, причем эти условия могут быть как простые, так и произвольные.
На данной вкладке система позволяет "группировать" (фактически - сворачивать таблицу) и суммировать нужные поля результата таблицы. Описывается использование конструкций СГРУППИРОВАТЬ ПО, СУММА, МИНИМУМ, СРЕДНЕЕ, МАКСИМУМ, КОЛИЧЕСТВО, КОЛИЧЕСТВО РАЗЛИЧНЫХ.
При описании группировки в тексте запроса все поля должны делиться:
- на те, по которым производится группировка;
- на агрегатные функции (рассчитываемые поля);
- на поля вложенных таблиц.
Вложенные таблицы не попадают ни в группируемые, ни в агрегируемые поля.
Количество записей в результате запроса можно узнать с помощью агрегатной функции КОЛИЧЕСТВО(*).
Отвечает за все условия, накладываемые на получаемые данные, т.е. за всё, что идёт в тексте запроса после конструкции ГДЕ.
Вкладка "Дополнительно" включает важные параметры.
Группировка "Выборка записей":
- Первые — параметр, возвращающий в запрос только N записей (оператор ПЕРВЫЕ ); позволяет вывести в отчет первые N записей, которые будут отобраны с учетом правил упорядочивания, настроенных в конструкторе запросов;
- Без повторяющихся — обеспечивает уникальность полученных записей (оператор РАЗЛИЧНЫЕ) и позволяет исключить из результирующей таблицы запроса одинаковые строки (данная операция распространяется только на детальные записи);
- Разрешенные — позволяет выбирать только те записи, которые позволяет выбрать система с учетом настройки RLS (конструкция РАЗРЕШЕННЫЕ).
Группировка "Тип запроса":
- определяет тип запроса:
- Выборка данных;
- Создание временной таблицы;
- Уничтожение временной таблицы.
Флаг "Блокировать получаемые данные для последующего изменения":
- позволяет включить возможность установки блокировки данных, которая обеспечивает сохранность данных от момента их чтения до изменения (актуально только для "Автоматического режима блокировок", конструкция ДЛЯ ИЗМЕНЕНИЯ); позволяет заблокировать данные указанных таблиц-источников на чтение (в рамках транзакции).
- в режиме автоматических транзакционных блокировок) происходят блокировки:
- в варианте файл-сервер - на уровне таблиц базы данных;
- в варианте клиент-сервер - на уровне записей таблиц базы данных.
- Если список "Таблицы для изменения" - пуст, то будут блокироваться данные всех таблиц, задействованных в запросе, иначе - только таблиц, указанных в списке.
Конструктор запросов 1с позволяет создавать объединения запросов. При их помощи можно последовательно выводить в результат данные, полученные из нескольких запросов, не используя при этом связи. Единственной условие для объединения — одинаковый набор полей в каждом отдельном запросе.
На этой закладке устанавливается возможность объединения разных таблиц и псевдонимы (конструкция КАК).
В левой части указываются таблицы, если установить флаги напротив таблицы, будут использоваться конструкции ОБЪЕДИНИТЬ, иначе — ОБЪЕДИНИТЬ ВСЕ (отличия двух способов тут).
В правой части указываются соответствия полей в разных таблицах, если соответствие не указано, запрос будет возвращать NULL.
Особенности использования закладки "Объединения/Псевдонимы" конструктора запросов:
- На закладке "Объединения/Псевдонимы" конструктора запросов можно:
- Задать список запросов (в результирующую выборку попадут записи всех указанных запросов).
- Задать имена полей результирующей таблицы.
- При создании запросов, участвующих в объединении, с помощью конструктора запросов, он сам добавит в каждый запрос необходимое количество недостающих полей со значением NULL. Настроить соответствие добавленных конструктором полей и полей результата запроса можно на закладке "Объединения/Псевдонимы".
- Настройки, сделанные в разделе "Объединения/Псевдонимы" конструктора запросов, отразятся на информации, отображаемой в других разделах конструктора, следующим образом:
- Если заданы псевдонимы полей результирующей таблицы, то новые названия появляются на закладке "Порядок" в списке полей.
- При объединении запросов на ряде закладок (в том числе "Таблицы и поля") появляется возможность переключения между объединяемыми запросами.
- При объединении двух запросов на закладке "Объединения/Псевдонимы" конструктора запросов условие связи между запросами задать нельзя: в случае объединения связь всегда безусловна.
Используя конструктор запроса, повлиять на порядок записей в результирующей таблице запроса можно:
- на закладке "Порядок";
- на закладке "Объединения/Псевдонимы".
Таблица, полученная в результате запроса, может быть отсортирована по алфавиту по нужному полю:
- по полю, присутствующему в запросе;
- по любому полю таблицы-источника (кроме случаев объединенных запросов).
Помимо указания порядка для конкретного поля, можно воспользоваться опцией "Автоупорядочивание", которая отсортирует список по основному представлению объекта.
Указывается порядок сортировки значений (УПОРЯДОЧИТЬ ПО) — по убыванию (УБЫВ) или возрастанию (ВОЗР).
Флаг "Автоупорядочивание" (в запросе — АВТОУПОРЯДОЧИВАНИЕ):
- если установлен, то система будет сортировать данные (по умолчанию система 1С 8.3 выводит данные в «хаотичном» порядке).
- записи в результирующей таблице запроса будут упорядочены по полям сортировки по умолчанию для таблиц-источников данных;
- если заданы настройки на станицах "Группировка" или "Итоги", то порядок записей в результирующей таблице запроса будет определяться этими настройками.
В случае установки флага "Автоупорядочивание" на закладке "Порядок" конструктора запросов записи в результирующей таблице запроса (если эта таблица уже упорядочена по полю, хранящему ссылку на иерархический справочник) будут упорядочены по значению основного представления для этого поля. Т.е. если справочник отсортирован по ссылке (= наименованию), но основное его представление - код, то после включения автоупорядочивания сортировка изменится на сортировку по коду.
В зависимости от иерархии справочника, можно выводить итоги связанных таблиц по иерархии. Для этого предназначена закладка "Итоги" конструктора запросов:
В ней указывается:
- Поле, по которому группируем, и тип его итогов:
- Элементы - итоги только по конечным элементам;
- Элементы и иерархия - итоги по папкам и по конечным элементам;
- Только иерахия - итоги только по папкам.
- Флаг "Общие итоги" - суммирование всех полученных запросом строк;
- Итоговое поле - по которому собственно и считаем итоги.
Таким образом, при использовании секции "ИТОГИ ... ПО" количество записей в результате запроса увеличится (по сравнению с их количеством без секции "ИТОГИ ... ПО").
Результатом запроса с итогами будет дерево значений:
На этой вкладке можно создавать новые пакеты запросов, а также использовать её для навигации.
В тексте запроса пакеты разделяются символом «;» (точка с запятой).
Особенности использования конструктора запросов 1С
- При сохранении текста запроса, открытого повторно с помощью конструктора запроса, без внесения в этот текст изменений, из первоначального текста будут удалены только комментарии.
- Для создания в конструкторе запросов вложенного запроса необходимо:
- открыть закладку "Таблицы и поля";
- в командной панели над деревом "Таблицы" нажать кнопку "Добавить" или выбрать одноименный пункт контекстного меню.
Рисунок "Создание в конструкторе запросов вложенного запроса"
[свернуть] -
Особенности использования вложенного запроса 1С
Фирма 1С рекомендует использовать вложенные запросы в исключительных случаях и предлагает заменять их временными таблицами или соединениями таблиц, замечая при этом, что результат такого изменения может быть другим. Такая рекомендация объясняется тем, что при использовании вложенных запросов оптимизатор СУБД не всегда может правильно определить размер выборки вложенного запроса и построить оптимальный план обращений к физическим таблицам базы данных, что сильно (иногда в десятки раз) может замедлить выполнение запроса.
[свернуть]
- Для создания в конструкторе запросов вычисляемого поля необходимо:
- открыть закладку "Таблицы и поля";
- в командной панели над деревом "Поля" нажать кнопку "Добавить" или выбрать одноименный пункт контекстного меню.
- Для создания в запросе строк, сгруппированных с помощью агрегатных функций, на закладке "Группировка" конструктора запросов обязательно должен быть заполнен только список суммируемых полей, при этом остальные поля автоматически попадут в секцию СГРУППИРОВАТЬ ПО в тексте запроса.
Рисунок
Укажем в конструкторе одно суммируемое поле:
По остальным полям будет произведена группировка в тексте запроса:
[свернуть] - Условия на значения агрегатных функций можно накладывать на закладке "Условия" (например, можно прописать функцию языка запросов, которая сделает отбор по агрегатам).
-
При использовании в качестве источника данных для запроса внешнего источника (данные которого передаются через параметр) в качестве такого источника может использоваться:
- таблица значений;
- табличная часть;
- результат запроса.
- При написании текста запроса в качестве источника данных можно использовать внешний источник (передаваемый как параметр) только если в этом запросе создается временная таблица.
- Вложенный запрос может использоваться:
- в качестве таблицы-источника данных;
- в качестве операнда операций сравнения "В" или "НЕ В" при задании параметров виртуальной таблицы;
- в качестве операнда операций сравнения "В" или "НЕ В" при задании конструкции языка запросов "ГДЕ".
По остальным полям будет произведена группировка в тексте запроса:
Оператор вхождения "В" имеет вид:
|
1 |
<Выражение> В (<Список значений>) |
Оператор вхождения является одним из операторов сравнения, определенных в языке запросов 1С:Предприятия, и принимает значение "Истина", если значение его левого операнда присутствует в списке значений из правого операнда.
Список значений может задаваться в виде:
- списка выражений;
- параметра, значением которого является коллекция значений;
- вложенного запроса.
Если список значений задан вложенным запросом, то возможно использование обобщенной формы оператора вхождения - группового оператора вхождения.
Групповой оператор вхождения принимает значение "Истина", если запись (<Выражение 1>, ..., <Выражение N>) совпадает с одной из записей выборки, определяемой вложенным запросом.
Например, пусть левым операндом является список:
|
1 |
(1, 2, 3) |
Если результатом вложенного запроса является выборка:
|
1 2 3 |
1, 1, 1 2, 2, 2 2, 2, 3 |
то результатом группового оператора вхождения будет "Ложь", а если результатом вложенного запроса является выборка:
|
1 2 3 |
1, 1, 1 1, 2, 3 2, 2, 3 |
то результатом группового оператора вхождения будет "Истина".
Наряду с оператором вхождения ("В") в языке запросов предусмотрен оператор не вхождения ("НЕ В"), результатом которого является отрицание оператора "В" с такими же операндами. Все, сказанное про особенности выполнения оператора "В", относится и к оператору "НЕ В".
Особенности построения запросов с учетом иерархии справочников
В случае, если справочник иерархичен, среди его стандартных реквизитов доступен "Родитель", то есть вышестоящий элемент:
По нему и нужно вести отбор, если требуется выбрать позиции в пределах подчинения.
Варианты построения запроса с ограничением выборки по иерархии:
При этом если внутри родителя есть дальнейшие уровни вложенности, в выборку они не попадут. Для этого, достаточно указать, в пределах какого родителя нужна выборка:
Для этого используется оператор В ИЕРАРХИИ:
При этом, в качестве аргумента оператора можно задать как одно значение, так и список; но в качестве источника списка должен выступать вложенный запрос:
|
1 2 3 4 5 6 7 8 9 10 |
ВЫБРАТЬ Справочник.Номенклатура.Наименование ГДЕ Справочник.Номенклатура.Ссылка В ИЕРАРХИИ ( ВЫБРАТЬ Справочник.Номенклатура.Ссылка ГДЕ Справочник.Номенклатура.Наименование = "Одежда" ) |
Виртуальные таблицы регистров накопления
Платформа формирует виртуальные таблицы:
- для остаточных регистров накопления:
- Остатки;
- Обороты;
- Остатки и обороты;
- для оборотных регистров накопления:
- Обороты (только).
Виртуальная таблица остатков для расчета данных использует:
- всегда - таблицу итогов и
- иногда - таблицу движений.
Использование таблицы движений зависит:
- от момента времени, на который считаются остатки, и
- периода рассчитанных итогов.
При расчете остатков используется довольно простая стратегия:
- Подбирается ближайший больший или равный момент времени, на который рассчитаны остатки.
- На этот момент времени получаются остатки из таблицы итогов.
- Если момент времени, на который считаются остатки, не совпадает с моментом времени итогов, то остатки рассчитываются по движениям за период с момента запроса остатков по момент итогов.
Рассмотрим несколько примеров (отсюда).
Пусть период рассчитанных итогов равен 31.07.2004. Мы хотим получить остатки:
- на 01.07.2004, 15.07.2004, 01.08.2004, 15.08.2004 и
- актуальные остатки.
Для случаев получения остатков на 01.07.2004, 01.08.2004 и актуальных остатков данные будут получены непосредственно из таблицы итогов.
В случае получения остатков на 15.07.2004 сначала будут получены данные из таблицы итогов на момент времени 01.08.2004, так как это ближайший больший момент времени, на который посчитаны остатки, а затем будут обработаны данные из таблицы движений за период с 15.07.2004 по 31.07.2004 включительно.
В случае получения остатков на 15.08.2004, ближайшим большим моментом времени, на который посчитаны остатки является момент актуальных остатков. Таким образом, для расчета остатков на 15.08.2004, будут получены актуальные итоги и обработаны данные таблицы движений начиная с 15.08.2004.
Виртуальная таблица оборотов всегда работает по данным таблицы движений.
То есть для получения оборотов за какой-либо период будут обработаны данные таблицы движений за этот период, независимо от периода рассчитанных итогов.
Виртуальная таблица остатков и оборотов рассчитывает одновременно и остатки, и обороты.
В зависимости от того, указана периодичность или нет, изменяется способ работы данной таблицы:
- Если периодичность не указана, то расчет данных производится единым запросом, который в свою очередь содержит подзапросы. Один из них вычисляет остатки на начальный момент периода, как это описано для виртуальной таблицы остатков, второй -обороты за заданный период, как это описано для виртуальной таблицы оборотов. Результаты подзапросов объединяются и выдаются как единый результат.
- В случае если периодичность задана, расчет данных разбивается на следующие шаги:
- Получение остатков на начало заданного периода.
- Получение оборотов с заданной периодичностью за заданный период.
- Объединение данных двух запросов.
Виртуальные таблицы не существуют физически в базе данных, они генерируются на уровне платформы для повышения быстроты и удобства доступа к данным.
Перед построением виртуальных таблиц, данные можно отобрать по параметрам, набор которых различен для разных таблиц:
- для таблицы "Остатков":
- период остатков;
- произвольное Условие.
Рисунок
[свернуть]
- для таблицы "Оборотов":
- начало периода;
- конец периода;
- периодичность;
- условие.
Рисунок
[свернуть]
- для таблицы "Остатков и оборотов":
- начало периода;
- конец периода;
- периодичность;
- метод дополнения;
- условие.
Рисунок
[свернуть]
Период (Начало периода, Конец периода)
Могут принимать значения типа:
- Дата;
- Момент времени;
- Граница.
ВАЖНО! В случае использования типа "Дата" либо "Момент времени" (комбинации "Даты" и "Ссылки") сама секунда времени, указанная в поле выборки, не учитывается в выборке данных! Выборка строится до (после) заданного момента.В случае использования типа "Граница", этот нюанс настраивается, так как можно указать тип границы (показывать данные включая или исключая заданную секунду).
Периодичность
Позволяет определить период, к которому будут приведены записи. Например, если периодичность "Год", то все записи будут показаны как на 01 января.
Вариант периодичности "Авто" позволяет в одном запросе привести данные к нескольким периодам. В конструкторе запроса при этом становятся доступны все варианты периода, можно выбрать нужные.
Метод дополнения
Определяет, строить запрос:
- только на основании движений за период (вариант Движения);
- учитывать и начальные остатки (вариант ДвиженияИГраницыПериода).
Отличие на выходе в следующем:
- если, например по номенклатуре, были остатки на начало, но не было оборотов за период, то в варианте "Движения" она не попадет в выборку;
- в варианте ДвиженияИГраницыПериода - попадет с нулевым оборотом.
Условие
Позволяет наложить произвольное условие на измерение регистра.