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

← На главную

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

Простая фильтрация через конструкцию ГДЕ Номенклатура В (&Список1) ИЛИ Номенклатура В (&Список2) не решает задачу, так как вернет документы, содержащие позицию хотя бы из одного списка. Условие с логическим И непосредственно к строке табличной части (ГДЕ Номенклатура В (&Список1) И Номенклатура В (&Список2)) также не сработает, поскольку в одной строке документа не может одновременно находиться несколько разных ссылок на номенклатуру (за исключением случая, когда один и тот же товар входит во все списки сразу).

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

Способ 1. Оптимальный: объединение списков с разметкой и группировка (за один проход)

Наиболее производительный вариант заключается в сведении всех исходных списков в одну временную таблицу с дополнительным признаком — номером списка. Затем табличная часть документа соединяется с этой таблицей, группируется по документу, а итоговая фильтрация выполняется в секции ИМЕЮЩИЕ по количеству различных номеров списков.

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


ВЫБРАТЬ
    Спис1.Номенклатура КАК Номенклатура,
    1 КАК НомерСписка
ПОМЕСТИТЬ ВТ_ВсеСписки
ИЗ
    &ТаблицаСписка1 КАК Спис1

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    Спис2.Номенклатура,
    2
ИЗ
    &ТаблицаСписка2 КАК Спис2

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    Спис3.Номенклатура,
    3
ИЗ
    &ТаблицаСписка3 КАК Спис3
;

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

Разберем, почему этот способ наиболее эффективен:

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

Способ 2. Внутреннее соединение временных таблиц по документам

Другой понятный и наглядный подход — для каждого списка получить отдельную временную таблицу с документами, а затем найти их пересечение через операторы ВНУТРЕННЕЕ СОЕДИНЕНИЕ.

Рассмотрим реализацию данного метода:


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

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

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

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ1.Ссылка КАК Документ
ИЗ
    ВТ_Документы1 КАК ВТ1
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Документы2 КАК ВТ2
        ПО ВТ1.Ссылка = ВТ2.Ссылка
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Документы3 КАК ВТ3
        ПО ВТ1.Ссылка = ВТ3.Ссылка

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

Способ 3. Использование подзапросов с логическим «И» в секции ГДЕ

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


ВЫБРАТЬ
    Счет.Ссылка КАК Документ
ИЗ
    Документ.СчетНаОплатуПокупателю КАК Счет
ГДЕ
    Счет.Проведен = ИСТИНА
    И Счет.Ссылка В
        (ВЫБРАТЬ ТЧ1.Ссылка ИЗ Документ.СчетНаОплатуПокупателю.Товары КАК ТЧ1 ГДЕ ТЧ1.Номенклатура В (&Список1))
    И Счет.Ссылка В
        (ВЫБРАТЬ ТЧ2.Ссылка ИЗ Документ.СчетНаОплатуПокупателю.Товары КАК ТЧ2 ГДЕ ТЧ2.Номенклатура В (&Список2))
    И Счет.Ссылка В
        (ВЫБРАТЬ ТЧ3.Ссылка ИЗ Документ.СчетНаОплатуПокупателю.Товары КАК ТЧ3 ГДЕ ТЧ3.Номенклатура В (&Список3))

В зависимости от СУБД такой запрос транслируется в полусоединения (Semi-Join / EXISTS), что позволяет оптимизатору быстро отсекать документы без создания явных временных таблиц.

Важные технические нюансы и рекомендации

Пересекающиеся позиции в исходных списках: если один и тот же товар входит одновременно в Список №1 и Список №2, то документ всего с одной строкой закроет условия обоих списков при сопоставлении. Если по бизнес-логике требуется наличие строго трех различных позиций в документе, необходимо дополнить секцию ИМЕЮЩИЕ проверкой: КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Товары.Номенклатура) >= 3.
Ограничения на размер массивов в параметрах: передача массивов из сотен или тысяч элементов напрямую через условие В (&Список) может привести к ошибкам СУБД (например, превышение лимита в 2100 параметров в MS SQL Server). В таких случаях списки следует передавать через МенеджерВременныхТаблиц в виде таблицы значений.
Индексация: при использовании временных таблиц со списками товаров обязательно указывайте директиву ИНДЕКСИРОВАТЬ ПО Номенклатура для применения быстрых алгоритмов соединения строк.
Иерархия номенклатуры: оператор В ИЕРАРХИИ неприменим в соединениях временных таблиц. Если исходные списки содержат группы номенклатуры, их следует развернуть до конечных элементов во встроенном языке перед передачей в запрос.