Как в запросе 1С отобрать документы, в табличной части которых отсутствует определенный признак?

← На главную

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

В чем заключается логическая ошибка классического соединения?

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


ЛЕВОЕ СОЕДИНЕНИЕ Документ.ПоступлениеТоваровУслуг.Товары КАК ПоступлениеТоваровУслугТовары
    ПО РегистрацияОбмена.Объект = ПоступлениеТоваровУслугТовары.Ссылка
        И (ПоступлениеТоваровУслугТовары.Номенклатура.ПризнакСтороннийПоставщик = ЛОЖЬ)
ГДЕ
    ПоступлениеТоваровУслугТовары.Ссылка ЕСТЬ NULL

Проанализируем, к чему приводит такая формулировка. В условии соединения отбираются строки, где признак равен ЛОЖЬ. Затем фильтр ГДЕ ПоступлениеТоваровУслугТовары.Ссылка ЕСТЬ NULL оставляет только те документы, к которым такие строки не присоединились. В результате мы получаем ровно противоположный эффект: выбираются документы, в которых либо вообще нет строк с признаком ЛОЖЬ, либо присутствуют исключительно строки со значением ИСТИНА.

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

В секции ПО левого соединения соединяем документ со строками, у которых признак установлен (= ИСТИНА).
В секции ГДЕ отсекаем найденные совпадения с помощью проверки Таблица.Ссылка ЕСТЬ NULL. В выборку попадут только те документы, для которых «запрещенных» строк не нашлось.

Решение 1. Использование исключающего ЛЕВОГО СОЕДИНЕНИЯ

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


ВЫБРАТЬ ПЕРВЫЕ 3
    РегистрацияОбмена.Объект КАК Объект,
    РегистрацияОбмена.Состояние КАК Состояние,
    РегистрацияОбмена.ДатаОбмена КАК ДатаОбмена,
    РегистрацияОбмена.Пользователь КАК Пользователь,
    РегистрацияОбмена.Номер КАК Номер,
    РегистрацияОбмена.Ошибок КАК Ошибок,
    РегистрацияОбмена.АдресЭлектроннойПочты КАК АдресЭлектроннойПочты
ИЗ
    РегистрСведений.РегистрацияОбменаЭлектроннымиДокументами КАК РегистрацияОбмена
    ЛЕВОЕ СОЕДИНЕНИЕ Документ.ПоступлениеТоваровУслуг.Товары КАК ПоступлениеТовары
        ПО РегистрацияОбмена.Объект = ПоступлениеТовары.Ссылка
            И (ПоступлениеТовары.Номенклатура.ПризнакСтороннийПоставщик = ИСТИНА)
ГДЕ
    РегистрацияОбмена.Состояние = &Состояние
    И РегистрацияОбмена.Ошибок < &КоличествоОшибок
    И РегистрацияОбмена.Объект ССЫЛКА Документ.ПоступлениеТоваровУслуг
    И НЕ ВЫРАЗИТЬ(РегистрацияОбмена.Объект КАК Документ.ПоступлениеТоваровУслуг).Организация В (&ПапкаКомиссионеров)
    И ПоступлениеТовары.Ссылка ЕСТЬ NULL
УПОРЯДОЧИТЬ ПО
    Ошибок,
    ВЫРАЗИТЬ(РегистрацияОбмена.Объект КАК Документ.ПоступлениеТоваровУслуг).Дата

Решение 2. Проверка через оператор НЕ СУЩЕСТВУЕТ (NOT EXISTS)

Посмотрим на альтернативный подход. Конструкция НЕ СУЩЕСТВУЕТ часто более наглядно отражает логику алгоритма: документ выбирается только тогда, когда в его табличной части отсутствует запись с флагом. Оптимизатор СУБД при такой конструкции прерывает сканирование строк документа сразу при нахождении первого совпадения.


ВЫБРАТЬ ПЕРВЫЕ 3
    РегистрацияОбмена.Объект КАК Объект,
    РегистрацияОбмена.Состояние КАК Состояние,
    РегистрацияОбмена.ДатаОбмена КАК ДатаОбмена,
    РегистрацияОбмена.Пользователь КАК Пользователь,
    РегистрацияОбмена.Номер КАК Номер,
    РегистрацияОбмена.Ошибок КАК Ошибок,
    РегистрацияОбмена.АдресЭлектроннойПочты КАК АдресЭлектроннойПочты
ИЗ
    РегистрСведений.РегистрацияОбменаЭлектроннымиДокументами КАК РегистрацияОбмена
ГДЕ
    РегистрацияОбмена.Состояние = &Состояние
    И РегистрацияОбмена.Ошибок < &КоличествоОшибок
    И РегистрацияОбмена.Объект ССЫЛКА Документ.ПоступлениеТоваровУслуг
    И НЕ ВЫРАЗИТЬ(РегистрацияОбмена.Объект КАК Документ.ПоступлениеТоваровУслуг).Организация В (&ПапкаКомиссионеров)
    И НЕ СУЩЕСТВУЕТ
        (
            ВЫБРАТЬ
                1
            ИЗ
                Документ.ПоступлениеТоваровУслуг.Товары КАК ТЧ_Товары
            ГДЕ
                ТЧ_Товары.Ссылка = РегистрацияОбмена.Объект
                И ТЧ_Товары.Номенклатура.ПризнакСтороннийПоставщик = ИСТИНА
        )
УПОРЯДОЧИТЬ ПО
    Ошибок,
    ВЫРАЗИТЬ(РегистрацияОбмена.Объект КАК Документ.ПоступлениеТоваровУслуг).Дата

Решение 3. Использование временной таблицы исключений

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

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


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

ИНДЕКСИРОВАТЬ ПО
    Ссылка
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ ПЕРВЫЕ 3
    РегистрацияОбмена.Объект КАК Объект,
    РегистрацияОбмена.Состояние КАК Состояние,
    РегистрацияОбмена.ДатаОбмена КАК ДатаОбмена,
    РегистрацияОбмена.Пользователь КАК Пользователь,
    РегистрацияОбмена.Номер КАК Номер,
    РегистрацияОбмена.Ошибок КАК Ошибок,
    РегистрацияОбмена.АдресЭлектроннойПочты КАК АдресЭлектроннойПочты
ИЗ
    РегистрСведений.РегистрацияОбменаЭлектроннымиДокументами КАК РегистрацияОбмена
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ИсключаемыеДокументы КАК Исключения
        ПО РегистрацияОбмена.Объект = Исключения.Ссылка
ГДЕ
    РегистрацияОбмена.Состояние = &Состояние
    И РегистрацияОбмена.Ошибок < &КоличествоОшибок
    И РегистрацияОбмена.Объект ССЫЛКА Документ.ПоступлениеТоваровУслуг
    И НЕ ВЫРАЗИТЬ(РегистрацияОбмена.Объект КАК Документ.ПоступлениеТоваровУслуг).Организация В (&ПапкаКомиссионеров)
    И Исключения.Ссылка ЕСТЬ NULL
УПОРЯДОЧИТЬ ПО
    Ошибок,
    ВЫРАЗИТЬ(РегистрацияОбмена.Объект КАК Документ.ПоступлениеТоваровУслуг).Дата

Важные нюансы при написании запроса

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

Разыменование составных типов: Измерение Объект в регистрах обмена почти всегда составное. Обращение через точку (Объект.Организация) без оператора ВЫРАЗИТЬ заставляет платформу строить неявные соединения со всеми таблицами типов, входящих в составное поле. Явное приведение типа ВЫРАЗИТЬ(Объект КАК Документ.ПоступлениеТоваровУслуг) полностью решает эту проблему.
Сортировка по моменту времени: Поле МоментВремени не хранится в физических таблицах СУБД. В запросах к составным полям надежнее и быстрее упорядочивать записи по реальным полям — дате и ссылке документа.
Пустые табличные части: При связке ЛЕВОЕ СОЕДИНЕНИЕ ... ЕСТЬ NULL документ с полностью пустой табличной частью также попадет в выборку, так как для него не найдется ни одной строки. Если пустые документы выбирать нельзя, необходимо добавить дополнительное условие на наличие строк.
Дополнительные табличные части: Обратите внимание, что в типовых конфигурациях номенклатура может присутствовать не только в табличной части Товары, но и в других разделах документа (например, Услуги). При необходимости отбора анализируйте все релевантные табличные части.
Особенности булевых значений и NULL: Проверка = ИСТИНА однозначно исключает как значение ЛОЖЬ, так и пустые значения NULL, которые могут присутствовать в базе при добавлении новых реквизитов к уже существующим объектам.