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

← На главную

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

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

Постановка технической задачи: Разузлование спецификаций

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

Строка из первой таблицы: Стол (ГП) — Крышка стола (ПФ) — Болтик (Материал).
Строка из второй таблицы: Стол (ГП) — Крышка стола (ПФ) — Ящик с ручкой (ПФ) — Ножка стола (Материал).

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

Базовое решение: Использование конструкции ОБЪЕДИНИТЬ ВСЕ

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


ВЫБРАТЬ "Вася" КАК Имя
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ "Федя"

В результате выполнения такого запроса мы получим одну колонку с именем "Имя" и две строки: в первой строке будет "Вася", во второй — "Федя".

Важнейшее правило ОБЪЕДИНИТЬ ВСЕ: Совпадение структуры колонок

Платформа 1С:Предприятие накладывает жесткое ограничение на использование операторов объединения: во всех объединяемых запросах должно быть строго одинаковое количество полей, их порядок и совместимые типы данных.

Если в одной временной таблице у нас 5 колонок, а во второй 6, простой запрос вида ВЫБРАТЬ * ИЗ ВТ_Таблица1 ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ * ИЗ ВТ_Таблица2 вернет критическую ошибку: «В запросе разное количество полей в объединяемых запросах».

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


// Запрос к первой таблице (где колонок меньше) - добавляем пустое поле NULL
ВЫБРАТЬ
    ВТ_ПФ2.НоменклатураКод КАК НоменклатураКод,
    ВТ_ПФ2.НоменклатураПродукции КАК НоменклатураПродукции,
    ВТ_ПФ2.Номенклатура КАК Номенклатура,
    NULL КАК Номенклатура1, // Поле-заглушка для выравнивания структуры
    ВТ_ПФ2.КоличествоПриход КАК КоличествоПриход,
    ВТ_ПФ2.СтоимостьПриход КАК СтоимостьПриход
ИЗ
    ВТ_ПФ2 КАК ВТ_ПФ2

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

// Запрос ко второй таблице (где полей больше)
ВЫБРАТЬ
    ВТ_МАТиПФ2.НоменклатураКод,
    ВТ_МАТиПФ2.НоменклатураПродукции,
    ВТ_МАТиПФ2.Номенклатура,
    ВТ_МАТиПФ2.Номенклатура1, // Реальное поле с данными следующего уровня вложенности
    ВТ_МАТиПФ2.КоличествоПриход,
    ВТ_МАТиПФ2.СтоимостьПриход
ИЗ
    ВТ_МАТиПФ2 КАК ВТ_МАТиПФ2

Вместо значения NULL в зависимости от прикладной задачи можно использовать дефолтные типы. Например, 0 для числовых показателей или ЗНАЧЕНИЕ(Справочник.Номенклатура.ПустаяСсылка) для ссылочных типов. Использование пустой ссылки предпочтительнее, если в дальнейшем к этому полю планируется применять группировки или использовать его в СКД, так как это позволит избежать ошибок сравнения типов.

Практический разбор схемы запросов автора

Проанализируем структуру пакета запросов, предложенную автором темы. Мы видим последовательное формирование временных таблиц для выявления связей между полуфабрикатами и материалами на основе регистров накопления ПродукцияВПроизводстве и ПеременныеЗатраты.Обороты.

Сначала выбираются первичные выпуски готовой продукции:


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

Затем выполняется левое соединение с оборотами затрат для получения первого уровня комплектующих (материалов и полуфабрикатов):


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

После этого автор отфильтровывает только полуфабрикаты для последующей развертки второго уровня:


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

И, наконец, формирует таблицу второго уровня, соединяя выделенные полуфабрикаты с регистром затрат повторно:


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

Для вывода консолидированного результата нам остается применить операцию ОБЪЕДИНИТЬ ВСЕ к таблицам ВТ_ПФ2 и ВТ_МАТиПФ2, предварительно выровняв их структуру с помощью явного перечисления полей и добавления пустого значения для колонки Номенклатура1 в первом запросе объединения.

Ограничения «жесткого» разузлования в запросах

Описанный выше метод последовательного левого соединения таблиц (ГП → ПФ1 → ПФ2 → Материалы) имеет критические недостатки при использовании в реальных производственных базах данных:

Фиксированная вложенность спецификаций. Наш запрос рассчитан строго на определенную глубину дерева (2 или 3 уровня). Если на предприятии появится сложная продукция с 5, 10 или более уровнями вложенности, запрос просто не увидит нижние уровни состава. Придется дописывать код и усложнять пакет запросов.
Падение производительности СУБД. Множественные соединения больших таблиц «на лету» создают колоссальную нагрузку на дисковую подсистему и процессор сервера СУБД, приводя к неоптимальным планам выполнения запросов.

Рассмотрим альтернативные и более гибкие архитектурные паттерны для решения задачи разузлования номенклатуры в 1С.

Альтернативный вариант 1: Итерационный цикл во встроенном языке 1С

Вместо написания одного сверхсложного статического запроса мы можем использовать итерационный алгоритм с применением менеджера временных таблиц в цикле Пока.

Логика работы алгоритма следующая:

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

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


МенеджерВТ = Новый МенеджерВременныхТаблиц;
Запрос = Новый Запрос;
Запрос.МенеджерВременныхТаблиц = МенеджерВТ;

// Шаг 1: Помещаем исходную номенклатуру во временную таблицу
Запрос.Текст = 
"ВЫБРАТЬ
|	ИсходныеДанные.Номенклатура КАК Номенклатура,
|	ИсходныеДанные.Количество КАК Количество
|ПОМЕСТИТЬ ВТ_ТекущиеУзлы
|ИЗ
|	&ТаблицаПродукции КАК ИсходныеДанные";

Запрос.УстановитьПараметр("ТаблицаПродукции", ТаблицаПродукции);
Запрос.Выполнить();

Итерация = 1;
Пока Истина Цикл
	
	// Шаг 2: Ищем комплектующие для узлов текущего уровня
	Запрос.Текст = 
	"ВЫБРАТЬ
	|	Спецификации.Номенклатура КАК РодУзел,
	|	Спецификации.Материал КАК Комплектующий,
	|	Спецификации.Количество * ВТ.Количество КАК Кол
	|ПОМЕСТИТЬ ВТ_НовыйУровень
	|ИЗ
	|	ВТ_ТекущиеУзлы КАК ВТ
	|	ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.СпецификацииНоменклатуры КАК Спецификации
	|	ПО ВТ.Номенклатура = Спецификации.Номенклатура";
	
	Попытка
		Запрос.Выполнить();
	Исключение
		Прервать;
	КонецПопытки;
	
	// Проверяем, нашли ли что-то на новом уровне
	Запрос.Текст = "ВЫБРАТЬ ПЕРВЫЕ 1 1 ИЗ ВТ_НовыйУровень";
	Если Запрос.Выполнить().Пустой() Тогда
		Прервать; // Развертка завершена, дошли до базовых материалов
	КонецЕсли;
	
	// Переносим данные из ВТ_НовыйУровень в итоговую таблицу результатов
	// И готовим ВТ_ТекущиеУзлы для следующей итерации...
	
	Итерация = Итерация + 1;
КонецЦикла;

Альтернативный вариант 2: Использование связей наборов данных в СКД

Система компоновки данных (СКД) предоставляет мощный и элегантный встроенный механизм для работы с иерархическими структурами и рекурсией. Нам не нужно вручную прописывать соединения или циклы.

Достаточно создать один набор данных (запрос к спецификациям/затратам) и настроить в конструкторе СКД вкладку «Связи наборов данных»:

В качестве источника связи указываем наш набор данных.
В качестве приемника указываем этот же самый набор данных.
Выражение связи: НоменклатураПродукции = Номенклатура.

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

Специфика CTE (Common Table Expressions) в контексте 1С

В сообщениях 10 и 17 участники обсуждения упоминают аббревиатуру ЦТЕ (CTE — Common Table Expressions, обобщенные табличные выражения). Обобщенные табличные выражения активно используются в классическом SQL (конструкция WITH ... AS), в том числе для организации рекурсивных запросов к СУБД.

Важно помнить: Встроенный язык запросов платформы 1С:Предприятие напрямую не поддерживает синтаксис CTE.

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

Неиерархические CTE в СУБД (например, MS SQL Server) зачастую компилируются «inline» как встроенные подзапросы и повторно вычисляются при каждом обращении к ним, если оптимизатор запросов не решит их материализовать.
Временные таблицы в 1С всегда физически создаются во временной базе данных СУБД (например, в базе tempdb для MS SQL или схеме pg_temp в PostgreSQL). Платформа 1С берет на себя управление индексами: при помещении данных во временную таблицу по полям, участвующим в последующих соединениях, автоматически создаются индексы и обновляется статистика СУБД. Это гарантирует высокую предсказуемость и скорость работы сложных пакетов запросов.

Современное решение (для платформы 8.3.25 и выше)

Если вы разрабатываете решение на современных версиях платформы 1С:Предприятие (начиная с релиза 8.3.25), в вашем распоряжении появляется новая долгожданная возможность для работы с временными таблицами — конструкция ДОБАВИТЬ (аналог SQL-оператора INSERT INTO).

Раньше временные таблицы в 1С были иммутабельными (неизменяемыми): их нельзя было дополнить строками после создания, приходилось генерировать новые ВТ через громоздкие объединения. Теперь мы можем динамически дописывать строки в ранее созданную временную таблицу. Это позволяет реализовывать итерационные алгоритмы разузлования прямо в рамках одного пакета запросов, делая код чистым, лаконичным и быстродействующим.