1с 8.2 зиуп вложенные запросы примеры. Вложенные запросы. Причины неоптимальной работы запросов

Данная статья рассчитана на читателей, которые знакомы с языком SQL.

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

Ниже представлена таблица соответствия основных операторов языка запросов и SQL:

И это далеко не полный список. Более полную справочную информацию по доступным операторам языка запросов можно получить в конструкторе запросов, о котором будет рассказано ниже.

Выполнение запроса 1С из программного кода осуществляется при помощи объекта встроенного языка «Запрос». Пример написания запроса к базе данных с использованием встроенного языка программирования:

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

Параметры запроса хранятся в свойстве «Параметры» (в данном случае это структура, поэтому все методы структуры тут применимы – вставить, удалить и т.д.).

Пример установки параметра «Запрос.Параметры.Вставить» («Справочник», СправочникСсылка). В запросе обратиться к параметрам можно через амперсанд «&Справочник». Ниже пример запроса с использованием параметров:

Напомним, что язык запросов предназначен только для чтения данных из базы, поэтому в нем отсутствуют аналоги таких операторов SQL, как INS ERT и UPDATE. Данные можно модифицировать только через объектную модель встроенного языка программирования 1С. Также в языке запросов 1С существуют операторы, аналогов которых нет в SQL, например:

• В ИЕРАРХИИ
• ПОМЕСТИТЬ
• ИНДЕКСИРОВАТЬ ПО

В ИЕРАРХИИ – позволяет выбрать все элементы иерархического справочника, которые входят в иерархию переданной ссылки. Пример запроса с использованием В ИЕРАРХИИ :

В данном случае в результат вернутся все подчиненные элементы справочника номенклатуры «Цитрусовые», неважно, сколько уровней иерархии есть у данного справочника.

Также, к примеру, стоит задача найти товар с именем «Ручка». Товар должен входить в иерархию «Канц. Товаров», то есть нам не надо искать дверную ручку. Структура номенклатуры в этом случае такова:

Канцелярия

Фурнитура

Пишем такой запрос:

При использовании конструкции В ИЕРАРХИИ необходимо учитывать, что если в параметр «Канцелярия» передать пустую ссылку, выполнение запроса замедлится, так как платформа будет проверять каждый элемент на принадлежность корню.

ПОМЕСТИТЬ – Данный оператор помещает результат во временную таблицу. Пример запроса:

Данный запрос на SQL будет выполнен несколькими запросами:

• Создание временной таблицы (платформа умеет «переиспользовать» ранее созданные временные таблицы, поэтому создание происходит не всегда);
• Помещение данных во временную таблицу;
• Выполнение основного запроса, а именно SEL ECT из этой временной таблицы;
• Уничтожение/очистка временной таблицы.

Временная таблица может быть уничтожена явно, через конструкцию УНИЧТОЖИТЬ , либо неявно – при закрытии менеджера временных таблиц.

У объекта «Запрос» встроенного языка программирования есть свойство «МенеджерВременныхТаблиц», которое предназначено для работы с временными таблицами. Пример кода:

После выполнения запроса переменную МВТ можно использовать второй раз в другом запросе, что, несомненно, является еще одним плюсом использования временных таблиц. В данном случае временная таблица из базы будет удалена при вызове метода «Закрыть»…

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

ИНДЕКСИРОВАТЬ ПО – этот оператор применяется совместно с оператором ПОМЕСТИТЬ. При создании временной таблицы этим оператором можно проиндексировать создаваемую таблицу, что существенно ускоряет работу с ней (но только, если индекс подходит под ваш запрос).

Бесплатная консультация эксперта

Спасибо за Ваше обращение!

Специалист 1С свяжется с вами в течение 15 минут.

Особенности некоторых операторов языка запросов

ДЛЯ ИЗМЕНЕНИЯ – данный оператор предназначен для блокировки определенной таблицы запроса (или всех таблиц, которые участвуют в запросе). Блокировка осуществляется наложением U блокировки на таблицу. На SQL это реализуется через hint UPDLOCK. Данная конструкция необходима для предотвращения блокировок типа deadlock. Пример запроса с конструкцией ДЛЯ ИЗМЕНЕНИЯ:

В данном примере U блокировка будет установлена на таблицу «Пользователи». Если не указывать таблицу для блокировки, она будет наложена на все таблицы, участвующие в запросе. Важно отметить, что данная конструкция работает только в конфигурациях, в которых включен автоматический режим управления блокировками.

СОЕДИНЕНИЕ – запрос поддерживает соединения ЛЕВОЕ/ПРАВОЕ, ПОЛНОЕ, ВНУТРЕННЕЕ, что соответствует соединениям в SQL – LEFT/RIGHT JOIN, OUTER JOIN, INNER JOIN.

Однако при использовании конструктора запросов вы не сможете сделать ПРАВОЕ СОЕДИНЕНИЕ. Конструктор просто будет менять местами таблицы, но оператор будет всегда левый. По этой причине в 1С никогда не встретишь применения правого соединения.

Синтаксически соединение выглядит так:

В языке запросов 1С отсутствует оператор для соединения декартова произведения (CROSS JOIN). Однако отсутствие оператора не означает, что язык запросов не поддерживает такого соединения. Соединить таблицы при необходимости можно таким образом:

Как видно из примера, задан ключ соединения ПО ИСТИНА , то есть каждая строка одной таблицы соответствует строке другой. Тип соединения (ЛЕВОЕ, ПРАВОЕ, ПОЛНОЕ, ВНУТРЕННЕЕ) не важен, если у вас есть строки в обеих таблицах, но если в какой-то из таблиц нет строк (пуская таблица) – результат будет отличаться. Например, при использовании ВНУТРЕННЕЕ соединение результат будет пустой. При использовании ЛЕВОЕ/ПРАВОЕ соединение в результате будет или не будет данных в зависимости от того, к какой таблице мы присоединяемся – с данными или нет. При использовании ПОЛНОГО соединения данные будут всегда (естественно, только одной таблицы, так как в другой пустота), выбор типа соединения зависит от конкретной прикладной задачи.

Небольшая визуальная подсказка, как работают различные типы соединений:

ПОДОБНО. В отличие от аналогичного оператора языка SQL – LIKE, шаблон для ПОДОБНО можно задать, используя только некоторые спец символы:

• % (процент): последовательность, содержащая любое количество произвольных символов;
• _ (подчеркивание): один произвольный символ;
• / - следующий символ нужно интерпретировать как обычный символ.

ИТОГИ ПО аналогом на SQL можно назвать оператор ROLLUP. Пример использования оператора ИТОГИ:

Результат будет такой:

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

Работа с пакетными запросами

1С позволяет работать с пакетами запросов. В пакетном запросе тексты запросов разделяются точкой с запятой (;). Выполнение пакетного запроса 1С осуществляется последовательно. Пример текста пакетного запроса:

Для получения результата всех запросов, входящих в пакет, необходимо воспользоваться методом объекта запроса «ВыполнитьПакет», вместо «Выполнить». Данный метод последовательно выполняет все запросы. Результат запроса – массив результатов для каждого запроса из пакета, а последовательность расположения в массиве такая же, как последовательность запросов в тексте пакета.

Рассматривая язык запросов, стоит упомянуть о такой особенности, как виртуальные таблицы. Виртуальные таблицы отсутствуют в базе данных, это своеобразная обертка, которая выполняется на стороне СУБД как запрос с использованием подзапросов. Пример запроса 1С с использованием виртуальных таблиц:

Такой запрос на СУБД будет выглядеть так:

Видно, что не похоже на SQL, поскольку есть подзапрос, группировка. Виртуальные таблицы, по большому счету – это «синтаксический сахар», то есть созданы, в общем-то, для удобства разработки запросов, чтобы запросы были компактнее и читабельнее.

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

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

В тексте запроса это выглядит так:

Для удобства написание запросов, то есть создания текстов запросов, в 1С существует конструктор, который можно вызвать через контекстное меню (правой кнопкой мыши):

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

Конструктор запросов является очень гибким визуальным инструментом для создания запросов любой сложности. Он доступен только в режиме конфигуратора. В режиме Предприятия есть так называемая «Консоль запросов» – это внешняя обработка, поставляемая на диске ИТС. Для управляемого приложения консоль запросов можно скачать на сайте its.1c.ru.

Описание работы в конструкторе запросов выходит за рамки тематики данной статьи, поэтому подробно рассматриваться не будет.

Причины неоптимальной работы запросов

Ниже приведен список основных причин (но не всех), которые приводят к замедлению выполнения запроса.

• Использования соединения с подзапросами

Не рекомендуется выполнять соединение с подзапросами, подзапросы необходимо заменить временными таблицами. Соединение подзапросов может приводить к значительной потере в производительности, при этом выполнение запроса на разных СУБД может значительно разниться в скорости. Скорость выполнения таких запросов также чувствительна к статистике в СУБД. Причина такого поведения в том, что оптимизатор СУБД не всегда корректно может определить оптимальный план выполнения запросов, так как оптимизатор ничего не знает о том, какое количество строк вернет подзапрос после своего выполнения.

• Использование виртуальных таблиц в соединениях запроса

Виртуальные таблицы на уровне СУБД выполняются как подзапросы, поэтому причины такие же, как в первом пункте.

• Использование условий в запросе, неподходящих под существующие индексы

Если в условиях запроса (в операторе ГДЕ или в условиях виртуальной таблицы) используются поля, которые не все входят в индекс, данный запрос будет выполнен с использованием SQL конструкции table scan или index scan (полностью или частично). Это скажется не только на времени выполнения запроса, но также будет наложена избыточная S блокировка на лишние строки, что в свою очередь может привести к эскалации блокировок, то есть будет заблокирована вся таблица.

• Использование ИЛИ в условиях запроса

Использование логического оператора ИЛИ в конструкции ГДЕ может также приводить к сканированию таблицы (table scan). Это происходит из-за того, что СУБД не может корректно использовать индекс. Вместо ИЛИ можно применить конструкцию ОБЪЕДИНИТЬ ВСЕ.

• Получение данных через точку для полей составного типа

Не рекомендуется получать значения через точку (в конструкции ВЫБРАТЬ, ГДЕ ), поскольку если реквизит объекта окажется составным типом, соединение будет происходить с каждой таблицей, входящей в этот составной тип. В результате запрос на СУБД будет значительно усложнен, это может помешать оптимизатору в выборе корректного плана выполнения запроса.

Конструктор запросов в 1С 8.3 и 8.2 — мощнейший инструмент разработки. Он позволяет составить текст запроса при помощи специальной визуальной среды. Таким образом, чтобы создать запрос 1с не обязательно знать встроенный язык запросов, достаточно ориентироваться в не сложном и интуитивно понятном интерфейсе конструктора.

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

Описание конструктора запросов на официальном сайте 1С 8: v8.1c.ru

Таблицы и поля; ; ; ; ; ; Вложенные запросы (в разработке).

Для того чтобы вызвать конструктор запросов 1с 8 в программном коде необходимо:

• Создать новый запрос

• Задать пустую строку текста запроса

• Поставить курсор мышки между кавычками, нажать правую кнопку мыши. В открывшемся контекстном меню выбрать пункт Конструктор запроса и ответить Да на вопрос о создании нового запроса. Если текст запроса уже записан, то необходимо щелкнуть на любом месте внутри него и вызвать конструктор;

Рассмотрим на небольших примерах с возрастающей сложностью все основные вкладки конструктора запросов. Такой подход позволит начинающему программисту 1с более эффективно изучить конструктор и все его возможности. Для примеров будем использовать конфигурацию Бухгалтерия 3.0.

Урок №1. Конструктор запросов — простейший пример использования.

Задача: написать запрос к справочнику номенклатура, выбрать всю номенклатуру справочника.

Новые вкладки: Таблицы и поля.

Новые механизмы: просмотр и редактирование текста запроса при помощи кнопки «Запрос».

Для начала создания запроса создадим новый запрос и вызовем конструктор (как это делается написано несколькими абзацами выше). После этого откроется окно конструктора на вкладке Таблицы и поля.

Теоретическая часть урока №1

Вкладка Таблицы и поля состоит из трех разделов:

База данных . В данном разделе представлены все таблицы базы данных, которые можно использовать для построения запроса;

Таблицы . В данный раздел выбираются таблицы необходимые для данного запроса. Для тогда чтобы переместить их из раздела база данных нужно:

• Либо дважды щелкнуть по таблице;
• Либо воспользоваться кнопками «>» или «>>».

Над разделом Таблицы присутствует ряд кнопок. Про большинство из них будет подробнее рассказано в следующих уроках. А пока дам только краткие пояснения.

• Создать вложенный запрос (красная линия). Предназначена для создания нового вложенного запроса;
• Создать описание временной таблицы (желтая линия). Позволяет задать имя временной таблицы, которая расположена вне данного запроса, также можно использовать для передачи в запрос таблицы значений;
• Изменить текущий элемент (зеленая линия). Позволяет перейти в выделенный вложенный запрос, временную таблицу или описание временной таблицы;
• Удалить текущий элемент (голубая линия). Удаляет выделенную таблицу из выбранных таблиц;
• Заменить таблицу (синяя линия). Открывает диалог замены выделенной таблицы. Полезно, если вы неверно выбрали виртуальную таблицу регистра, так как происходит позиционирование на текущей выбранной таблице в списке.
• Параметры виртуальной таблицы (фиолетовая линия). Открывает параметры виртуальной таблицы регистра.

Поля . В данный раздел выбираются поля таблиц из предыдущего раздела. Эти поля будут колонками таблицы или выборки полученной в результате выполнения запроса. Нужны они прежде всего, для того чтобы получить из выбранных таблиц только нужную в конкретном случае информацию. Для того чтобы переместить их из раздела Таблицы необходимо:

• Либо дважды щелкнуть по полю;
• Либо воспользоваться кнопками «>» или «>>»;
• Также можно добавить новое поле самостоятельно, используя произвольное выражение из полей выбранных таблиц и функций языка запросов.

Над разделом Поля присутствует ряд кнопок. Про создание полей при помощи произвольных выражений будет подробнее рассказано в следующих уроках. А пока дам только краткие пояснения.

• Добавить (зеленая линия). Предназначена для добавления нового поля при помощи редактора произвольных выражений;
• Изменить текущий элемент (красная линия). Позволяет изменить выделенное поле при помощи редактора;
• Удалить текущий (синяя линия). Удаляет выделенное поле из списка.

Практическая часть урока №1

Мы разобрались с теорией необходимой для выполнения задания, предусмотренного в данном уроке. Напомню как оно звучит: написать запрос к справочнику номенклатура, выбрать всю номенклатуру справочника.

Приступим к созданию запроса по номенклатуре:

• Создадим новый запрос и откроем конструктор методом указанным в начале урока;
• В разделе База данных , откроем ветку Справочники и найдем там справочник Номенклатура;
• Выделим его и при помощи кнопки «>» перенесем в раздел Таблицы;
• В разделе Таблицы раскроем справочник номенклатура при помощи значка «+»;
• В раскрывшемся списке полей найдем поле Ссылка и перенесем его в раздел Поля при помощи кнопки «>»
• Запрос по номенклатуре готов, нажимаем кнопку «ОК» в нижней части окна конструктора.

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

Основные достоинства языка запросов 1С 8.3 (8.2) перед SQL:

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

Недостатки языка запросов в 1С:

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

Рассмотрим наш мини учебник по основным конструкциям языка запросов 1С.

В связи с тем, что запросы в 1С позволяют лишь получать данные, любой запрос должен начинаться со слова «ВЫБРАТЬ». После этой команды указываются поля, данные из которых нужно получить. Если указать «*», то будут выбраны все доступные поля. Место, откуда будут выбираться данные (документы, регистры, справочники и прочее) указывается после слова «ИЗ».

В рассмотренном ниже примере выбираются наименования всей номенклатуры из справочника «Номенклатура». После слова «КАК» указываются псевдонимы (имена) для таблиц и полей.

ВЫБРАТЬ
Номенклатура.Наименование КАК НаименованиеНоменклатуры
ИЗ
Справочник.Номенклатура КАК Номенклатура

Рядом с командой «ВЫБРАТЬ» можно указать ключевые слова:

• РАЗЛИЧНЫЕ . Запрос будет отбирать только отличающиеся хотя бы по одному полю строки (без дублей).
• ПЕРВЫЕ n , где n – количество строк с начала результата, которые необходимо отобрать. Чаще всего такая конструкция используется совместно с сортировкой (УПОРЯДОЧИТЬ ПО). Например, когда нужно отобрать определенное количество последних по дате документов.
• РАЗРЕШЕННЫЕ . Данная конструкция позволяет выбирать из базы только те записи, которые доступны текущему пользователю. Баз использования этого ключевого слова пользователю будет выведено сообщение об ошибке при попытке обращения запроса к тем записям, доступа к которым у него нет.

Эти ключевые слова могут использоваться как все вместе, так и по отдельности.

ДЛЯ ИЗМЕНЕНИЯ

Это предложение блокирует данные для исключения взаимных конфликтов. Заблокированные данные не будут считываться из другого соединения до окончания транзакции. В данном предложении можно указывать конкретные таблицы, которые нужно заблокировать. В противном случае будут заблокированы все. Конструкция актуальна лишь для режима автоматических блокировок.

Чаще всего предложение «ДЛЯ ИЗМЕНЕНИЯ» используется при получении остатков. Ведь при одновременной работе нескольких пользователей в программе, пока один получает остатки, другой может их изменить. В таком случае полученный остаток будет уже не верен. Если же заблокировать данные этим предложением, то пока первый сотрудник не получит корректный остаток и не совершит с ним все необходимые манипуляции, второй сотрудник будет вынужден ждать.

ВЫБРАТЬ
Взаиморасчеты.Сотрудник,
Взаиморасчеты.СуммаВзаиморасчетовОстаток
ИЗ
РегистрНакопления.ВзаиморасчетыССотрудниками.Остатки КАК Взаиморасчеты
ДЛЯ ИЗМЕНЕНИЯ

ГДЕ (WHERE)

Конструкция необходима для наложения какого-либо отбора на выгружаемые данные. В некоторых случая получения данных из регистров разумнее прописывать условия отборов в параметрах виртуальных таблиц. При использовании «ГДЕ», сначала получаются все записи, и только потом применяется отбор, что значительно замедляет выполнение запроса.

Ниже приведен пример запроса получения контактных лиц с определенной должностью. Параметр отбора имеет формат: &ИмяПараметра (имя параметра произвольное).

ВЫБОР (CASE)

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

В приведенном ниже примере «ДополнительноеПоле» будет содержать текст в зависимости от того проведен документ или нет:

ВЫБРАТЬ
ПоступлениеТиУ.Ссылка,
ВЫБОР
КОГДА ПоступлениеТиУ.Проведен
ТОГДА «Документ проведен!»
ИНАЧЕ «Документ не проведен…»
КОНЕЦ КАК ДополнительноеПоле
ИЗ
Документ.ПоступлениеТоваровУслуг КАК ПоступлениеТиУ

СОЕДИНЕНИЕ (JOIN)

Соединения связывают две таблицы по определенному условию связи.

ЛЕВОЕ/ПРАВОЕ СОЕДИНЕНИЕ

Суть ЛЕВОГО соединения заключается в том, что полностью берется первая указанная таблица и к ней по условию связи привязывается вторая. Если записей, соответствующих первой таблице во второй не нашлось, то в качестве их значений подставляется NULL. Проще говоря, главной является первая указанная таблица и к её данным уже подставляются данные второй таблицы (если они есть).

Например, необходимо получить номенклатурные позиции из документов «Поступление товаров и услуг» и цены из регистра сведений «Цены номенклатуры». В данном случае, если цена у какой-либо позиции не найдена, вместо нее подставиться NULL. Из документа все позиции будут выбраны вне зависимости от того, есть ли на них цена или нет.

ВЫБРАТЬ
ПоступлениеТиУ.Номенклатура,
Цены.Цена
ИЗ
Документ.ПоступлениеТоваровУслуг.Товары КАК ПоступлениеТиУ
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних КАК Цены
ПО ПоступлениеТиУ.Номенклатура = Цены.Номенклатура

В ПРАВОМ все в точности да наоборот.

ПОЛНОЕ СОЕДИНЕНИЕ

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

При использовании в предыдущем примере полного соединения будут выбраны все позиции номенклатуры из документа «Поступление товаров и услуг» и все последние цены из регистра «Цены номенклатуры». Значения не найденных записей, как в первой, так и во второй таблице будут равняться NULL.

ВНУТРЕННЕЕ СОЕДИНЕНИЕ

Отличием ВНУТРЕННЕГО соединения от ПОЛНОГО является то, что если хотя бы в одной из таблиц не найдена запись, то запрос не выведет ее вообще. В результате будут выбраны только те номенклатурные позиции из документа «Поступление товаров и услуг», для которых в регистре сведений «Цены номенклатуры» есть записи, если в предыдущем примере заменить «ПОЛНОЕ» на «ВНУТРЕННЕЕ».

СГРУППИРОВАТЬ ПО (GROUP BY)

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

Результатом следующего запроса будет список видов номенклатуры с максимальными ценами по ним.

ВЫБРАТЬ
,
МАКСИМУМ(Цены.Цена) КАК Цена
ИЗ

СГРУППИРОВАТЬ ПО
Цены.Номенклатура.ВидНоменклатуры

ИТОГИ

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

Итоги можно подводить по всей таблице целиком (с использованием ключевого слова «ОБЩИЕ»), по нескольким полям, по полям с иерархической структурой (ключевые слова «ИЕРАРХИЯ», «ТОЛЬКО ИЕРАРХИЯ»). При подведении итогов не обязательно использовать агрегатные функции.

Рассмотрим пример, аналогичный примеру выше с использованием группировки. В данном случае результат запроса вернет не только сгруппированные поля, но и детальные записи.

ВЫБРАТЬ
Цены.Номенклатура.ВидНоменклатуры КАК ВидНоменклатуры,
Цены.Цена КАК Цена
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних КАК Цены
ИТОГИ
МАКСИМУМ(Цена)
ПО
ВидНоменклатуры

ИМЕЮЩИЕ (HAVING)

Данный оператор схож с оператором «ГДЕ», но используется только для агрегатных функций. Остальные поля, кроме используемых этим оператором, должны быть сгруппированы. Оператор «ГДЕ» не применим для агрегатных функций.

В рассмотренном ниже примере отбираются максимальные цены номенклатуры, если они превышают 1000, сгруппированные по виду номенклатуры.

ВЫБРАТЬ

МАКСИМУМ(Цены.Цена) КАК Цена
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних КАК Цены
СГРУППИРОВАТЬ ПО
Цены.Номенклатура.ВидНоменклатуры
ИМЕЮЩИЕ
МАКСИМУМ(Цены.Цена) > 1000

УПОРЯДОЧИТЬ ПО

Оператор «УПОРЯДОЧИТЬ ПО» сортирует результат запроса. Для того, чтобы гарантированно выводить записи в постоянном порядке, используется АВТОУПОРЯДОЧИВАНИЕ. Примитивные типы сортируются по обычным правилам. Ссылочные типы сортируются по GUID.

Пример получения списка сотрудников, отсортированного по наименованию:

ВЫБРАТЬ
Сотрудники.Наименование КАК Наименование
ИЗ
Справочник.Сотрудники КАК Сотрудники
УПОРЯДОЧИТЬ ПО
Наименование
АВТОУПОРЯДОЧИВАНИЕ

Прочие конструкции языка запросов 1С

• ОБЪЕДИНИТЬ – результаты двух запросов в один.
• ОБЪЕДИНИТЬ ВСЕ – аналог ОБЪЕДИНИТЬ, но без группировки одинаковых строк.
• ПУСТАЯ ТАБЛИЦА – иногда используется при объединении запросов для указания пустой вложенной таблицы.
• ПОМЕСТИТЬ – создает временную таблицу для оптимизации сложных запросов 1С. Такие запросы называются пакетными.

Функции языка запросов

• ПОДСТРОКА обрезает строку с определенной позиции на указанное количество символов.
• ГОД…СЕКУНДА позволяют получить выбранное значение числового типа. Входным параметром является дата.
• НАЧАЛОПЕРИОДА и КОНЕЦПЕРИОДА используются при работе с датами. В качестве дополнительного параметра указывается тип периода (ДЕНЬ, МЕСЯЦ, ГОД и т. п.).
• ДОБАВИТЬКДАТЕ позволяет прибавить или отнять от даты указанное время определенного типа (СЕКУНДА, МИНУТА, ДЕНЬ и т. п.).
• РАЗНОСТЬДАТ определяет разницу между двумя датами с указанием типа выходного значения (ДЕНЬ, ГОД, МЕСЯЦ и т. п.).
• ЕСТЬNULL заменяет отсутствующее значение на указанное выражение.
• ПРЕДСТАВЛЕНИЕ и ПРЕДСТАВЛЕНИЕССЫЛКИ получают строковое представление указанного поля. Применяются для любых значений и только ссылочных соответственно.
• ТИП, ТИПЗНАЧЕНИЯ используются для определения типа входного параметра.
• ССЫЛКА является логическим оператором сравнения для типа значения реквизита.
• ВЫРАЗИТЬ используется для преобразования значения к нужному типу.
• ДАТАВРЕМЯ получает значение типа «Дата» из числовых значений (Год, Месяц, День, Час, Минута, Секунда).
• ЗНАЧЕНИЕ в запросе 1С используется для указания предопределенных значений — справочников, перечислений, планов видов характеристик. Пример использования: «Где ЮрФизЛицо = Значение(Перечисление.ЮрФизЛица.ФизЛицо) «.

Конструктор запросов

Для создания запросов с 1С есть очень удобный встроенный механизм – конструктор запросов. Он содержит следующие основные вкладки:

• «Таблицы и поля» — содержит поля, которые необходимо выбрать и их источники.
• «Связи» — описывает условий для конструкции СОЕДИНЕНИЕ.
• «Группировка» — содержит описание конструкций группировок и суммируемых полей по ним.
• «Условия» — отвечает за отборы данных в запросе.
• «Дополнительно» — дополнительные параметры запроса, такие как ключевые слова команды «ВЫБРАТЬ» и пр.
• «Объединения/Псевдонимы» — указываются возможности объединения таблиц и задаются псевдонимы (конструкция «КАК»).
• «Порядок» — отвечает за сортировку результата запросов.
• «Итоги» — аналогична вкладке «Группировка», но применяется для конструкции «ИТОГИ».

Текст самого запроса можно просмотреть, нажав в левом нижнем углу на кнопку «Запрос». В данной форме его можно откорректировать вручную или скопировать.

Консоль запросов

Для быстрого просмотра результата запроса в режиме «Предприятие», либо отладки сложных запросов используется . В ней пишется текст запроса, устанавливаются параметры, и показывается его результат.

Скачать консоль запросов можно на диске ИТС, либо по .

В этой статье мы хотим обсудить с Вами все функции языка запросов 1с , а также конструкции языка запросов . Чем же отличается функция от конструкции? Функция вызывается со скобками и возможными параметрами в них, а конструкция пишется без скобок. Безусловно все конструкции и функции языка запросов 1с делают процесс получения данных гибким и многофункциональным. Данные функции и конструкции применимы к полям запроса, а некоторые также применимы в условиях.

Функции языка запросов 1с

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

1. Функция ДАТАВРЕМЯ - данная функция создает константное поле с типом "Дата".

Синтаксис: ДАТАВРЕМЯ(<Год>,<Месяц>,<День>,<Час>,<Минута>,<Секунда>)

Пример использования:

2. Функция РАЗНОСТЬДАТ - возвращает разность двух дат в одном из измерений (год, месяц, день, час, минута, секунда). Измерение передается в параметре.

Синтаксис: РАЗНОСТЬДАТ(<Дата1>, <Дата2>, <Тип>)

Пример использования:

Запрос.Текст = "ВЫБРАТЬ | РАЗНОСТЬДАТ(ДАТАВРЕМЯ(2015, 4, 17), ДАТАВРЕМЯ(2015, 2, 1), ДЕНЬ) | КАК КолвоДней";

3. Функция ЗНАЧЕНИЕ - задает константное поле с предопределенной записью из базы данных, также можно получить пустую ссылку любого типа.

Синтаксис: ЗНАЧЕНИЕ(<Имя>)

Пример использования:

Запрос.Текст = "ВЫБРАТЬ //предопределенный элемент | ЗНАЧЕНИЕ(Справочник.Валюты.Доллар) КАК Доллар, //пустая ссылка | ЗНАЧЕНИЕ(Документ.ПоступлениеТоваровУслуг.ПустаяСсылка) КАК Поступление, //знач. перечисления | ЗНАЧЕНИЕ(Перечисление.ЮрФизЛицо.ФизЛицо) КАК ФизЛицо, //предопределенный счет | ЗНАЧЕНИЕ(ПланСчетов.Хозрасчетный.Материалы) КАК Счет_10" ;

4. Функция ВЫБОР - перед нами аналог конструкции ЕСЛИ который используется в коде, только эта используется в запросах 1С.

Синтаксис: ВЫБОР КОГДА <Выражение> ТОГДА <Выражение> ИНАЧЕ <Выражение> КОНЕЦ

Пример использования:

Запрос.Текст = //если сумма больше 7500, тогда должна быть скидка 300 рублей, //поэтому если условие срабатывает то функция //возвращает Сумма - 300 //в противном случае запрос вернет просто Сумма "ВЫБРАТЬ | ВЫБОР | КОГДА ТЧПоступления.Сумма > 7500 | ТОГДА ТЧПоступления.Сумма - 300 | ИНАЧЕ ТЧПоступления.Сумма | КОНЕЦ КАК СуммаСоСкидкой |ИЗ | Документ.ПоступлениеТоваровУслуг.Товары КАК ТЧПоступления";

5. Функция ВЫРАЗИТЬ - позволяет выразить константное поле определенным типом.

Синтаксис: ВЫРАЗИТЬ(НазваниеПоля КАК НазваниеТипа)

Пример использования:

Запрос.Текст = "ВЫБРАТЬ РАЗЛИЧНЫЕ | Продажи.Регистратор.Номер, | ВЫБОР | КОГДА Продажи.Регистратор ССЫЛКА Документ.Расходная | ТОГДА ВЫРАЗИТЬ(Продажи.Регистратор КАК Документ.Расходная) | ИНАЧЕ ВЫБОР | КОГДА Продажи.Регистратор ССЫЛКА Документ.Реализация | ТОГДА ВЫРАЗИТЬ(Продажи.Регистратор КАК Документ.Реализация) | КОНЕЦ | ... | КОНЕЦ КАК Номер | ИЗ | РегистрНакопления.Закупки КАК Закупки";

Еще есть вариант использования функции ВЫРАЗИТЬ в полях смешанных типах, где такие встречаются? Самый простой пример это "Регистратор" у любого регистра. Так зачем нам может понадобиться уточнять тип в регистраторе? Давайте рассмотрим ситуацию когда мы из регистратора выбираем поле "Номер", из какой таблицы будет выбран номер? Правильный ответ из всех! Поэтому чтобы наш запрос работал быстро следует указывать явный тип с помощью функции ВЫРАЗИТЬ

Пример использования:

Запрос.Текст = "ВЫБРАТЬ | ВЫРАЗИТЬ(Номенклатура.Комментарий КАК Строка(300)) КАК Комментарий, | ВЫРАЗИТЬ(Номенклатура.Сумма КАК Число(15,2)) КАК Сумма |ИЗ | Справочник.Номенклатура КАК Номенклатура";

6. Функция ISNULL (альтернативное написание ЕСТЬNULL) - если поле имеет тип NULL, то оно заменяется на второй параметр функции.

Синтаксис: ЕСТЬNULL(<Поле>, <ПодставляемоеЗначение>)

Пример использования:

Также отметим что тип NULL желательно ВСЕГДА заменять на какое-то значение, т.к. сравнение с типом NULL всегда дает ЛОЖЬ даже если вы сравниваете NULL с NULL. Чаще всего значения NULL образуются в результате соединения таблиц (все виды соединений кроме внутреннего).

Запрос.Текст = //Выбираем всю номенклатуру и остатки по ней //если остатокв но какой-то номенклатуре нет то будет поле //NULL которое заменится значением 0 "ВЫБРАТЬ | Ном.Ссылка, | ЕСТЬNULL(ТоварыНаСкладахОстатки.ВНаличииОстаток, 0) КАК Остаток |ИЗ | Справочник.Номенклатура КАК Ном | ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки КАК ТоварыНаСкладахОстатки | ПО (ТоварыНаСкладахОстатки.Номенклатура = Ном.Ссылка)";

7. Функция ПРЕДСТАВЛЕНИЕ - позволяет получить представление поля запроса.

Синтаксис: ПРЕДСТАВЛЕНИЕ(<НаименованиеПоля>)

Пример использования:

Запрос.Текст = "ВЫБРАТЬ | ПРЕДСТАВЛЕНИЕ(СвободныеОстаткиОстатки.Номенклатура) КАК Номенклатура, | ПРЕДСТАВЛЕНИЕ(СвободныеОстаткиОстатки.Склад) КАК Склад, | СвободныеОстаткиОстатки.ВНаличииОстаток |ИЗ | РегистрНакопления.СвободныеОстатки.Остатки КАК СвободныеОстаткиОстатки";

Конструкции в языке запросов 1с

Выше мы рассмотрели с Вами функции языка запросов 1с , теперь пришло время рассмотреть конструкции в языке запросов 1с , они не менее важны и полезны, приступаем.

1. Конструкция ССЫЛКА - представляет из себя логический оператор проверки ссылочного типа. Наиболее часто встречается при проверки поля составного типа на конкретный тип. Синтаксис: ССЫЛКА <Имя таблицы>

Пример использования:

Запрос.Текст = //если тип значения регистратора документ Приходная, //тогда запрос вернет "Поступление товаров", иначе "Реализация товаров" "ВЫБРАТЬ | ВЫБОР | КОГДА Остатки.Регистратор ССЫЛКА Документ.ПоступлениеТоваровУслуг | ТОГДА ""Приход"" | ИНАЧЕ ""Расход"" | КОНЕЦ КАК ВидДвижения |ИЗ | РегистрНакопления.ОстаткиТоваровНаСкладах КАК Остатки" ;

2. Конструкция МЕЖДУ - данный оператор проверяет входит ли значение в указанный диапазон.

Синтаксис: МЕЖДУ <Выражение> И <Выражение>

Пример использования:

Запрос.Текст = //получим всю номенклатуру код которой лежит в диапазоне от 1 до 100 "ВЫБРАТЬ | Номенклатура.Ссылка |ИЗ | Справочник.Номенклатура КАК Номенклатура |ГДЕ | Номенклатура.Код МЕЖДУ 1 И 100" ;

3. Конструкция В и В ИЕРАРХИИ - проверяют находится ли значение в передаваемом списке (в качестве списка могут передаваться массивы, таблицы значений и т.д.). Оператор В ИЕРАРХИИ позволяет просматривать иерархию (пример использования ПланСчетов).

Синтаксис: В(<СписокЗначений>), В ИЕРАРХИИ(<СписокЗначений>)

Пример использования:

Запрос.Текст = //выбираем все субсчета счета "ВЫБРАТЬ | Хозрасчетный.Ссылка КАК Счет |ИЗ | ПланСчетов.Хозрасчетный КАК Хозрасчетный |ГДЕ | Хозрасчетный.Ссылка В ИЕРАРХИИ ЗНАЧЕНИЕ(ПланСчетов.Хозрасчетный.Товары)";

4. Конструкция ПОДОБНО - эта функция позволяет нам сравнивать строку с шаблоном строки.

Синтаксис: ПОДОБНО "<ТекстШаблона>"

Варианты шаблона строки:

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

Один произвольный символ.

[...] - любой одиночный символ, либо последовательность символов из перечисленных внутри квадратных скобок. В перечислении могут задаваться диапазоны, например a-z, означающие произвольный символ, входящий в диапазон, включая концы диапазона.

[^...] - любой одиночный символ, либо последовательность символов из перечисленных внутри квадратных скобок кроме тех, которые перечислены следом за значком отрицания.

Пример использования:

Запрос.Текст = //найдем всю номенклатуру которая содержит корень ТАБУР и начинается //либо с маленькой либо с большой буквы т "ВЫБРАТЬ | Номенклатура.Ссылка |ИЗ | Справочник.Номенклатура КАК Номенклатура |ГДЕ | Товары.Наименование ПОДОБНО ""[Тт]абур%""" ;

5. Конструкция РАЗРЕШЕННЫЕ - этот оператор позволяет выбрать только те записи из БД, на которые вызывающий имеет право чтения. Данные права настраиваются на уровне записей (RLS).

Синтаксис: РАЗРЕШЕННЫЕ пишется после ключевого слова ВЫБРАТЬ

Пример использования:

Запрос.Текст = "ВЫБРАТЬ РАЗРЕШЕННЫЕ | Контрагенты.Ссылка |ИЗ | Справочник.Контрагенты КАК Контрагенты";

6. Конструкция РАЗЛИЧНЫЕ - позволяет выбрать записи в которых отсутствуют повторные записи.

Синтаксис: РАЗЛИЧНЫЕ пишется после ключевого слова ВЫБРАТЬ

Пример использования:

Запрос.Текст = //выбирает записи на которые есть права у читающего "ВЫБРАТЬ РАЗЛИЧНЫЕ | Контрагенты.Наименование |ИЗ | Справочник.Контрагенты КАК Контрагенты" ;

Также Конструкция РАЗЛИЧНЫЕ может использоваться с оператором РАЗРЕШЕННЫЕ и другими операторами.

Пример использования:

Запрос.Текст = //выбирает различные записи на которые есть права у читающего "ВЫБРАТЬ РАЗРЕШЕННЫЕ РАЗЛИЧНЫЕ | Контрагенты.Наименование |ИЗ | Справочник.Контрагенты КАК Контрагенты";

7. Конструкция ПЕРВЫЕ - выбирает указанное в параметре число записей из результата запроса.

Синтаксис: ПЕРВЫЕ <число>

Пример использования:

Запрос.Текст = //выбирают первые 4 номера ГТД из справочника "ВЫБРАТЬ ПЕРВЫЕ 4 | НомераГТД.Ссылка |ИЗ | Справочник.НомераГТД КАК НомераГТД";

8. Конструкция ДЛЯ ИЗМЕНЕНИЯ - позволяет заблокировать таблицу, работает только в транзакциях (актуально только для автоматических блокировок).

Синтаксис: ДЛЯ ИЗМЕНЕНИЯ <НаименованиеТаблицы>

Пример использования:

Запрос.Текст = "ВЫБРАТЬ | СвободныеОстаткиОстатки.Номенклатура, | СвободныеОстаткиОстатки.Склад, | СвободныеОстаткиОстатки.ВНаличииОстаток |ИЗ | РегистрНакопления.СвободныеОстатки.Остатки КАК СвободныеОстаткиОстатки |ДЛЯ ИЗМЕНЕНИЯ | РегистрНакопления.СвободныеОстатки.Остатки";

9. Конструкция УПОРЯДОЧИТЬ ПО - упорядочивает данные по определенному полю. Если полем является ссылка то при установке флага АВТОУПОРЯДОЧИВАНИЕ будет происходить сортировка по представлению ссылки, если флаг выключен то ссылки сортируются по старшинству адреса ссылки в памяти.

Синтаксис: УПОРЯДОЧИТЬ ПО <НаименованиеПоля> АВТОУПОРЯДОЧИВАНИЕ

Пример использования:

Запрос.Текст = "ВЫБРАТЬ | СвободныеОстаткиОстатки.Номенклатура КАК Номенклатура, | СвободныеОстаткиОстатки.Склад КАК Склад, | СвободныеОстаткиОстатки.ВНаличииОстаток |ИЗ | РегистрНакопления.СвободныеОстатки.Остатки КАК СвободныеОстаткиОстатки | |УПОРЯДОЧИТЬ ПО | Номенклатура |АВТОУПОРЯДОЧИВАНИЕ";

10. Конструкция СГРУППИРОВАТЬ ПО - используется для группировки строк запроса по определенным полям. Числовые поля должны использоваться с любой агрегатной функцией.

Синтаксис: СГРУППИРОВАТЬ ПО <НаименованиеПоля1>, .... , <НаименованиеПоляN>

Пример использования:

Запрос.Текст = "ВЫБРАТЬ | ТоварыНаСкладах.Номенклатура КАК Номенклатура, | ТоварыНаСкладах.Склад, | СУММА(ТоварыНаСкладах.ВНаличии) КАК ВНаличии |ИЗ | РегистрНакопления.ТоварыНаСкладах КАК ТоварыНаСкладах | |СГРУППИРОВАТЬ ПО | ТоварыНаСкладах.Номенклатура, | ТоварыНаСкладах.Склад";

11. Конструкция ИМЕЮЩИЕ - позволяет применить агрегатную функцию к условию выборки данных, похожа на конструкцию ГДЕ.

Синтаксис: ИМЕЮЩИЕ <агрегатная функция с условием>

Пример использования:

Запрос.Текст = //выбирает сгруппированные записи где поле ВНаличии больше 3 "ВЫБРАТЬ | ТоварыНаСкладах.Номенклатура КАК Номенклатура, | ТоварыНаСкладах.Склад, | СУММА(ТоварыНаСкладах.ВНаличии) КАК ВНаличии |ИЗ | РегистрНакопления.ТоварыНаСкладах КАК ТоварыНаСкладах | |СГРУППИРОВАТЬ ПО | ТоварыНаСкладах.Номенклатура, | ТоварыНаСкладах.Склад | |ИМЕЮЩИЕ | СУММА(ТоварыНаСкладах.ВНаличии) > 3" ;

12. Конструкция ИНДЕКСИРОВАТЬ ПО - используется для индексации поле запроса. Запрос с индексацией дольше выполняется, но ускоряет поиск по индексированным полям. Можно использовать только в виртуальных таблицах.

Синтаксис: ИНДЕКСИРОВАТЬ ПО <Поле1, ... , ПолеN>

Пример использования:

Запрос.Текст = "ВЫБРАТЬ | Тз.НаименованиеОС, | Тз.НомерПапки, | Тз.КодОС, | Тз.Срок, | Тз.Тип |ПОМЕСТИТЬ ДанныеТз |ИЗ | &Тз КАК Тз | |ИНДЕКСИРОВАТЬ ПО | Тз.НаименованиеОС, | Тз.КодОС";

13. Конструкция ГДЕ - позволяет наложить условие на любые поля выборки. В результат попадут записи только удовлетворяющие условию.

Синтаксис: ГДЕ <Условие1 ОператорЛогСоединения УсловиеN>

Пример использования:

Запрос.Текст = //выбираются все записи у которых КомпенсацияОстаток <> 0 и //СуммаДляРасчКомпОстаток > 100 "ВЫБРАТЬ | КомпенсацияРПОстатки.Контрагент, | КомпенсацияРПОстатки.Ребенок, | КомпенсацияРПОстатки.КомпенсацияОстаток, | КомпенсацияРПОстатки.СуммаДляРасчКомпОстаток |ПОМЕСТИТЬ ДанныеТз |ИЗ | РегистрНакопления.КомпенсацияРП.Остатки КАК КомпенсацияРПОстатки |ГДЕ | КомпенсацияРПОстатки.КомпенсацияОстаток <> 0 | И КомпенсацияРПОстатки.СуммаДляРасчКомпОстаток > 100" ;

14. Конструкция ИТОГИ... ПО ОБЩИЕ - применяется для подсчета итогов, в конструкции указываются поля по которым будут считаться итоги и агрегатные функции применяемые к итоговым полям. При использовании итогов на каждое поле следующее после конструкции ИТОГИ производится группировка данных. Есть необязательная конструкция ОБЩИЕ, ее использование также обеспечивает дополнительную группировку. Пример результата запроса вы увидите ниже.

Синтаксис: ИТОГИ <АгрегатнаяФункция1, ... , АгрегатнаяФункцияN> ПО <ОБЩИЕ> <Поле1, ... , ПолеN>

Пример использования:

Запрос.Текст = "ВЫБРАТЬ | Расчеты.ДоговорКонтрагента.ВидДоговора КАК ВидДоговора, | Расчеты.ДоговорКонтрагента КАК Договор, | Расчеты.Контрагент, | Расчеты.СуммаВзаиморасчетовОстаток КАК Остаток |ИЗ | РегистрНакопления.ВзаиморасчетыСКонтрагентами.Остатки КАК Расчеты |ИТОГИ | СУММА(Остаток) |ПО | ОБЩИЕ, | ВидДоговора";

На рисунке обведены группировки которые образовались в ходе выполнения запроса, самая верхняя относится к секции ОБЩИЕ, а вторая к полю ДоговорКонтрагентаВидДоговора.

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

1. Конструкция "ИЗ".

Для того, чтобы получить данные из базы совсем необязательно использовать конструкцию "ИЗ".
Пример: Нам необходимо выбрать все сведения о банках из справочника банки.
Запрос:

ВЫБРАТЬ Справочник.Банки.*

Выбирает все поля из справочника Банки. И является аналогичным запросу:

ВЫБРАТЬ Банки.* ИЗ Справочник.Банки КАК Банки

2. Упорядочивание данных по ссылочному полю

Когда нам необходимо упорядочить данные запроса по примитивным типам: "Строка", "Число", "Дата" и т.д., то все решается использованием конструкции "УПОРЯДОЧИТЬ ПО", если вам необходимо упорядочить данные по ссылочному полю? Ссылочное поле представляет из себя ссылку, уникальный идентификатор, т.е. грубо говоря некий произвольный набор символов и обычное упорядочивание может выдать не совсем ожидаемый результат. Для упорядочивания ссылочным полей используется конструкция "АВТОУПОРЯДОЧИВАНИЕ". Для этого необходимо сначала упорядочить данные непосредственно по ссылочному типу конструкцией "УПОРЯДОЧИТЬ ПО", а затем конструкция "АВТОУПОРЯДОЧИВАНИЕ".

В этом случае для документов упорядочивание будет происходить в порядке "Дата->Номер" , для справочников по "Основному представлению". Если упорядочивание происходит не по ссылочным полям, то использовать конструкцию "АВТОУПОРЯДОЧИВАНИЕ" не рекомендуется.

В некоторых случаях конструкция "АВТОУПОРЯДОЧИВАНИЕ" может замедлять процесс выборки. Аналогичным образом можно переписать без автоупорядочивания для документов:

3.Получение текстового представления ссылочного типа. Конструкция "ПРЕДСТАВЛЕНИЕ".

Когда вам необходимо вывести для показа поле ссылочного типа, например поле "Банк", которое является ссылкой на элемент справочника "Банки", то необходимо понимать, что при выводе этого поля автоматически выполнится подзапрос к справочнику "Банки", чтобы получить представление справочника. Это будет замедлять вывод данных. Для Того, чтобы этого избежать необходимо использовать конструкцию "ПРЕДСТАВЛЕНИЕ" в запросе, чтобы сразу получить представление объекта и уже его выводить для просмотра.

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

4. Условие на выборку данных по шаблону.

Например, вам необходимо получить мобильные телефоны сотрудников вида (8 -123- 456-78-912). Для этого необходимо поставить такое условие в запросе:

ВЫБРАТЬ Сорудник.Наименование, Сорудник.Телефон КАК Телефон ИЗ Справочник.Сотрудники КАК Сотрудники ГДЕ Телефон ПОДОБНО "_-___-___-__-__"

Символ "_" является служебным и заменяет любой символ.

5. Одновременное использование итогов и группировок.

Итоги часто используются совместно с группировками, в таком случае агрегатные функции в итогах можно не указывать.

ВЫБРАТЬ ОказаниеУслуг.Организация КАК Организация, ОказаниеУслуг.Номенклатура КАК Номенклатура, СУММА(ОказаниеУслуг.СуммаДокумента) КАК СуммаДокумента ИЗ Документ.ОказаниеУслуг КАК ОказаниеУслуг СГРУППИРОВАТЬ ПО ОказаниеУслуг.Организация, ОказаниеУслуг.Номенклатура ИТОГИ ПО ОБЩИЕ, Организация, Номенклатура

В этом случае запрос вернет практически тоже самое что и такой запрос:

ВЫБРАТЬ ОказаниеУслуг.Организация КАК Организация, ОказаниеУслуг.Номенклатура КАК Номенклатура, ОказаниеУслуг.СуммаДокумента КАК СуммаДокумента ИЗ Документ.ОказаниеУслуг КАК ОказаниеУслуг ИТОГИ СУММА(СуммаДокумента) ПО ОБЩИЕ, Организация, Номенклатура

Только первый запрос свернет записи с одинаковой номенклатурой.

6. Разыменование полей.

Обращение к полям через точку называется операцией разыменования ссылочного поля. Например Оплата.Организация.АдминистративнаяЕдиница . В этом случае в ссылочном поле "Организация" документа "Оплата", ссылается на другую таблицу "Организации", в которой будет получено значение реквизита "АдминистративнаяЕдиница". Важно понимать, что при обращении к полям через точку платформа неявно создает подзапрос и соединяет эти таблицы.

Запрос:

Можно представить в виде:

ВЫБРАТЬ Оплата.Ссылка, Оплата.Организация, Оплата.Организация, Организации. АдминистративнаяЕдиница ИЗ Документ.Оплата КАК Оплата ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Организации КАК Организации ПО Оплата.Организация = Организации.Ссылка

При разыменовании ссылочных полей составного типа платформа пытается создать неявные соединения со всеми таблицами, которые входят в тип этого поля. В этом случае запрос будет неоптимален.Если четко известно, какого типа поле, необходимо ограничивать такие поля по типу конструкцией ВЫРАЗИТЬ() .

Например имеется регистр накопления "Нераспределенные оплаты", где регистратором могут выступать несколько документов. В этом случае неверно получать значения реквизитов регистратора таким образом:

ВЫБРАТЬ НераспределенныеОплаты.Регистратор.Дата, ..... ИЗ РегистрНакопления.НераспределеныеОплаты КАК НераспределенныеОплаты

следует ограничить тип составного поля регистратор:

ВЫБРАТЬ ВЫРАЗИТЬ(НераспределенныеОплаты.Регистратор КАК Документ.Оплата).Дата, ..... ИЗ РегистрНакопления.НераспределеныеОплаты КАК НераспределенныеОплаты

7. Конструкция "ГДЕ"

При левом соединении двух таблиц, когда вы накладываете условие "ГДЕ" на правую таблицу то мы получим результат аналогичный результату при внутреннем соединении таблиц.

Пример. Необходимо выбрать всех Клиентов из Справочника клиенты и для тех клиентов, у которых имеется документ оплата со значением реквизита "Организация" = &Организация вывести документ "Оплата", для тех у кого нет, не выводить.

Результат запроса вернет записи только для тех клиентов, у которых была оплата по организации в параметре, а других клиентов отсеет. Поэтому необходимо сначала получить все оплаты по "такой-то" организации во временной таблице, а потом уже соединять со справочником "Клиенты" левым соединением.

ВЫБРАТЬ Оплата.Ссылка КАК Оплата, Оплата.Пайщик КАК Клиент ПОМЕСТИТЬ тОплаты ИЗ Документ.Оплата КАК Оплата ГДЕ Оплата.Отделение = &Отделение; //////////////////////////////////////////////////////////////////////////////// ВЫБРАТЬ Клиенты.Ссылка КАК Клиент, ЕСТЬNULL(тОплаты.Оплата, "") КАК Оплата ИЗ Справочник.Клиенты КАК Клиенты ЛЕВОЕ СОЕДИНЕНИЕ тОплаты КАК тОплаты ПО Клиенты.Ссылка = тОплаты.Клиент

Можно обойти это условие и другим способом. необходимо наложить условие "ГДЕ" непосредственно в связи двух таблиц. Пример:

ВЫБРАТЬ Клиенты.Ссылка, Оплата.Ссылка ИЗ Справочник.УС_Абоненты КАК УС_Абоненты ЛЕВОЕ СОЕДИНЕНИЕ Документ.Оплата КАК Оплата ПО (Клиенты.Ссылка = Оплата.Клиент И Оплата.Клиент.Наименование ПОДОБНО "Сахарный Пакет") СГРУППИРОВАТЬ ПО Клиенты.Ссылка, Оплата.Ссылка

8. Соединения с Вложенными и Виртуальными таблицами

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

Для примера нам необходимо для некоторых клиентов получить Сумму остатка на текущую дату.

ВЫБРАТЬ НераспределенныеОплатыОстатки.Клиент, НераспределенныеОплатыОстатки.СуммаОстаток ИЗ (ВЫБРАТЬ Клиенты.Ссылка КАК Ссылка ИЗ Справочник.Клиенты КАК Клиенты ГДЕ Клиенты.Ссылка В(&Клиенты)) КАК ВложенныйЗапрос ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.НераспределенныеОплаты.Остатки КАК НераспределенныеОплаты ПО ВложенныйЗапрос.Ссылка = НераспределенныеОплатыОстатки.Клиент

При выполнении такого запроса, вероятны ошибки оптимизатора СУБД при выборе плана, что приведет к неоптимальному выполнению запроса. При соединении двух таблиц оптимизатор СУБД выбирает алгоритм соединения таблиц исходя из количества записей в обеих таблицах. В случае наличия вложенного запроса, определить количество записей, которое вернет вложенный запрос крайне сложно. Поэтому вместо вложенных запросов всегда стоит использовать временные таблицы. Поэтому перепишем запрос.

ВЫБРАТЬ Клиенты.Ссылка КАК Ссылка ПОМЕСТИТЬ тКлиенты ИЗ Справочник.Клиенты КАК Клиенты ГДЕ
Клиенты.Ссылка В (&Клиенты) ; //////////////////////////////////////////////////////////////////////////////// ВЫБРАТЬ тКлиенты.Ссылка, НераспределенныеОплатыОстатки.СуммаОстаток, ИЗ тКлиенты КАК тКлиенты ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.НераспределенныеОплаты.Остатки(, Клиент В (ВЫБРАТЬ тКлиенты.Ссылка ИЗ тКлиенты)) КАК НераспределенныеОплатыОстатки ПО тКлиенты.Ссылка = НераспределенныеОплатыОстатки.Клиенты

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

Виртуальные таблицы , позволяют получить практически готовые данные для большинства прикладных задач.(СрезПервых,СрезПоследних,Остатки,Обороты,ОстаткиИОбороты) Ключевое слово здесь виртуальные. Эти таблицы не являются физическими, а компонуются системой налету, т.е. при получении данных из виртуальных таблиц система собирает данные из итоговых таблиц регистров, компонует, группирует и выдает пользователю.

Т.е. при соединении с виртуальной таблицей происходит соединение с подзапросом. В этом случае оптимизатор СУБД может также выбрать неоптимальный план соединения. Если запрос формируется недостаточно быстро и в запросе испольуются соединения в виртуальными таблицами, то реклмендуется вынести обращение к виртуальным таблицам во временную таблицу, а затем в произвести соедининие между двумя временными таблицами. Перепишем предыдущий запрос.

ВЫБРАТЬ Клиенты.Ссылка КАК Ссылка ПОМЕСТИТЬ тКлиенты ИЗ Справочник.Клиенты КАК Клиенты ИНДЕКСИРОВАТЬ ПО Ссылка ГДЕ
Клиенты.Ссылка В (&Клиенты) ; //////////////////////////////////////////////////////////////////////////////// ВЫБРАТЬ НераспределенныеОплаты.СуммаОстаток, НераспределенныеОплаты.Клиент КАК Клиент ПОМЕСТИТЬ тОстатки ИЗ РегистрНакопления.НераспределенныеОплаты.Остатки(, Клиент В (ВЫБРАТЬ тКлиенты.Ссылка ИЗ тКлиенты)) КАК НераспределенныеОплатыОстатки; //////////////////////////////////////////////////////////////////////////////// ВЫБРАТЬ тКлиенты.Ссылка, тОстатки.СуммаОстаток КАК СуммаОстаток ИЗ тКлиенты КАК тКлиенты ЛЕВОЕ СОЕДИНЕНИЕ тОстатки КАК тОстатки ПО тКлиенты.Ссылка = тОстатки.Клиент

9.Проверка результата выполнения запроса.

Результат выполнения запроса может быть пустым, для проверки на пустые значения следует использовать конструкцию:

РезЗапроса = Запрос.Выполнить(); Если резЗапроса.Пустой() Тогда Возврат; КонецЕсли;

Метод Пустой() следует использовать до методов Выбрать() или Выгрузить() , так как на получение коллекции тратится время.

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

Запрос = Новый Запрос; Запрос.Текст = "ВЫБРАТЬ | Клиенты.Ссылка, | Клиенты.ДатаРождения |ИЗ | Справочник.Клиенты КАК Клиенты |ГДЕ | Клиенты.Ссылка = &Клиент"; Для Каждого Строка ИЗ ТаблицаКлиенты Цикл Запрос.УстановитьПараметр("Клиент", Клиент); РезультатЗапроса = Запрос.Выполнить().Выбрать(); КонецЦикла;

Это избавит систему от синтаксической проверки запроса в цикле.

11. Конструкция "ИМЕЮЩИЕ".

Конструкция, довольно редко встречающаяся в запросах. Позволяет накладывать условия на значения агрегатные функций (СУММА, МИНИМУМ, СРЕДНЕЕ и т.д.). Например, вам необходимо выбрать только тех клиентов, у которых сумма оплат в сентябре была больше 13 000 рублей. Если использовать условие "ГДЕ", то придется сначала создавать временную таблицу или вложенный запрос, там группировать записи по сумме оплаты и потом накладывать условие. Конструкция "ИМЕЮЩИЕ" поможет этого избежать.

ВЫБРАТЬ Оплата.Клиент, СУММА(Оплата.Сумма) КАК Сумма ИЗ Документ.Оплата КАК Оплата ГДЕ МЕСЯЦ(Оплата.Дата) = 9 СГРУППИРОВАТЬ ПО Оплата.Клиент ИМЕЮЩИЕ СУММА(Оплата.Сумма) > 13000

В конструкторе для этого достаточно перейти на вкладку "Условия", добавить новое условие и поставить галочку на "Произвольное". Далее просто написать Сумма(Оплата.Сумма) > 13000

12. Значение NULL

Я не буду описывать здесь принципы трехзначной логики в БД, есть множество статей на эту тему. Просто вкратце о том как NULL может повлиять на результат запроса. Значение NULL на самом деле не значение, а факт того, что значение не определено, неизвестно. Поэтому любые операции с NULL возвращают NULL, будь то сложение, вычитание, деление или сравнение. Значение NULL не может быть сравнимо со значением NULL, потому как мы не знаем, что именно сравнивать. Т.е. оба этих сравнения: NULL = NULL, NULL<>NULL - это не Истина или не Ложь, это неизвестно.

Давайте рассмотрим пример.

Нам необходимо для тех клиентов, у которых нет оплат, вывести поле "Признак" со значением "Нет оплат". Причем мы точно знаем, что такие клиенты у нас есть. И для того, чтобы отразить суть того, что писал выше сделаем это так.

ВЫБРАТЬ "Нет оплат" КАК Признак, NULL КАК Документ ПОМЕСТИТЬ тОплаты; //////////////////////////////////////////////////////////////////////////////// ВЫБРАТЬ Клиенты.Ссылка КАК Клиент, Оплата.Ссылка КАК Оплата ПОМЕСТИТЬ тКлиентОплата ИЗ Справочник.Клиенты КАК Клиенты ЛЕВОЕ СОЕДИНЕНИЕ Документ.Оплата КАК Оплата ПО Клиенты.Ссылка = Оплата.Пайщик; //////////////////////////////////////////////////////////////////////////////// ВЫБРАТЬ тКлиентОплата.Клиент ИЗ тКлиентОплата КАК тКлиентОплата ВНУТРЕННЕЕ СОЕДИНЕНИЕ тОплаты КАК тОплаты ПО тКлиентОплата.Оплата = тОплаты.Документ

Обратите внимание на вторую временную таблицу тКлиентОплата. Левым соединением я выбираю всех клиентов и все оплаты по этим клиентам. Для тех же клиентов у которых нет оплат в поле "Оплата" будет NULL . Следуя логике, в первой временной таблице "тОплаты" я обозначил 2 поля, одно из них NULL, второе строка "Не имеет оплат". В третьей таблице я соединяю внутренним соединением таблицы "тКлиентОплата" и "тОплаты" по полям "Оплата" и "Документ". Мы знаем, что в первой таблице поле "Документ" это NULL, и во второй таблице у тех, у кого нет оплат в поле "Оплата" тоже NULL. Что же вернет нам такое соединение? А ничего не вернет. Потому как сравнение NULL = NULL не принимает значение Истина.

Для того, чтобы запрос вернул нам ожидаемый результат, перепишем его:

ВЫБРАТЬ "Нет оплат" КАК Признак, ЗНАЧЕНИЕ(Документ.Оплата.ПустаяСсылка) КАК Документ ПОМЕСТИТЬ тОплаты; //////////////////////////////////////////////////////////////////////////////// ВЫБРАТЬ Клиенты.Ссылка КАК Клиент, ЕСТЬNULL(Оплата.Ссылка, ЗНАЧЕНИЕ(Документ.Оплата.ПустаяСсылка)) КАК Оплата ПОМЕСТИТЬ тКлиентОплата ИЗ Справочник.Клиенты КАК Клиенты ЛЕВОЕ СОЕДИНЕНИЕ Документ.Оплата КАК Оплата ПО Клиенты.Ссылка = Оплата.Пайщик; //////////////////////////////////////////////////////////////////////////////// ВЫБРАТЬ тКлиентОплата.Клиент ИЗ тКлиентОплата КАК тКлиентОплата ВНУТРЕННЕЕ СОЕДИНЕНИЕ тОплаты КАК тОплаты ПО тКлиентОплата.Оплата = тОплаты.Документ

Теперь, во второй временной таблице, мы указали, что в случае, если поле "Оплата" есть NULL, тогда это поле = пустая ссылка на документ оплата. В Первой таблице мы также заменили NULL на пустую ссылку. Теперь в соединении участвуют не NULL поля и запрос вернет нам ожидаемый результат.

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

13. Недокументированная особенность конструкции "ВЫБОР КОГДА...ТОГДА....КОНЕЦ".

В том случае, когда необходимо описывать в запросе контрукцию "Условия", то мы используем стандартный синтаксис:

ВЫБРАТЬ ВЫБОР КОГДА Пользователи.Наименование = "Вася Пупкин" ТОГДА "Наш любимый сотрудник" ИНАЧЕ "Не знаем такого" КОНЕЦ КАК Поле1 ИЗ Справочник.Пользователи КАК Пользователи

А что делать, если, к примеру, нам надо получить название месяца в запросе? Писать огромную конструкцию в запросе некрасиво и долго, поэтому нас может выручить такая форма записи выше:

ВЫБОР МЕСЯЦ(УС_РасчетПотребления_ГрафикОбороты.ПериодРасчета) КОГДА 1 ТОГДА "Январь" КОГДА 2 ТОГДА "Февраль" КОГДА 3 ТОГДА "Март" КОГДА 4 ТОГДА "Апрель" КОГДА 5 ТОГДА "Май" КОГДА 6 ТОГДА "Июнь" КОГДА 7 ТОГДА "Июль" КОГДА 8 ТОГДА "Август" КОГДА 9 ТОГДА "Сентябрь" КОГДА 10 ТОГДА "Октябрь" КОГДА 11 ТОГДА "Ноябрь" КОГДА 12 ТОГДА "Декабрь" КОНЕЦ КАК Месяц

Теперь конструкция выглядит не такой громоздкой и легко воспринимается.

14. Пакетное выполнение запроса.

Для того, чтобы не плодить запросы, можно создать один большой запрос, разбить его на пакеты и работать уже с ним.
Например, мне нужно получить из справочника "Пользователи" поля: "ДатаРождения" и доступные роли для каждого пользователя. в выгрузить это в разные табличные части на форме. Конечно можно сделать это в одном запросе, тогда придется перебирать записи или сворачивать, а можно так:

ВЫБРАТЬ Пользователи.Ссылка КАК ФИО, Пользователи.ДатаРождения, Пользователи.Роль ПОМЕСТИТЬ втПользователи ИЗ Справочник.Пользователи КАК Пользователи; //////////////////////////////////////////////////////////////////////////////// ВЫБРАТЬ втПользователи.ФИО, втПользователи.ДатаРождения ИЗ втПользователи КАК втПользователи СГРУППИРОВАТЬ ПО втПользователи.ФИО, втПользователи.ДатаРождения; //////////////////////////////////////////////////////////////////////////////// ВЫБРАТЬ втПользователи.ФИО, втПользователи.Роль ИЗ втПользователи КАК втПользователи СГРУППИРОВАТЬ ПО втПользователи.ФИО, втПользователи.ДатаРождения

тПакет = Запрос.ВыполнитьПакет();

ТП_ДатыРождения = тПакет.Выгрузить();
ТП_Роли = тПакет.Выгрузить();

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

15. Условия в пакетном запросе

Например, у нас есть пакетный запрос, где сначало мы получаем поля: "Наименование, ДатаРождения, Код" из справочника "Пользователи" и хотим из справочника "ФизЛица" получить записи с условием по этим полям.

ВЫБРАТЬ Пользователи.ФизЛицо.Наименование КАК Наименование, Пользователи.ФизЛицо.ДатаРождения КАК ДатаРождения, Пользователи.ФизЛицо.Код КАК Код ПОМЕСТИТЬ втПользователи ИЗ Справочник.Пользователи КАК Пользователи; //////////////////////////////////////////////////////////////////////////////// ВЫБРАТЬ ФизическиеЛица.Ссылка КАК ФизЛицо ИЗ Справочник.ФизическиеЛица КАК ФизическиеЛица

Можно накложить условия таким образом:

ГДЕ ФизическиеЛица.Код В (ВЫБРАТЬ втПользователи.Код ИЗ втПользователи) И ФизическиеЛица.Наименование В (ВЫБРАТЬ втПользователи.Код ИЗ втПользователи) И ФизическиеЛица.ДатаРождения В (ВЫБРАТЬ втПользователи.ДатаРождения ИЗ втПользователи)

А Можно и так:

ГДЕ (ФизическиеЛица.Код, ФизическиеЛица.Наименование, ФизическиеЛица.ДатаРождения) В (ВЫБРАТЬ втПользователи.Код, втПользователи.Наименование, втПользователи.ДатаРождения ИЗ втПользователи)

Причем обязателено соблюдать порядок.

16. Вызов конструктора запросов для "условия" в пакетном запросе

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

Необходимо после Конструкции "В" поставить скобки и между скобками оставить пустое место(пробел), выделить это место и вызвать контруктор запросов. Контруктору будут доступны все таблицы пакетного запроса. Прием работает как на виртуальных таблицах регистров, так и для вкладки "Условия". В последнем случае необходимо поставить галочку "П(произволное условие)" и войти в режим редактирования "F4".

Запросов зачастую выдумывал на ходу и они служат просто для отображения "приемов", которые я рассматривал.

Хотел рассмотреть использование индексов в запросах, но больно обширная тема. Вынесу в отдельную статью, либо позже добавлю здесь.

upd1. Пункты 11,12
upd2. Пункты 13,14,15,16

Используемая литература:
Язык запросов "1С:Предприятия 8" - Е.Ю. Хрусталева
Профессиональная разработка в системе 1С:Предприятие 8".