Как поставить условие в запросе 1с

Эффективные условия запросов

Область применения: управляемое приложение, мобильное приложение, обычное приложение.

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

Поля основного условия в секциях ГДЕ, ПО и виртуальных таблицах должны быть проиндексированы. Основное условие может быть уточнено дополнительным условием, но объединять их следует только по И.

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

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

Дополнительное условие – это то, что объединено с основным условием по И и его составляющие могут быть любой сложности (НЕ, <>, +, -, /, *, функции и т.п.).

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

• для первого и всех используемых полей индекса, кроме последнего, только = и И;
• для последнего или единственного используемого поля индекса допустимо использовать =, >, <, >=, <=, ПОДОБНО, МЕЖДУ, В, ИЛИ (приводимое к В);
• нельзя использовать арифметические операции, функции, отрицания и неравенства.

Для условий в ГДЕ или в виртуальной таблице следует индексировать поля в основной таблице, из которой выполняется выборка.

Для условий в ПО ЛЕВОГО соединения следует индексировать поля в правой таблице.

Для условий в ПО ВНУТРЕННЕГО соединения следует индексировать поля в таблице с большим количеством записей.

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

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

1.2. Если записей в таблице много и выполнить указанные выше требования невозможно, то можно попробовать:

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

2.1. В основном условии оператор ИЛИ можно использовать только для последнего из используемых или единственного поля индекса, когда оператор ИЛИ можно заменить на оператор В.

ГДЕ
Таблица.Поле = &Значение1
ИЛИ Таблица.Поле = &Значение2

т.к. можно переписать при помощи оператора В (специально переписывать не нужно, можно оставить, как есть):

ГДЕ
Таблица.Поле В (&Значения)

ГДЕ
Таблица.Поле1 = &Значение1
ИЛИ Таблица.Поле2 = &Значение2

нельзя переписать при помощи «В», но можно переписать при помощи «ОБЪЕДИНИТЬ ВСЕ» (каждое поле Поле1 и Поле2 должны быть проиндексированы):

ГДЕ
Таблица.Поле1 = &Значение1

ГДЕ
Таблица.Поле2 = &Значение1

Примечание: заменить ИЛИ на ОБЪЕДИНИТЬ ВСЕ можно не всегда, убедитесь, что результат будет действительно тем же, что и при ИЛИ, перед тем, как применять.

2.2. В дополнительном условии оператор ИЛИ можно использовать без ограничений.

ГДЕ
Таблица.Поле1 = &Значение1 // Основное условие (использует индекс)
И // Дополнительное условие (можно использовать ИЛИ)
(Таблица.Поле2 = &Значение2 ИЛИ Таблица.Поле3 = &Значение3)

ГДЕ
(Таблица.Поле1 = &Значение1 ИЛИ Таблица.Поле1 = &Значение2)
И
(Таблица.Поле2 = &Значение3 ИЛИ Таблица.Поле2 = &Значение4)

т.к. можно переписать при помощи В (специально переписывать не нужно, можно оставить, как есть):

ГДЕ
Таблица.Поле1 В (&Значения1) // Основное условие
И Таблица.Поле2 В (&Значения2) // Дополнительное условие (или наоборот)

3. Оператор ПОДОБНО

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

ГДЕ
ПОДСТРОКА(Таблица.Поле, 1, 6) = «строка»

ГДЕ
Таблица.Поле ПОДОБНО «строка%»

ГДЕ
ПОДСТРОКА(Таблица.Поле, 3, 6) = «строка»

ГДЕ
Таблица.Поле ПОДОБНО «__строка%» // Литерал не должен начинаться с символов «_» или «%»

Добавить новое вычисляемое при записи в таблицу поле, которое будет содержать фрагмент ПОДСТРОКА(Таблица.Поле, 3, 6). Проиндексировать это поле и искать по следующему условию:

ГДЕ
Таблица.ВычисляемоеПоле ПОДОБНО «строка%»

4. Оператор МЕЖДУ

В основном условии для последнего из используемых или единственного поля индекса можно использовать оператор МЕЖДУ. Функции работы с датой, в некоторых случаях, можно привести к оператору МЕЖДУ и использовать его в основном условии.

ГДЕ
МЕСЯЦ(Таблица.Поле) = 1

ГДЕ
Таблица.Поле МЕЖДУ &ДатаНачалаМесяца И &ДатаКонцаМесяца

Например, ДатаНачалаМесяца=01.01.2016, ДатаКонцаМесяца=31.01.2016 23:59:59

5. Выражение ВЫБОР

Выражение ВЫБОР можно использовать только в дополнительных условиях.

ГДЕ
Таблица.Поле1 = &Значение1 // Основное условие (использует индекс)
И // Дополнительное условие (можно использовать ВЫБОР)
ВЫБОР
КОГДА Таблица.Поле2 = &Значение2
ТОГДА Таблица.Поле3 = &Значение3
ИНАЧЕ Таблица.Поле4 = &Значение4
КОНЕЦ

ГДЕ
ВЫБОР // Основное условие (поиск по индексу использоваться не будет)
КОГДА Таблица.Поле2 = &Значение2
ТОГДА Таблица.Поле3 = &Значение3
ИНАЧЕ Таблица.Поле4 = &Значение4
КОНЕЦ

6. Арифметические операции

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

ГДЕ
Таблица.Поле1 = &Значение1 // Основное условие (использует индекс)
И // Дополнительное условие (можно выполнять арифметические операции)
Таблица.Поле2 — 1 > 0

ГДЕ
Таблица.Поле1 — 1 > 0 // Основное условие (поиск по индексу невозможен)

7. Если в конфигурации описано несколько ролей с разным ограничением доступа на уровне записей (RLS), то не следует назначать одному пользователю более одной такой роли. Если один пользователь будет включен, например, в две роли с RLS — бухгалтер и кадровик, то при выполнении всех его запросов к их условиям будут добавляться условия обоих RLS с использованием логического ИЛИ. Таким образом, даже если в исходном запросе нет условия ИЛИ, оно появится там после добавления условий RLS. Такой запрос так же может выполняться неоптимально — медленно и с избыточными блокировками.

Условия отбора данных в запросе 1С

Механизм запросов — это один из способов доступа к данным, которые поддерживает платформа 1С:Предприятие. Используя этот механизм, разработчик может читать и обрабатывать данные, хранящиеся в информационной базе.

Условия, задаваемые в конструкции ВЫБРАТЬ

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

Количество всевозможных значений, отличных от NULL , для некоторого поля в таблице:

• КОЛИЧЕСТВО(<Имя поля>)

Для определения числа различных записей:

Неверно (выведет общее число записей):

Условия, задаваемые в конструкции ГДЕ

ГДЕ — условия отбора данных из таблицы, причем поле конструкции ГДЕ может не входить в список выборки, например:

Особенности использования конструкции ГДЕ:

1. В предложении ГДЕ можно использовать как имена полей, так и их псевдонимы.
2. Условие отбора может определяться и как простое логическое выражение, и как сложное, в котором простые логические выражения соединяются между собой логическими операторами И , ИЛИ , НЕ . Порядок можно менять с использованием круглых скобок.
3. Допускается использование оператора МЕЖДУ , который проверяет результат вхождения значения в указанный диапазон.
4. Допускается использование оператора ПОДОБНО для сравнения значения со строкой шаблона вида «%строка%» .
5. Допускается использование параметров, которые обозначаются символом «&» (например, параметр &Клиент или &Цена ).

Особенности использования оператора ПОДОБНО :

1. может размещаться:
   • в блоке условий, обозначенном ключевым словом ГДЕ ;
   • в конструкции Выбор Когда Тогда Иначе Конец ;
   • непосредственно в полях выборки, как результат сравнения полей;
2. специальные символы, облегчающие разработку:
   • «%»– последовательность любых символов произвольной длины, используется для поиска отдельных слов или цифр в строке;
   • «_»– любой единичный символ для обозначения наличия одного знака;
   • «[…]»– последовательность символов для сравнения со знаком в строке, проверяется соответствие любому из перечисленных в скобках знаков (можно задать диапазон цифр или букв — [а-г] или [1-7]);
   • «[^…]» – шаблон, противоположный предыдущему, проверяется отличие указанного в строке символа от перечисленных в скобках.

Конструкция ВЫБОР (КОГДА … ТОГДА) ИНАЧЕ … КОНЕЦ

Для формирования поля выборки может использоваться операция выбора ВЫБОР (КОГДА … ТОГДА) ИНАЧЕ … КОНЕЦ , в которой:

• после ключевого слова КОГДА записывается условие выбора;
• после ключевого слова ТОГДА следует значение поля выборки в случае, если условие КОГДА истинно;
• после слова ИНАЧЕ указывается значение, которое используется в качестве результата операции выбора в том случае, если ни одно из ранее указанных альтернативных условий выбора КОГДА не было выполнено.

В общем случае в операции выбора может указываться неограниченное количество альтернативных одиночных выборов КОГДА … ТОГДА.

Добавить комментарий Отменить ответ

Для отправки комментария вам необходимо авторизоваться.

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.

Запросы

Запросы представляют собой, табличную модель доступа к данным. С помощью запросов удобно организовывать чтение данных, по той причине, что с помощью одного запроса (здесь читаем обращения к серверу) можно в большинстве случаев получить все необходимые для работы данные. В случае с объектной моделью доступа(работа с классом Справочник.Выборка ), при получении каждого элемента в переборе система будет обращаться к серверу. В том случае если база 1С работает под управлением СУБД (клиент-серверный вариант) использование запросов является более оптимальным, потому что работа СУБД ориентирована на использование запросов. Результатом запроса всегда является одна таблица за исключением специфических случаев (например в случае пакетного запроса).

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

Виды таблиц

Таблицы в 1С делятся на реальные и виртуальные. Реальные таблицы, это те которые действительно существуют в базе данных. Виртуальные таблицы в базе не существуют, и представляют по сути, функции которые возвращают определенным образом отфильтрованные данные из реальных таблиц. Эти таблицы сделаны для удобства разработчиков. У виртуальных таблиц могут быть параметры. Для таблиц оборотов регистра накопления к примеру можно указать дату начала и дату окончания выборки оборотов. А для таблицы остатков этого же регистра, можно указать только дату, на которую будут получены остатки. Для этой таблицы можно указать условие (этим условием может быть отдельный запрос) и тогда будет наложен фильтр с указанным условием.

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

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

Основной синтаксис языка запросов.

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

Таблицы и поля

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

Группировка строк запроса.

При формировании запроса очень часто бывает необходимо "свернуть" данные запроса, то есть к примеру у нас в результате выборки получились такие записи

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

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

Условие и параметры в запросе

На результат выборки запроса, можно накладывать условия, которые могут быть достаточно разнообразными, в качестве условия могут выступать строковые или числовые константы, значения перечислений, предопределенные элементы справочников, подзапросы или любое значение переданное в параметре запроса. Для того, что бы указать, что выражение является параметром, перед ним указывают символ "&". За указание условия в запросе, отвечает секция "ГДЕ"

Передать параметр в запрос, можно следующим образом:

Псевдонимы

В случаях когда результат запроса может быть сразу выгружен в табличную часть документа, или в набор записей регистра, необходимо, что бы выходные поля запроса имели имена идентичные именам принимающей таблицы. Для того что бы присвоить полю свое имя его можно указать на закладке Объединения/Псевдонимы или в тексте запроса в секции указания списка полей указать ключевое слово КАК НовоеИмяВыходногоПоля напротив нужно поля. Поле для которого указан псевдоним, подсвечивается в конструкторе жирным шрифтом.

Объединения

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

Соединения

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

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

Вложенные запросы

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

Сортировка

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

Тип данных NULL

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

Использование функций

Язык запросов обладает ограниченным набором функций, для обработки данных, так как считается, что запросы это средство для получения данных, а не для их обработки. Но необходимый минимум, все таки имеется. Например нет возможность получить дату без времени, но есть возможность привести дату к началу дня, месяца, года, с помощью функции, начало периода. Условный оператор тоже присутствует в виде функции "Выбор". Функция "Значение" позволяет в запросе получать значения перечислений, и к некоторым значениям системных перечислений(например вид движения регистра) или значений предопределенных значений справочника. Описание функций языка запросов, отсутствует во встроенном синтаксис помощнике, но его можно найти в справке которая находится в меню «Справка» или по кнопке F1. Там нужно открыть содержание справки (можно сразу его открывать из этого же меню, или по shift F1) и выбрать Встроенный язык – Работа с запросами – Синтаксис языка запросов – Ключевые слова и функции – Функции.

Получение данных из табличных частей документов

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

А вот так будет правильно:

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

Условия в запросе

Эти операторы возможно использовать во всех 4 ситуациях, но контекст (окружаемые переменные у них отличаются).

В первых двух случаях условия накладываются на поля выборки, например:

ВЫБРАТЬ Ссылка ИЗ Справочник.Контрагенты КАК СпрКонтрагенты ГДЕ СпрКонтрагенты.ИНН<>»»

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

Данное условие вызывает ограничение выборки (обычно уменьшает ее)

В конструкторе запроса находятся на вкладке условия:

В произвольном варианте пишется текстом, в обычном выбирается поле, оператор сравнения и значение:

ВЫБОР КОГДА

Этот условный оператор используется для преобразования значения, ограничивает же выборку только когда расположен после служебного слова ГДЕ.
Допустимо несколько подчиненных условий. Может находится в полях выборки, полях условий ГДЕ, группировок, итогов (универсальный оператор для сложных условий)

Например, так выглядит вариант преобразования:

ВЫБРАТЬ
ВЫБОР
КОГДА 1 = 2
ТОГДА «Никогда не будет истиной»
КОГДА 1 = 1
ТОГДА «Всегда будет истиной»
ИНАЧЕ ДАТАВРЕМЯ(1, 1, 1)
КОНЕЦ КАК ПолеПредставленияЗначения

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

Пример № 1

ВЫБРАТЬ Ссылка ИЗ Справочник.Контрагенты КАК К ГДЕ ВЫБОР КОГДА К.ИНН = «» ТОГДА ЛОЖЬ ИНАЧЕ ИСТИНА КОНЕЦ

Пример № 2:

ВЫБРАТЬ Ссылка ИЗ Справочник.Контрагенты КАК К ГДЕ ВЫБОР КОГДА К.ИНН = «» ТОГДА «» ИНАЧЕ «Заполнено» КОНЕЦ = «Заполнено»

Параметры виртуальных таблиц

В данном случае оперирование идет с полями таблиц, например ресурсами, измерениями, реквизитами. В этих условиях обращение идет без точки (контекст этого не требует).

ВЫБРАТЬ * ИЗ РегистрСведений.КурсыВалют.СрезПоследних(, Валюта.Код = «RUR»)

В конструкторе запроса они находятся вот здесь:

Стоит отметить, что обычно используется ограничение над измерениями, так как иначе условие отнесется к выборке для получения, а не на сам результат (на ресурсы ограничения следует наложить в операторе ГДЕ, чтобы получить ожидаемый результат).

Накладывание условия в запросе на измерения, если нет обращения через точку (как в примере выше), ускоряет выборку: на больших таблицах, это будет визуально заметно; на небольших можно использовать как удобно.

ИМЕЮЩИЕ

Для наложения отбора для результата «группировочных» функции

ВЫБРАТЬ
Контрагенты.ИНН,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Контрагенты.Ссылка) КАК Ссылка
ИЗ
Справочник.Контрагенты КАК Контрагенты

СГРУППИРОВАТЬ ПО
Контрагенты.ИНН

ИМЕЮЩИЕ
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Контрагенты.Ссылка) > 1 И
Контрагенты.ИНН <> «»

В данную выборку попадут дубли ИНН, когда оно вообще заполнено.

В конструкторе запроса также находится на вкладке условия

В этой конструкции также возможно использование «группировочных» операторов И,ИЛИ, но сравнивать можно с константами запроса, с функцией или значением, входящим в группировку (другие реквизиты справочника в данном примере не будут доступны для сравнения -> тогда следует использовать ГДЕ).

Реклама – самая интересная и самая трудная форма литературы.

— Олдос Хаксли.