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

← На главную

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

Анализ проблемы и типичные ошибки группировки

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

Если мы попытаемся применить секцию СГРУППИРОВАТЬ ПО напрямую в выборке детальных полей, платформа выдаст ошибку:


ВЫБРАТЬ
    ВводДанных.Дата,
    ВводДанных.Товар,
    ВводДанных.Поставщик,
    ВводДанных.Сумма
ИЗ
    Документ.ВводДанных КАК ВводДанных
СГРУППИРОВАТЬ ПО
    ВводДанных.Товар
ИМЕЮЩИЕ
    КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ВводДанных.Поставщик) > 1

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

Вторая частая ошибка возникает при попытке написать фильтр через условие В (ПОДЗАПРОС), возвращая из подзапроса более одной колонки:


// Ошибочный вариант: подзапрос возвращает две колонки
ГДЕ СправочникТовар.Ссылка В
    (ВЫБРАТЬ
        ВводДанных.Товар,
        КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ВводДанных.Поставщик)
    ИЗ
        Документ.ВводДанных КАК ВводДанных
    СГРУППИРОВАТЬ ПО
        ВводДанных.Товар
    ИМЕЮЩИЕ
        КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ВводДанных.Поставщик) > 1)

Конструкция ГДЕ Поле В (...) ожидает от подзапроса строго одну колонку с сопоставимым типом данных.

Способ 1. Пакетный запрос с временной таблицей (рекомендуемый)

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

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

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


ВЫБРАТЬ
    ВводДанных.Товар КАК Товар
ПОМЕСТИТЬ ВТ_ТоварыНесколькоПоставщиков
ИЗ
    Документ.ВводДанных КАК ВводДанных
СГРУППИРОВАТЬ ПО
    ВводДанных.Товар
ИМЕЮЩИЕ
    КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ВводДанных.Поставщик) > 1
ИНДЕКСИРОВАТЬ ПО
    Товар
;

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

Важный момент: использование директивы ИНДЕКСИРОВАТЬ ПО ускоряет последующее соединение таблиц на стороне СУБД и исключает полное сканирование выборки.

Способ 2. Использование условия с вложенным подзапросом

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


ВЫБРАТЬ
    ВводДанных.Дата КАК Дата,
    ВводДанных.Товар КАК Товар,
    ВводДанных.Поставщик КАК Поставщик,
    ВводДанных.Сумма КАК Сумма
ИЗ
    Документ.ВводДанных КАК ВводДанных
ГДЕ
    ВводДанных.Товар В
        (ВЫБРАТЬ
            Вложенный.Товар
        ИЗ
            Документ.ВводДанных КАК Вложенный
        СГРУППИРОВАТЬ ПО
            Вложенный.Товар
        ИМЕЮЩИЕ
            КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Вложенный.Поставщик) > 1)

Альтернативная оптимизация проверки условия

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


ИМЕЮЩИЕ
    МИНИМУМ(ВводДанных.Поставщик) <> МАКСИМУМ(ВводДанных.Поставщик)

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

Важные практические нюансы

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