Как найти разницу между таблицей значений и справочником в запросе 1С

← На главную

В практике разработчика 1С часто возникает задача: есть внешние данные (например, загруженные из Excel или полученные из другой системы) в виде таблицы значений (ТЗ), и нужно определить, какие из этих записей еще не созданы в справочнике или отличаются от него. На первый взгляд задача кажется тривиальной — использовать левое соединение. Однако на практике программисты сталкиваются с тем, что запрос возвращает некорректные данные, дубли или пустые выборки. Рассмотрим подробно, как правильно решить эту задачу, учитывая нюансы типизации и особенности работы СУБД.

Постановка задачи

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

Шаг 1. Подготовка и типизация таблицы значений

Проанализируем первую и самую распространенную причину неудач — отсутствие строгой типизации колонок ТЗ. Когда мы передаем таблицу значений в качестве параметра запроса, платформа 1С должна сопоставить типы данных ТЗ с типами данных в SQL-таблицах. Если типы не определены явно, соединение может не сработать или работать крайне медленно.

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


тзНоменклатураДата = Новый ТаблицаЗначений;
тзНоменклатураДата.Колонки.Добавить("Номенклатура", Новый ОписаниеТипов("СправочникСсылка.Номенклатура"));
тзНоменклатураДата.Колонки.Добавить("ДатаПартии", Новый ОписаниеТипов("Дата"));
// Заполнение таблицы данными...

Без использования ОписаниеТипов колонки будут иметь тип Произвольный, что в контексте запроса превратится в NULL или некорректное сопоставление типов в базе данных.

Шаг 2. Загрузка ТЗ во временную таблицу

Для эффективного сравнения мы поместим нашу ТЗ во временную таблицу. Это позволит нам не только обращаться к данным несколько раз, но и создать индексы для ускорения поиска.

Рассмотрим структуру запроса:


ВЫБРАТЬ
    НоменклатураДата.Номенклатура КАК Номенклатура,
    НоменклатураДата.ДатаПартии КАК ДатаПартии
ПОМЕСТИТЬ ВТ_ДанныеТЗ
ИЗ
    &НоменклатураДата КАК НоменклатураДата
ИНДЕКСИРОВАТЬ ПО
    Номенклатура,
    ДатаПартии
;

Важный момент: Использование инструкции ИНДЕКСИРОВАТЬ ПО критически важно, если в вашей таблице более нескольких сотен строк. Это создает временный индекс в базе данных, что превращает тяжелое соединение в быструю операцию поиска.

Шаг 3. Использование левого соединения для поиска разницы

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

Разберем пример работающего запроса:


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

В этом запросе мы выбираем только те строки из исходной таблицы, для которых ссылка на элемент справочника ЕСТЬ NULL. Это и есть искомая нами «разница».

Шаг 4. Решение проблемы с типом Дата

Часто программисты сталкиваются с тем, что соединение не срабатывает из-за разницы во времени. В справочнике дата может храниться без времени (00:00:00), а в ТЗ — с секундами. Даже если визуально даты одинаковы, для СУБД 11.09.2015 00:00:00 и 11.09.2015 14:30:15 — это абсолютно разные значения.

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

Скорректируем наше условие соединения:


ПО ДанныеТЗ.Номенклатура = СпрПартии.Номенклатура
    И НАЧАЛОПЕРИОДА(ДанныеТЗ.ДатаПартии, ДЕНЬ) = НАЧАЛОПЕРИОДА(СпрПартии.ДатаПартии, ДЕНЬ)

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

Альтернативный вариант: Оператор "НЕ В"

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


ВЫБРАТЬ
    ДанныеТЗ.Номенклатура,
    ДанныеТЗ.ДатаПартии
ИЗ
    ВТ_ДанныеТЗ КАК ДанныеТЗ
ГДЕ
    НЕ (ДанныеТЗ.Номенклатура, ДанныеТЗ.ДатаПартии) В
        (ВЫБРАТЬ
            Спр.Номенклатура,
            Спр.ДатаПартии
        ИЗ
            Справочник.Партии КАК Спр)

Этот метод выглядит более лаконичным, но имеет свои подводные камни:

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