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

← На главную

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

Анализ типичных ошибок при составлении запроса

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


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

В этом примере допущены две фундаментальные ошибки:

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

Способ 1. Перенос условий в параметры соединения и виртуальной таблицы

Выясним, как исправить логику левого соединения. Чтобы номенклатура не пропадала из выборки при отсутствии складских остатков, условие по конкретному складу необходимо перенести непосредственно в условие связи ПО либо передать в параметры виртуальной таблицы остатков.

Посмотрим на исправленный вариант запроса:


ВЫБРАТЬ
    СпрНоменклатура.Код КАК Код,
    СпрНоменклатура.Артикул КАК Артикул,
    СпрНоменклатура.Ссылка КАК Ссылка,
    СпрНоменклатура.Наименование КАК Наименование,
    ЕСТЬNULL(ТоварыНаСкладахБО.КоличествоОстаток, 0) КАК ОбщийОстаток,
    ЕСТЬNULL(ТоварыНаСкладахОтбор.КоличествоОстаток, 0) КАК ОстатокПоСкладу
ИЗ
    Справочник.Номенклатура КАК СпрНоменклатура
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(&Дата, ) КАК ТоварыНаСкладахБО
        ПО СпрНоменклатура.Ссылка = ТоварыНаСкладахБО.Номенклатура
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(&Дата, Склад = &ВыбСклад) КАК ТоварыНаСкладахОтбор
        ПО СпрНоменклатура.Ссылка = ТоварыНаСкладахОтбор.Номенклатура
ГДЕ
    СпрНоменклатура.Наименование ПОДОБНО &ВыбНаименование

Разберем ключевые доработки:

Отбор Склад = &ВыбСклад перенесен в параметры виртуальной таблицы РегистрНакопления.ТоварыНаСкладах.Остатки. Это не только сохраняет работу левого соединения, но и существенно ускоряет выполнение запроса на уровне СУБД, так как выборка данных из базы ограничивается до выполнения соединения.
Поля остатков обернуты в функцию ЕСТЬNULL(..., 0), благодаря чему при отсутствии записей в регистре в отчет выводятся нули, а не пустые значения.

Способ 2. Оптимизированный запрос через одно соединение и условное суммирование

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

Рассмотрим пример реализации:


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

Важные рекомендации по повышению производительности

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