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

← На главную

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

Почему прямое соединение искажает итоговые данные?

Проанализируем классическую схему:

Таблица 1 содержит остатки учетной системы в разрезе справочника Номенклатура.
Таблица 2 содержит данные внешней системы в разрезе специализированного справочника (например, АлкогольнаяПродукция или Алкокод).
Таблица 3 (регистр сведений) хранит соответствие между первой и второй сущностями.

Если связь между элементами не является строго уникальной (1:1), а представляет собой связь один-ко-многим (1:N) — например, одному коду алкогольной продукции соответствует несколько позиций номенклатуры в базе или наоборот, — то обычное соединение через ЛЕВОЕ СОЕДИНЕНИЕ порождает декартово произведение строк. Числовые поля (количество, сумма) при этом дублируются столько раз, сколько соответствий нашлось в регистре, что приводит к кратному завышению остатков при последующем суммировании.

Кроме того, если позиция есть в учетной базе, но для нее еще не создано соответствие в регистре (или товар есть в ЕГАИС, но не сопоставлен с номенклатурой), цепочка левых соединений может потерять такие строки либо заполнить связанные поля значениями NULL.

Способ 1. Свертка остатков до общего ключа верхнего уровня

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

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


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

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

В этом варианте учетные остатки предварительно суммируются по полю АлкогольнаяПродукция, что исключает дублирование строк при последующем соединении с данными ЕГАИС.

Способ 2. Паттерн «Объединение потоков» с итоговой группировкой

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

Разберем логику построения такого запроса:

В первой ветке выбираем остатки из базы, связываем с регистром соответствий и выводим поле КоличествоУчет, а для поля КоличествоЕГАИС передаем фиктивный ноль.
Во второй ветке выбираем остатки внешней системы, связываем с регистром соответствий и передаем 0 в колонку учетного количества, а реальное значение — в КоличествоЕГАИС.
В результирующем пакете группируем данные по обоим классификаторам и суммируем числовые поля.


ВЫБРАТЬ
    ОстаткиПоБазе.Номенклатура КАК Номенклатура,
    Соответствия.АлкогольнаяПродукция КАК АлкогольнаяПродукция,
    ОстаткиПоБазе.КоличествоОстаток КАК КоличествоУчет,
    0 КАК КоличествоЕГАИС
ПОМЕСТИТЬ ВТ_ВсеПотоки
ИЗ
    ВТ_ОстаткиПоБазе КАК ОстаткиПоБазе
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.алкСоответствияАлкогольнойПродукцииЕГАИСИНоменклатуры КАК Соответствия
        ПО ОстаткиПоБазе.Номенклатура = Соответствия.Номенклатура

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

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

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ_ВсеПотоки.Номенклатура КАК Номенклатура,
    ВТ_ВсеПотоки.АлкогольнаяПродукция КАК АлкогольнаяПродукция,
    СУММА(ВТ_ВсеПотоки.КоличествоУчет) КАК КоличествоУчет,
    СУММА(ВТ_ВсеПотоки.КоличествоЕГАИС) КАК КоличествоЕГАИС,
    СУММА(ВТ_ВсеПотоки.КоличествоУчет) - СУММА(ВТ_ВсеПотоки.КоличествоЕГАИС) КАК Расхождение
ИЗ
    ВТ_ВсеПотоки КАК ВТ_ВсеПотоки
СГРУППИРОВАТЬ ПО
    ВТ_ВсеПотоки.Номенклатура,
    ВТ_ВсеПотоки.АлкогольнаяПродукция

Такой подход полностью исключает искажение сумм и позволяет четко увидеть позиции, у которых нет сопоставления (в строках останутся значения NULL по соответствующему измерению), а также сразу рассчитать дельту расхождений.

Способ 3. Реализация через схему компоновки данных (СКД)

Если задача заключается в формировании отчета для пользователя, оптимальным решением является использование механизмов СКД:

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

СКД самостоятельно рассчитывает агрегатные функции СУММА() на каждом уровне группировки без риска завышения итогов, что избавляет от необходимости писать сложные многоэтажные соединения во временных таблицах.