Как сравнить две таблицы в запросе 1С и найти расхождения?

← На главную

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

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

Исходная задача и подготовка тестовых данных

Представим классическую ситуацию: у нас есть две временные таблицы. Первая содержит данные бухгалтерского учета (БУ), вторая — данные управленческого учета (УУ). Нам нужно сопоставить эти таблицы по ключевому полю (например, по номеру строки) и вывести только те строки, которые различаются или отсутствуют в одной из таблиц.

Для демонстрации примеров подготовим исходные временные таблицы ТаблицаБУ и ТаблицаУУ с помощью следующего пакета запросов:


ВЫБРАТЬ
    ВложенныйЗапрос.ДокументБУ КАК ДокументБУ,
    ВложенныйЗапрос.НомерСтроки КАК НомерСтроки,
    ВложенныйЗапрос.Сумма КАК Сумма
ПОМЕСТИТЬ ТаблицаБУ
ИЗ
    (ВЫБРАТЬ "ДокументБУ" КАК ДокументБУ, 1 КАК НомерСтроки, 10 КАК Сумма
    ОБЪЕДИНИТЬ ВСЕ
    ВЫБРАТЬ "ДокументБУ", 2, 20) КАК ВложенныйЗапрос
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВложенныйЗапрос.ДокументУУ КАК ДокументУУ,
    ВложенныйЗапрос.НомерСтроки КАК НомерСтроки,
    ВложенныйЗапрос.Сумма КАК Сумма
ПОМЕСТИТЬ ТаблицаУУ
ИЗ
    (ВЫБРАТЬ "ДокументУУ" КАК ДокументУУ, 1 КАК НомерСтроки, 15 КАК Сумма
    ОБЪЕДИНИТЬ ВСЕ
    ВЫБРАТЬ "ДокументУУ", 2, 25
    ОБЪЕДИНИТЬ ВСЕ
    ВЫБРАТЬ "ДокументУУ", 3, 30) КАК ВложенныйЗапрос

Как мы видим, в бухгалтерской таблице у нас две строки, а в управленческой — три. Наша цель — написать запрос, который выявит лишнюю строку с номером 3, присутствующую только во второй таблице.

Способ 1. Двойное ЛЕВОЕ СОЕДИНЕНИЕ через ОБЪЕДИНИТЬ ВСЕ

Рассмотрим классическое и интуитивно понятное решение проблемы. Чтобы получить симметрическую разность (строки, которые есть в первой таблице, но отсутствуют во второй, и наоборот), мы можем объединить результаты двух левых соединений.

В первом запросе объединения мы берем левую таблицу и ищем расхождения с правой. Во втором запросе — меняем их местами:


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

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

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

Как это работает:

Первая часть запроса соединяет ТаблицаБУ с ТаблицаУУ по ключевому полю НомерСтроки. Условие ГДЕ ТаблицаУУ.НомерСтроки ЕСТЬ NULL отсекает все совпавшие записи, оставляя только те, которые есть в БУ, но отсутствуют в УУ.
Вторая часть делает зеркальную операцию: выбирает записи, которые есть в УУ, но отсутствуют в БУ.
Оператор ОБЪЕДИНИТЬ ВСЕ собирает результаты в единую таблицу.

Этот способ прост в отладке и нагляден, однако он заставляет СУБД выполнять два полноценных левых соединения, что может негативно сказаться на производительности при работе с большими объемами информации.

Способ 2. Оптимизированное ПОЛНОЕ СОЕДИНЕНИЕ

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

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


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

Преимущества данного метода:

Код становится более лаконичным, исчезает необходимость дублировать структуру полей, как в случае с ОБЪЕДИНИТЬ ВСЕ.
Современные СУБД (такие как MS SQL Server или PostgreSQL) умеют эффективно оптимизировать планы запросов для полных соединений, что делает этот вариант быстрее, чем двойное левое соединение на средних объемах данных.

Способ 3. Метод группировки с «весами» (Альтернатива соединениям)

Если вам нужно сравнить таблицы с миллионами строк, операции соединения (JOIN) станут критическим узким местом. В практике оптимизации систем на платформе 1С применяется изящный и крайне производительный метод — объединение таблиц с использованием числовых весов.

Суть метода заключается в том, что мы не соединяем таблицы между собой. Вместо этого мы объединяем их "встык" по вертикали, добавляя к каждой строке искусственное поле Вес. Для первой таблицы мы задаем вес равным 1, а для второй — равным -1. Затем мы выполняем группировку по всем ключевым полям и суммируем веса. Если строки в обеих таблицах были абсолютно идентичны, их суммарный вес станет равен 0, и мы отсечем их в секции ИМЕЮЩИЕ.

Посмотрим на практическую реализацию этого метода:


ВЫБРАТЬ
    ОбъединенныеДанные.НомерСтроки КАК НомерСтроки,
    МАКСИМУМ(ОбъединенныеДанные.ДокументБУ) КАК ДокументБУ,
    МАКСИМУМ(ОбъединенныеДанные.ДокументУУ) КАК ДокументУУ,
    СУММА(ОбъединенныеДанные.Вес) КАК Вес,
    СУММА(ОбъединенныеДанные.Сумма) КАК Сумма
ИЗ
    (ВЫБРАТЬ
        ТаблицаБУ.НомерСтроки КАК НомерСтроки,
        ТаблицаБУ.ДокументБУ КАК ДокументБУ,
        NULL КАК ДокументУУ,
        ТаблицаБУ.Сумма КАК Сумма,
        1 КАК Вес
    ИЗ
        ТаблицаБУ КАК ТаблицаБУ
    
    ОБЪЕДИНИТЬ ВСЕ
    
    ВЫБРАТЬ
        ТаблицаУУ.НомерСтроки,
        NULL,
        ТаблицаУУ.ДокументУУ,
        -ТаблицаУУ.Сумма, // Берем сумму со знаком минус для сверки
        -1
    ИЗ
        ТаблицаУУ КАК ТаблицаУУ) КАК ОбъединенныеДанные

СГРУППИРОВАТЬ ПО
    ОбъединенныеДанные.НомерСтроки

ИМЕЮЩИЕ
    СУММА(ОбъединенныеДанные.Вес) <> 0

Почему этот метод работает быстрее:

При использовании JOIN СУБД вынуждена сопоставлять каждую строку первой таблицы с каждой строкой второй, что при отсутствии индексов приводит к алгоритмической сложности O(N*M).
Метод группировки использует операцию UNION ALL, которая выполняется мгновенно, а последующая группировка обрабатывается СУБД через эффективный алгоритм Hash Aggregate или Stream Aggregate с линейной сложностью O(N).
По значению поля Вес в итоговой выборке мы сразу понимаем происхождение расхождения: если вес равен 1 — строка есть только в БУ, если -1 — только в УУ.

Важнейший нюанс: Индексация временных таблиц

Каким бы способом сопоставления вы ни воспользовались, критически важно помнить о производительности СУБД на уровне индексов. Если временные таблицы содержат более нескольких сотен строк, СУБД будет выполнять сканирование всей таблицы (Table Scan) при поиске связей.

Для оптимизации запроса всегда индексируйте временные таблицы по полям связи при их создании. Например:


ВЫБРАТЬ
    ВложенныйЗапрос.ДокументБУ КАК ДокументБУ,
    ВложенныйЗапрос.НомерСтроки КАК НомерСтроки,
    ВложенныйЗапрос.Сумма КАК Сумма
ПОМЕСТИТЬ ТаблицаБУ
ИЗ
    (...) КАК ВложенныйЗапрос

ИНДЕКСИРОВАТЬ ПО
    НомерСтроки
;

Создание индекса позволит СУБД применить операцию Index Seek вместо полного сканирования, что ускорит выполнение запроса в разы.

Сравнение таблиц значений в коде: современный подход

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


// Устаревший и нерекомендуемый подход!
Если ЗначениеВСтрокуВнутр(ТЗ_БУ) <> ЗначениеВСтрокуВнутр(ТЗ_УУ) Тогда
    // Таблицы не совпадают
КонецЕсли;

Этот метод считается крайне плохой практикой разработки по нескольким причинам:

Метод ЗначениеВСтрокуВнутр возвращает системное представление объекта. Даже малейшее различие в порядке колонок или во внутреннем идентификаторе метаданных приведет к тому, что одинаковые по сути данные будут признаны разными.
Этот способ ресурсоемкий, так как сериализация больших таблиц в память может вызвать падение рабочего процесса или переполнение оперативной памяти (OutOfMemory).

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


// Современный и надежный подход с использованием БСП
ИменаКолонок = "НомерСтроки, Сумма";
ТаблицыИдентичны = ОбщегоНазначения.КоллекцииИдентичны(Таблица1, Таблица2, ИменаКолонок);

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

Интерактивное сравнение таблиц встроенными средствами 1С

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

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

Выгрузите первую таблицу в табличный документ и сохраните ее на диск как файл с расширением .mxl (через меню «Файл — Сохранить как...»).
Аналогичным образом сохраните вторую таблицу в другой файл .mxl.
В главном меню платформы выберите пункт «Файл — Сравнить файлы...».
Укажите пути к обоим сохраненным файлам и нажмите кнопку «ОК».

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