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

← На главную

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

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

Анализ проблемы: почему полное соединение трех таблиц неэффективно

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


ПОЛНОЕ СОЕДИНЕНИЕ Регистр3 КАК Регистр3
ПО ЕСТЬNULL(Регистр1.Номенклатура, Регистр2.Номенклатура) = Регистр3.Номенклатура

А в секции ВЫБРАТЬ итоговое поле приходится собирать с помощью глубокой вложенности функций ЕСТЬNULL(Регистр1.Номенклатура, ЕСТЬNULL(Регистр2.Номенклатура, Регистр3.Номенклатура)). Главный недостаток такого подхода заключается в том, что использование функций в условиях соединения на стороне СУБД делает невозможным использование индексов. Оптимизатор СУБД переходит на полное сканирование таблиц, что приводит к блокировкам и резкому падению скорости выполнения запроса на реальных базах.

Почему оператор ОБЪЕДИНИТЬ выводит данные в несколько строк

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


Товар 1 | 33 |  0 |  0
Товар 1 |  0 | 15 |  0
Товар 1 |  0 |  0 | 10

Такое поведение абсолютно закономерно: оператор объединения просто дописывает строки одного набора данных к другому. Чтобы свернуть эти строки в одну, данные необходимо сгруппировать по ключевому полю (номенклатуре) и применить агрегатную функцию СУММА к количественным показателям. Выполнить группировку непосредственно между ветками объединения нельзя — группировать можно только результирующий набор записей.

Для этого в платформе 1С применяются два базовых механизма: вложенный запрос и временная таблица.

Решение 1. Использование вложенного запроса с группировкой

Рассмотрим вариант решения без записи во временные таблицы базы данных. Мы помещаем объединение всех трех источников во вложенный запрос в секции ИЗ, а во внешнем запросе выполняем группировку и суммирование показателей.

Разберем по шагам текст запроса:


ВЫБРАТЬ
    Вложенный.Номенклатура КАК Номенклатура,
    СУММА(Вложенный.Количество1) КАК КоличествоРегистр1,
    СУММА(Вложенный.Количество2) КАК КоличествоРегистр2,
    СУММА(Вложенный.Количество3) КАК КоличествоРегистр3
ИЗ
    (ВЫБРАТЬ
        Регистр1.Номенклатура КАК Номенклатура,
        Регистр1.Количество КАК Количество1,
        0 КАК Количество2,
        0 КАК Количество3
    ИЗ
        РегистрНакопления.Остатки1.Остатки(&Период, ) КАК Регистр1

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

    ВЫБРАТЬ
        Регистр2.Номенклатура,
        0,
        Регистр2.Количество,
        0
    ИЗ
        РегистрНакопления.Остатки2.Остатки(&Период, ) КАК Регистр2

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

    ВЫБРАТЬ
        Регистр3.Номенклатура,
        0,
        0,
        Регистр3.Количество
    ИЗ
        РегистрНакопления.Остатки3.Остатки(&Период, ) КАК Регистр3) КАК Вложенный
СГРУППИРОВАТЬ ПО
    Вложенный.Номенклатура

Обратите внимание на важные особенности этого запроса:

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

Решение 2. Использование пакета запросов и временной таблицы

Если результирующие данные планируется дальше соединять с другими таблицами или алгоритм требует пошаговой отладки, удобнее применить пакетный запрос с созданием временной таблицы через конструкцию ПОМЕСТИТЬ.

Посмотрим на пример реализации через пакет запросов:


ВЫБРАТЬ
    Регистр1.Номенклатура КАК Номенклатура,
    Регистр1.Количество КАК КоличествоРегистр1,
    0 КАК КоличествоРегистр2,
    0 КАК КоличествоРегистр3
ПОМЕСТИТЬ втДанныеВсехРегистров
ИЗ
    РегистрНакопления.Остатки1.Остатки(&Период, ) КАК Регистр1

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

ВЫБРАТЬ
    Регистр2.Номенклатура,
    0,
    Регистр2.Количество,
    0
ИЗ
    РегистрНакопления.Остатки2.Остатки(&Период, ) КАК Регистр2

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

ВЫБРАТЬ
    Регистр3.Номенклатура,
    0,
    0,
    Регистр3.Количество
ИЗ
    РегистрНакопления.Остатки3.Остатки(&Период, ) КАК Регистр3
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    вт.Номенклатура КАК Номенклатура,
    СУММА(вт.КоличествоРегистр1) КАК КоличествоРегистр1,
    СУММА(вт.КоличествоРегистр2) КАК КоличествоРегистр2,
    СУММА(вт.КоличествоРегистр3) КАК КоличествоРегистр3
ИЗ
    втДанныеВсехРегистров КАК вт
СГРУППИРОВАТЬ ПО
    вт.Номенклатура

Важное предостережение по синтаксису: во втором запросе пакета нельзя писать ВЫБРАТЬ * ... СГРУППИРОВАТЬ ПО Номенклатура. Платформа 1С выдаст синтаксическую ошибку. При наличии секции группировки все негруппировочные поля обязательно должны быть обернуты в агрегатные функции, такие как СУММА или МАКСИМУМ.

Решение 3. Реализация с помощью Системы компоновки данных (СКД)

Если отчет разрабатывается с использованием механизма СКД, объединять таблицы вручную в едином тексте запроса не обязательно. Платформа предоставляет встроенные инструменты интеграции наборов данных:

Набор данных — Объединение: создаем корневой элемент типа «Набор данных — объединение». Внутрь него добавляем три отдельных набора данных типа «Запрос», каждый из которых выбирает номенклатуру и остаток из своего регистра. Имена колонок номенклатуры задаются одинаковыми, а числовые поля именуются по-разному. На вкладке «Ресурсы» числовые поля помечаются агрегатом Сумма(...). СКД самостоятельно сопоставит строки и выведет единую итоговую таблицу.
Связи наборов данных: если в схеме компоновки присутствует базовый набор данных (например, справочник номенклатуры), к нему можно подключить остальные запросы через вкладку «Связи наборов данных», указав поле связи Номенклатура. СКД выполнит соединение автоматически без ручного написания сложных связок ПОЛНОЕ СОЕДИНЕНИЕ.