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

← На главную

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

Анализ проблемы: почему оператор ПОДОБНО не находит подстроку

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


ВЫБОР
    КОГДА ВТВнешние.НомерДокумента ПОДОБНО "%" + ВЫРАЗИТЬ(ВТВнутренние.НомерДокумента КАК Строка(100)) + "%"
        ТОГДА ИСТИНА
    ИНАЧЕ ЛОЖЬ
КОНЕЦ

На первый взгляд конструкция логична, но на практике она возвращает ЛОЖЬ даже при явном совпадении. Выясним причину такого поведения:

Дополнение строк пробелами: Конструкция ВЫРАЗИТЬ(Поле КАК Строка(N)) в языке запросов 1С преобразует значение в строку фиксированной длины N. Это означает, что СУБД дополняет строку пробелами справа до 100 символов. В результате шаблон поиска превращается в "%11234 %". Если в проверяемом поле после номера нет ровно такого количества пробелов, совпадение никогда не будет найдено.
Ограничения оператора ПОДОБНО: Согласно стандартам 1С, правым операндом конструкции ПОДОБНО должен быть строковый литерал или параметр запроса. Динамическая конкатенация полей справа от ПОДОБНО поддерживается не всеми СУБД, а также приводит к полному сканированию таблиц (Full Scan) и потере производительности.

Ошибка условий связи: почему пропадают строки при расхождении сумм

Вторая распространенная ошибка кроется в секции ПО при использовании полного соединения. Проанализируем фрагмент запроса:


ИЗ
    ВТВнешниеДанные КАК ВТВнешниеДанные
    ПОЛНОЕ СОЕДИНЕНИЕ ВТВнутренниеДанные КАК ВТВнутренниеДанные
        ПО ВТВнешниеДанные.ДатаДокумента = ВТВнутренниеДанные.ДатаДокумента
        И ВТВнешниеДанные.Сумма = ВТВнутренниеДанные.Сумма

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

Решение 1: Корректное построение запроса для сверки таблиц

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


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

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    НАЧАЛОПЕРИОДА(Внутренние.ДатаДокумента, ДЕНЬ) КАК ДатаДокумента,
    Внутренние.НомерДокумента КАК НомерДокумента,
    Внутренние.Сумма КАК Сумма,
    Внутренние.Регистратор КАК Регистратор
ПОМЕСТИТЬ ВТВнутренниеДанные
ИЗ
    &ВнутренниеДанные КАК Внутренние
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ЕСТЬNULL(ВТВнешние.НомерДокумента, "") КАК НомерВнешний,
    ЕСТЬNULL(ВТВнутренние.НомерДокумента, "") КАК НомерВнутренний,
    ЕСТЬNULL(ВТВнешние.ДатаДокумента, ВТВнутренние.ДатаДокумента) КАК ДатаДокумента,
    ЕСТЬNULL(ВТВнешние.Сумма, 0) КАК СуммаВнешняя,
    ЕСТЬNULL(ВТВнутренние.Сумма, 0) КАК СуммаВнутренняя,
    ЕСТЬNULL(ВТВнешние.Сумма, 0) - ЕСТЬNULL(ВТВнутренние.Сумма, 0) КАК РазницаСумм,
    ВТВнутренние.Регистратор КАК Регистратор
ИЗ
    ВТВнешниеДанные КАК ВТВнешние
    ПОЛНОЕ СОЕДИНЕНИЕ ВТВнутренниеДанные КАК ВТВнутренние
        ПО ВТВнешние.ДатаДокумента = ВТВнутренние.ДатаДокумента
        И НАЙТИ(ВТВнешние.НомерДокумента, ВТВнутренние.НомерДокумента) > 0

Обратим внимание на ключевые особенности этого запроса:

Нормализация времени: Функция НАЧАЛОПЕРИОДА(..., ДЕНЬ) устраняет расхождения, если в одной таблице дата содержит время проведения, а в другой передана чистая дата без времени.
Использование строковых функций: Функция НАЙТИ(ГдеИскать, ЧтоИскать) > 0 на платформе 8.3 позволяет проверить вхождение подстроки без побочных эффектов дополнения строки пробелами. Также для удаления хвостовых пробелов при конкатенации можно использовать функцию СОКРЛП().
Обработка пустых значений: Функция ЕСТЬNULL гарантирует корректное вычисление разницы сумм, если строка присутствует только в одной из таблиц.

Решение 2: Предварительная обработка данных на встроенном языке

Поиск по неточному совпадению подстрок внутри запроса работает медленно на больших объемах данных. Оптимальный архитектурный подход — предварительно нормализовать номера перед передачей в запрос:

Добавим в обе таблицы значений колонку ЧистыйНомер.
Очистим строковые номера от буквенных префиксов и лидирующих нулей средствами встроенного языка.
В запросе свяжем таблицы по строгому равенству: ВТВнешние.ЧистыйНомер = ВТВнутренние.ЧистыйНомер. Такое соединение позволяет платформе использовать эффективные алгоритмы сопоставления.

Решение 3: Сверка таблиц в цикле без использования запроса

Если мы решаем задачу программным обходом, разберем, как сделать это максимально быстро без потери производительности:


// Создаем результирующую таблицу
ТЗРезультат = Новый ТаблицаЗначений;
ТЗРезультат.Колонки.Добавить("Номер");
ТЗРезультат.Колонки.Добавить("Дата", Новый ОписаниеТипов("Дата"));
ТЗРезультат.Колонки.Добавить("СуммаВнешняя", Новый ОписаниеТипов("Число", Новый КвалификаторыЧисла(15, 2)));
ТЗРезультат.Колонки.Добавить("СуммаВнутренняя", Новый ОписаниеТипов("Число", Новый КвалификаторыЧисла(15, 2)));
ТЗРезультат.Колонки.Добавить("Регистратор");

// Для ускорения поиска индексируем внутреннюю таблицу
ВнутренниеДанные.Индексы.Добавить("ДатаДокумента");

Для Каждого СтрВнешняя Из ВнешниеДанные Цикл
    Отбор = Новый Структура("ДатаДокумента", НачалоДня(СтрВнешняя.ДатаДокумента));
    НайденныеСтроки = ВнутренниеДанные.НайтиСтроки(Отбор);
    
    СтрокаНайдена = Ложь;
    Для Каждого СтрВнутренняя Из НайденныеСтроки Цикл
        // Проверяем вхождение номера
        Если СтрНайти(СтрВнешняя.НомерДокумента, СтрВнутренняя.НомерДокумента) > 0 Тогда
            СтрокаНайдена = Истина;
            
            НоваяЗапись = ТЗРезультат.Добавить();
            НоваяЗапись.Номер = СтрВнешняя.НомерДокумента;
            НоваяЗапись.Дата = СтрВнешняя.ДатаДокумента;
            НоваяЗапись.СуммаВнешняя = СтрВнешняя.Сумма;
            НоваяЗапись.СуммаВнутренняя = СтрВнутренняя.Сумма;
            НоваяЗапись.Регистратор = СтрВнутренняя.Регистратор;
            Прервать;
        КонецЕсли;
    КонецЦикла;
КонецЦикла;

Способы отладки сложных запросов

Когда запрос ведет себя неожиданно, выяснить причину помогают следующие инструменты платформы 1С:

Использование объекта МенеджерВременныхТаблиц: Подключим менеджер к объекту Запрос перед выполнением. После этого в точке останова можно отдельным запросом прочитать содержимое любой промежуточной таблицы (ВТВнешниеДанные, ВТВнутренниеДанные) и выгрузить результат в таблицу для визуальной оценки.
Метод ВыполнитьПакет(): Если разделить текст запроса на пакет через точку с запятой и вызвать метод Запрос.ВыполнитьПакет(), платформа вернет массив результатов каждого шага. Это позволяет моментально увидеть, какие данные попадают во временные таблицы.
Внешняя обработка «Консоль запросов»: Позволяет протестировать поведение условий и выражений на реальных данных без необходимости перезапускать сеанс отладки.