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

← На главную

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

Анализируем проблему: ловушка Левого Соединения

Рассмотрим ситуацию, описанную автором темы. В запросе используются две временные таблицы: втОстатки и втПравила. Задача состоит в том, чтобы получить остатки только по той номенклатуре, которой нет в таблице правил. Автор использует конструкцию:


ЛЕВОЕ СОЕДИНЕНИЕ втОстатки КАК втОстатки 
ПО (втОстатки.Номенклатура = втПравила.Номенклатура) 
ГДЕ втОстатки.Номенклатура = втПравила.Номенклатура

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

Решение 1: Использование ЛЕВОГО СОЕДИНЕНИЯ и проверки на NULL

Это классический и наиболее производительный метод исключения записей (так называемый Anti-Join). Чтобы оставить только те позиции из таблицы остатков, которых нет в правилах, нам нужно соединить остатки (слева) с правилами (справа) и отобрать строки, где в правой таблице образовался NULL.

Проанализируем правильную структуру такого запроса:


ВЫБРАТЬ
    втОстатки.Номенклатура,
    втОстатки.Артикул,
    втОстатки.Остаток
ИЗ
    втОстатки КАК втОстатки
    ЛЕВОЕ СОЕДИНЕНИЕ втПравила КАК втПравила
    ПО втОстатки.Номенклатура = втПравила.Номенклатура
ГДЕ
    втПравила.Номенклатура ЕСТЬ NULL

В данном случае мы берем всю номенклатуру из остатков. Если номенклатура есть в правилах, поле втПравила.Номенклатура будет заполнено ссылкой. Если же её в правилах нет — там будет NULL. Условие ЕСТЬ NULL отсекает все лишнее, оставляя только нужные нам данные.

Решение 2: Использование конструкции ГДЕ НЕ ... В (...)

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


ВЫБРАТЬ
    втОстатки.*
ИЗ
    втОстатки КАК втОстатки
ГДЕ
    НЕ втОстатки.Номенклатура В (ВЫБРАТЬ втПравила.Номенклатура ИЗ втПравила)

Важный технический нюанс: при использовании В (ВЫБРАТЬ...) следует убедиться, что в подзапросе нет значений NULL, иначе результат всего условия может стать неопределенным для некоторых СУБД (например, SQL Server). В 1С это обычно не является проблемой для ссылочных типов, но об этом стоит помнить.

Оптимизация работы с временными таблицами

В исходном коде автора данные передаются в запрос через параметры &ТаблицаОстатки и &Правила. Рассмотрим подробнее, как оптимизировать этот процесс, так как передача больших Таблиц Значений (ТЗ) может замедлять работу системы.

Индексация: При создании временных таблиц из внешних источников (ТЗ) обязательно используйте ИНДЕКСИРОВАТЬ ПО. В нашем примере связь идет по полю Номенклатура. Без индекса поиск соответствий при соединении таблиц будет выполняться крайне медленно.
Типизация полей: Обратите внимание на конструкцию ВЫРАЗИТЬ(Артикул КАК СТРОКА(50)). Это хорошая практика, так как она помогает оптимизатору SQL заранее знать длину строковых полей и строить более эффективный план запроса.
Минимизация колонок: В примере автора запроса выбирается огромное количество колонок (ОстаткиСкладов1...ОстаткиСкладов8 и так далее). Если данные для этих колонок берутся из регистров 1С, лучше делать расчет прямо в запросе, а не передавать их в него из заранее подготовленной таблицы значений.

Обработка NULL в расчетах

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

Посмотрим на пример правильной обработки:


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

Или более лаконичный вариант с использованием функции ЕСТЬNULL:


ЕСТЬNULL(втПравила.КоличествоДляЗамены, втОстатки.ИсточникОстаткиСкладов1) КАК ОстаткиСкладов1

Динамическое формирование запроса

В коде автора заметна структура колонок с суффиксами от 1 до 12. Ручное написание таких запросов чревато опечатками. Проанализируем, как можно упростить поддержку такого кода. Вместо копирования блоков ВЫБОР КОГДА... 12 раз, можно использовать динамический текст запроса в модуле 1С:


ТекстВыбора = "";
Для Номер = 1 По 12 Цикл
    ТекстВыбора = ТекстВыбора + ", ВЫБОР КОГДА &ПрименятьЗамену" + Номер + " ТОГДА ... ИНАЧЕ ... КОНЕЦ";
КонецЦикла;
Запрос.Текст = СтрЗаменить(Запрос.Текст, "%ПоляСкладов%", ТекстВыбора);

Это позволит сократить размер исходного кода и сделает его более гибким при изменении количества складов или групп аналитики.

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