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

← На главную

При разработке отчетов на системе компоновки данных (СКД) часто возникает задача вывести полный список справочника (например, Номенклатура) вместе с текущими остатками. При этом стандартное Левое соединение выводит абсолютно все записи. Однако для чистоты отчета заказчики часто просят скрыть те товары, которые уже не используются (помечены на удаление) и по которым нет складских остатков. Если же товар помечен на удаление, но остаток по нему все еще числится, он должен попасть в отчет для анализа.

В этой статье мы подробно разберем, как правильно составить такой запрос, какие логические условия использовать и как оптимизировать производительность при работе с большими справочниками.

Постановка задачи и базовый запрос

Рассмотрим исходную ситуацию. Нам необходимо получить список всей номенклатуры определенного типа (например, «Запас») и соединить ее с остатками из регистра накопления Запасы. Мы используем временную таблицу для предварительного отбора справочника, чтобы не тянуть лишние данные (группы или услуги) в основную выборку.

Начальный текст запроса выглядит следующим образом:


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

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

Этот запрос выведет всю номенклатуру. Теперь нам нужно добавить условие: если ПометкаУдаления = ИСТИНА И Количество = 0, то такая запись не должна попасть в результат.

Решение 1: Использование отрицательного условия в секции ГДЕ

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

Проанализируем пример кода с использованием оператора НЕ:


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

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

Решение 2: Использование конструкции ВЫБОР (логическое соответствие)

Другой подход заключается в явном описании того, что мы хотим видеть. Это решение часто кажется более читаемым для других разработчиков. Мы говорим: «Если стоит пометка на удаление, то проверяй, чтобы остаток был больше нуля. В противном случае (если пометки нет) — выводи запись всегда».

Рассмотрим реализацию через ВЫБОР:


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

Этот вариант работает идентично первому, но более наглядно разделяет логику обработки «живых» и «удаленных» карточек товаров.

Почему нельзя наложить это условие в параметрах виртуальной таблицы?

Часто возникает вопрос: почему мы пишем условие в секции ГДЕ, а не в параметрах виртуальной таблицы остатков РегистрНакопления.Запасы.Остатки(...)? Выясним причину. Виртуальная таблица Остатки формирует агрегированные данные (итоги) на лету. Параметры внутри скобок позволяют фильтровать измерения (Склад, Номенклатура, Организация) до того, как начнется расчет остатков.

Поле КоличествоОстаток — это ресурс. Наложить условие на результат вычисления ресурса внутри параметров виртуальной таблицы невозможно технически. Такие условия всегда накладываются либо в секции ГДЕ, либо в секции ИМЕЮЩИЕ (если используется группировка).

Оптимизация производительности (Best Practices)

Если в вашем справочнике Номенклатура десятки или сотни тысяч записей, простое левое соединение может начать «тормозить». Рассмотрим несколько шагов по оптимизации нашего запроса.

Индексация временных таблиц. Всегда индексируйте поля, по которым происходит соединение во временных таблицах. В нашем случае это втНоменклатура.
Избегайте «разыменования» полей через точку. Обращение втСправочникНоменклатура.втНоменклатура.ПометкаУдаления заставляет систему делать дополнительное неявное соединение с таблицей справочника. Лучше выбрать поле ПометкаУдаления сразу в первую временную таблицу.
Правильный порядок соединений. Хотя 1С и SQL-оптимизатор стараются сами определить план запроса, старайтесь делать основной таблицей ту, которая максимально ограничивает выборку.

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


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

ИНДЕКСИРОВАТЬ ПО
    Ссылка;

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

Альтернативный метод: Объединение (Union)

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

Выбрать всю номенклатуру без пометки на удаление (простое левое соединение с остатками).
Выбрать только помеченную на удаление номенклатуру через Внутреннее соединение (оно автоматически отсечет записи с нулевым остатком).

Пример реализации через ОБЪЕДИНИТЬ ВСЕ:


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

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

-- Часть 2: Только удаленные, но с остатками
ВЫБРАТЬ ТЗ.Ссылка, Остатки.КоличествоОстаток
ИЗ втНоменклатураСИндексом КАК ТЗ
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрНакопления.Запасы.Остатки КАК Остатки ПО ТЗ.Ссылка = Остатки.Номенклатура
ГДЕ ТЗ.ПометкаУдаления

Преимущество этого метода: Внутреннее соединение работает значительно быстрее левого на больших объемах, а в первой части запроса условие фильтрации ПометкаУдаления очень простое для оптимизатора SQL.

Подведение итогов

Мы рассмотрели несколько способов решения задачи. Для небольших и средних баз достаточно использовать простое условие в секции ГДЕ с проверкой на NULL. Для высоконагруженных систем рекомендуется использовать временные таблицы с индексами и избегать обращений через точку в основном цикле запроса. Использование СКД позволяет также вынести эти условия в настройки отбора, однако программная реализация в тексте запроса гарантирует, что пользователь не увидит «мусорные» данные даже при базовых настройках отчета.