Как отобрать документы реализации, не содержащие определенную номенклатуру?

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

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

Типичная ошибка: прямая фильтрация строк табличной части

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

ВЫБРАТЬ РАЗЛИЧНЫЕ
    РеализацияТовары.Ссылка КАК Ссылка
ИЗ
    Документ.РеализацияТоваровУслуг.Товары КАК РеализацияТовары
ГДЕ
    НЕ РеализацияТовары.Номенклатура В (&СписокНоменклатуры)

Выясним причину, почему такой запрос возвращает неверный результат. Если в документе реализации присутствуют две строки — одна с исключаемым товаром, а вторая с обычным, то условие в секции ГДЕ отфильтрует только первую строку. Вторая строка пройдет проверку, и документ все равно попадет в итоговую выборку. Следовательно, фильтрация должна выполняться не построчно, а на уровне всего документа.

Способ 1. Использование вложенного запроса с конструкцией НЕ В

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

ВЫБРАТЬ
    Реализация.Ссылка КАК Ссылка,
    Реализация.Номер КАК Номер,
    Реализация.Дата КАК Дата
ИЗ
    Документ.РеализацияТоваровУслуг КАК Реализация
ГДЕ
    НЕ Реализация.Ссылка В
        (ВЫБРАТЬ РАЗЛИЧНЫЕ
            Товары.Ссылка
        ИЗ
            Документ.РеализацияТоваровУслуг.Товары КАК Товары
        ГДЕ
            Товары.Номенклатура В (&СписокНоменклатуры))

Такой вариант понятен и прост в написании, однако при обработке больших массивов данных конструкция НЕ ... В может приводить к неоптимальному плану выполнения в СУБД (Index Scan или полное сканирование таблицы).

Способ 2. Левое соединение с отбором по NULL

Более производительный подход заключается в использовании левого соединения основной таблицы документов с выборкой документов-исключений и последующей проверке на отсутствие соответствия через ЕСТЬ NULL.

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

В этом случае к каждому документу присоединяется запись из выборки исключений. Если документ содержит запрещенную номенклатуру, поле ИсключаемыеДокументы.Ссылка будет заполнено. Если не содержит — оно примет значение NULL, что и отбирается в секции ГДЕ.

Способ 3. Оптимальный пакетный запрос на временных таблицах

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

Разберем по шагам создание такого запроса:

1. Формируем временную таблицу ВТ_ВсеДокументы с отбором по периоду и статусу проведения.
2. Собираем во временную таблицу ВТ_ИсключаемыеСсылки все документы, где нужная номенклатура фигурирует в табличных частях Товары или Услуги через оператор ОБЪЕДИНИТЬ.
3. Индексируем временные таблицы по полю Ссылка.
4. Соединяем таблицы левым соединением и фильтруем по условию ЕСТЬ NULL.

Посмотрим на пример итогового пакетного запроса:

ВЫБРАТЬ
    Реализация.Ссылка КАК Ссылка
ПОМЕСТИТЬ ВТ_ВсеДокументы
ИЗ
    Документ.РеализацияТоваровУслуг КАК Реализация
ГДЕ
    Реализация.Проведен = ИСТИНА
    И Реализация.Дата МЕЖДУ &ДатаНачала И &ДатаОкончания
ИНДЕКСИРОВАТЬ ПО
    Ссылка
;

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

ОБЪЕДИНИТЬ

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

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВсеДокументы.Ссылка КАК Ссылка
ИЗ
    ВТ_ВсеДокументы КАК ВсеДокументы
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ИсключаемыеСсылки КАК Исключаемые
    ПО ВсеДокументы.Ссылка = Исключаемые.Ссылка
ГДЕ
    Исключаемые.Ссылка ЕСТЬ NULL

Особенности работы с иерархией и СКД

При реализации отбора обратим внимание на следующие практические моменты:

1. Работа с группами товаров: Если отбор выполняется по группе номенклатуры, в условии необходимо использовать конструкцию В ИЕРАРХИИ (&СписокНоменклатуры). Для максимальной производительности рекомендуется предварительно развернуть иерархию в плоский список ссылок перед передачей в параметры запроса.
2. Ограничения в СКД: В Системе компоновки данных стандартный отбор по полю табличной части вида Товары.Номенклатура Не в списке работает построчно и приведет к ошибке. Для корректной фильтрации в СКД необходимо использовать наборы данных с вышеописанной логикой соединения или связывать два набора данных через левое соединение.