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

← На главную

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

Почему виртуальная таблица остатков не возвращает нули

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

Решение: левое соединение справочника с виртуальной таблицей остатков

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

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

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


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

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

Почему сортировка по номенклатуре работает некорректно

Выясним причину, по которой конструкция УПОРЯДОЧИТЬ ПО Номенклатура не дает ожидаемой сортировки по алфавиту. Поле Номенклатура содержит ссылочный тип данных (тип Ссылка). В базе данных ссылка представляет собой 16-байтный глобальный уникальный идентификатор (UUID). Когда мы пишем упорядочивание по ссылочному полю без указания конкретного реквизита, СУБД сортирует записи по их бинарному внутреннему представлению, что визуально выглядит как случайный порядок.

Рассмотрим два способа исправления ситуации:

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

Причины «задвоения» строк и остатков

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

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

Посмотрим на пример запроса с группировкой, решающего проблему дублирования:


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

Почему результаты могут различаться в консоли запросов и коде обработки

Если в консоли запросов выборка выдает одни цифры, а в модуле обработки — другие, проанализируем следующие моменты:

Передача параметра &Период: тип данных Дата в 1С всегда включает время. Если передать дату без времени (например, 01.05.2024 00:00:00), то виртуальная таблица рассчитает остаток на самое начало этой секунды, и движения, проведенные в эту же дату, в выборку не попадут. В консолях запросов период нередко задают концом дня (23:59:59) либо оставляют пустым (на момент времени «сейчас»). Если требуется получить остатки на конкретный документ в модуле, следует передавать объект Граница с видом ВидГраницы.Включая или метод МоментВремени().
Ограничения прав доступа (RLS): консоль запросов часто запускается разработчиком под полными правами без ограничений на уровне записей. В то же время модуль обработки у пользователя может работать под ролью с RLS (например, с ограничением доступа по складам или организациям), что приводит к отсечению части строк.
Передача больших массивов: если в параметр &СписокНоменклатуры передается массив из десятков тысяч элементов, формируемый SQL-запрос может стать неоптимальным. В таких случаях правильнее загружать список во временную таблицу через МенеджерВременныхТаблиц с обязательным созданием индекса по полю ссылки, после чего выполнять левое соединение уже с этой временной таблицей.