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

← На главную

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

Почему ПОЛНОЕ СОЕДИНЕНИЕ перестает работать при использовании условий

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

Когда мы пишем условие в секции ГДЕ, например: ЗаказыПоставщикамОстатки.Номенклатура.НоменклатурнаяГруппа В ИЕРАРХИИ(&НоменклатурнаяГруппа), система ожидает, что в поле таблицы будет конкретное значение. Но если по какому-то товару нет данных в регистре заказов, то в результате ПОЛНОЕ СОЕДИНЕНИЕ в этом поле будет стоять NULL. Сравнение NULL с группой номенклатуры всегда возвращает «Ложь». В итоге строки, где хотя бы в одной из таблиц нет данных, просто отсекаются.

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

Решение 1: Перенос фильтров в параметры виртуальных таблиц

Самый правильный и производительный способ решения проблемы — наложение фильтров непосредственно при обращении к виртуальным таблицам. Это позволяет платформе 1С отобрать нужные данные еще на этапе обращения к базе данных, а не фильтровать уже соединенный массив строк.

Посмотрим, как должен выглядеть оптимизированный запрос:


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

Обратите внимание на использование функции ЕСТЬNULL. При полном соединении мы не знаем заранее, в какой из таблиц окажется значение измерения (Номенклатура или Склад). Поэтому мы «проверяем» каждую таблицу по очереди. Если в первой NULL, берем значение из второй, если и там NULL — из третьей.

Решение 2: Использование конструкции ОБЪЕДИНИТЬ ВСЕ

Для опытных разработчиков 1С использование ОБЪЕДИНИТЬ ВСЕ является более предпочтительным методом, чем ПОЛНОЕ СОЕДИНЕНИЕ. Это связано с тем, что операция объединения (UNION) выполняется SQL-сервером гораздо быстрее и легче, чем сложные соединения по нескольким полям. Кроме того, такой код легче читать и отлаживать.

Разберем алгоритм работы этого метода:

Выбираем данные из каждого регистра отдельными запросами в одном пакете.
Во всех запросах делаем одинаковый набор полей. Если в текущем регистре нет нужного ресурса (например, «Резерв» в таблице остатков), ставим в это поле 0.
Объединяем результаты всех запросов.
Группируем итоговый результат по измерениям (Склад, Номенклатура).

Пример структуры такого запроса:


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

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

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

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

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

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

Как вывести товары, по которым остатков вообще нет

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

Проанализируем шаги для решения этой задачи:

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

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

Оптимизация работы с «точками» и разыменованием

В тексте запроса часто встречается обращение Номенклатура.НоменклатурнаяГруппа. Каждое такое обращение — это неявное соединение с таблицей справочника номенклатуры. В больших отчетах это может замедлять работу.

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


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

// Шаг 2: Используем временную таблицу как фильтр в параметрах
ВЫБРАТЬ ... ИЗ РегистрНакопления.ТоварыНаСкладах.Остатки(, Номенклатура В (ВЫБРАТЬ Товар ИЗ ВТ_СписокТоваров))

Использование временных таблиц делает запрос структурированным, а вывод нулевых остатков — предсказуемым. Следуя этим правилам, мы избавимся от «фантомного» исчезновения строк при пустых значениях в одном из регистров.