При разработке учетных механизмов, создании печатных форм или формировании документов ввода на основании (например, при выписке счета на оплату или акта по документу Заказ-наряд) часто возникает задача: объединить данные из двух табличных частей одного документа. Типичный сценарий — сопоставить строки табличной части Работы со строками табличной части Товары (затраченные материалы/запчасти), при этом к каждой работе может относиться произвольное количество товаров (от нуля до нескольких десятков), а результирующая сумма работы должна включать как стоимость самой услуги, так и общую стоимость привязанных к ней материалов.
Разберем подробно, почему прямое объединение двух таблиц через простое соединение приводит к дублированию строк и ошибкам, а также рассмотрим несколько надежных способов решения этой задачи на языке запросов 1С.
Прежде чем перейти к составлению запросов, проанализируем важный нюанс архитектуры метаданных 1С. В типовых конфигурациях и при самостоятельной разработке часто пытаются связать подчиненную таблицу (товары) с главной (работы) через системное поле НомерСтроки.
Выясним, к чему это приводит на практике:
НомерСтроки не является неизменным уникальным идентификатором. Если пользователь откроет форму документа, удалит первую строку с работой или переместит строки местами с помощью стрелок, номера строк пересчитаются заново (1, 2, 3...). В подчиненной же таблице товаров значение реквизита привязки останется старым, что приведет к неверной привязке материалов к чужим работам.КлючСвязи (или ИдентификаторСтроки). Это поле может иметь тип Число или Строка (хранящая GUID). При добавлении работы генерируется ключ связи, который затем копируется в строки привязанных к ней товаров.Разберем по шагам классический и наиболее надежный подход к сопоставлению. Если соединять таблицу Работы напрямую с таблицей Товары, в которой по одной работе числится несколько материалов, левое соединение размножит строки работ на количество найденных товаров. Чтобы этого избежать, сначала свернем таблицу материалов по ключу связи, а затем соединим результат со списком работ.
Рассмотрим структуру такого пакетного запроса:
ВЫБРАТЬ
ЗаказНарядТовары.ИдРаботы КАК ИдРаботы,
СУММА(ЗаказНарядТовары.Сумма) КАК СуммаТовары
ПОМЕСТИТЬ ВТ_СвернутыеТовары
ИЗ
Документ.ЗаказНаряд.Товары КАК ЗаказНарядТовары
ГДЕ
ЗаказНарядТовары.Ссылка = &Ссылка
СГРУППИРОВАТЬ ПО
ЗаказНарядТовары.ИдРаботы
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ЗаказНарядРаботы.НомерСтроки КАК НомерСтроки,
ЗаказНарядРаботы.Работа КАК Работа,
ЗаказНарядРаботы.Работа.Наименование КАК РаботаНаименование,
ЗаказНарядРаботы.Сумма КАК СуммаРабот,
ЕСТЬNULL(ВТ_СвернутыеТовары.СуммаТовары, 0) КАК СуммаТоваров,
ЗаказНарядРаботы.Сумма + ЕСТЬNULL(ВТ_СвернутыеТовары.СуммаТовары, 0) КАК ВсегоСумма
ИЗ
Документ.ЗаказНаряд.Работы КАК ЗаказНарядРаботы
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_СвернутыеТовары КАК ВТ_СвернутыеТовары
ПО ЗаказНарядРаботы.НомерСтроки = ВТ_СвернутыеТовары.ИдРаботы
ГДЕ
ЗаказНарядРаботы.Ссылка = &Ссылка
УПОРЯДОЧИТЬ ПО
НомерСтроки
Проанализируем ключевые моменты данного решения:
ВТ_СвернутыеТовары попадают только свернутые суммы по каждому идентификатору работы. Это гарантирует отсутствие дублирования строк при последующем соединении.ЕСТЬNULL: если к услуге не привязано ни одного товара, результат соединения вернет значение NULL. Функция ЕСТЬNULL(ВТ_СвернутыеТовары.СуммаТовары, 0) заменяет пустое значение на ноль, что позволяет корректно выполнять арифметическое сложение.ГДЕ: обратите внимание, что отбор по конкретному документу &Ссылка установлен в обоих запросах пакета.Рассмотрим распространенную ошибку начинающих разработчиков при попытке объединить выборки в одном запросе без временных таблиц. Посмотрим на следующий ошибочный фрагмент кода:
ВЫБРАТЬ
ЗаказНарядРаботы.Работа КАК Работа,
ЗаказНарядРаботы.Сумма КАК СуммаРаботы,
ЗаказНарядТовары.Сумма КАК СуммаТовары
ИЗ
Документ.ЗаказНаряд.Работы КАК ЗаказНарядРаботы
ЛЕВОЕ СОЕДИНЕНИЕ Документ.ЗаказНаряд.Товары КАК ЗаказНарядТовары
ПО ЗаказНарядРаботы.НомерСтроки = ЗаказНарядТовары.ИдРаботы
ГДЕ
ЗаказНарядРаботы.Ссылка = &Ссылка
И ЗаказНарядТовары.Ссылка = &Ссылка
Выясним причину некорректного поведения: когда условие ЗаказНарядТовары.Ссылка = &Ссылка помещается в секцию ГДЕ, СУБД отфильтровывает все строки, где для правой таблицы не нашлось соответствия (так как там значение ссылки равно NULL, а NULL = &Ссылка дает ЛОЖЬ). В результате из итоговой выборки полностью пропадают те работы, к которым не привязаны товары.
Если требуется выполнить соединение в одном запросе без временной таблицы, условие отбора для правой таблицы необходимо перенести непосредственно в условие связи ПО:
ВЫБРАТЬ
ЗаказНарядРаботы.Работа КАК Работа,
ЗаказНарядРаботы.Сумма КАК СуммаРаботы,
СУММА(ЕСТЬNULL(ЗаказНарядТовары.Сумма, 0)) КАК СуммаТоваров,
ЗаказНарядРаботы.Сумма + СУММА(ЕСТЬNULL(ЗаказНарядТовары.Сумма, 0)) КАК ОбщаяСумма
ИЗ
Документ.ЗаказНаряд.Работы КАК ЗаказНарядРаботы
ЛЕВОЕ СОЕДИНЕНИЕ Документ.ЗаказНаряд.Товары КАК ЗаказНарядТовары
ПО (ЗаказНарядРаботы.НомерСтроки = ЗаказНарядТовары.ИдРаботы)
И (ЗаказНарядТовары.Ссылка = &Ссылка)
ГДЕ
ЗаказНарядРаботы.Ссылка = &Ссылка
СГРУППИРОВАТЬ ПО
ЗаказНарядРаботы.НомерСтроки,
ЗаказНарядРаботы.Работа,
ЗаказНарядРаботы.Сумма
В высоконагруженных системах или при обработке больших объемов строк соединение таблиц по составным условиям может работать медленнее, чем операция объединения. Посмотрим на альтернативный подход с использованием конструкции ОБЪЕДИНИТЬ ВСЕ:
ВЫБРАТЬ
ВложенныйЗапрос.ИдРаботы КАК ИдРаботы,
МАКСИМУМ(ВложенныйЗапрос.Работа) КАК Работа,
СУММА(ВложенныйЗапрос.СуммаРабот) КАК СуммаРабот,
СУММА(ВложенныйЗапрос.СуммаТоваров) КАК СуммаТоваров,
СУММА(ВложенныйЗапрос.СуммаРабот + ВложенныйЗапрос.СуммаТоваров) КАК ВсегоСумма
ИЗ
(ВЫБРАТЬ
ЗаказНарядРаботы.НомерСтроки КАК ИдРаботы,
ЗаказНарядРаботы.Работа КАК Работа,
ЗаказНарядРаботы.Сумма КАК СуммаРабот,
0 КАК СуммаТоваров
ИЗ
Документ.ЗаказНаряд.Работы КАК ЗаказНарядРаботы
ГДЕ
ЗаказНарядРаботы.Ссылка = &Ссылка
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ЗаказНарядТовары.ИдРаботы,
ЗНАЧЕНИЕ(Справочник.Номенклатура.ПустаяСсылка),
0,
ЗаказНарядТовары.Сумма
ИЗ
Документ.ЗаказНаряд.Товары КАК ЗаказНарядТовары
ГДЕ
ЗаказНарядТовары.Ссылка = &Ссылка) КАК ВложенныйЗапрос
СГРУППИРОВАТЬ ПО
ВложенныйЗапрос.ИдРаботы
В этом варианте обе табличные части приводятся к единому набору колонок, объединяются в единый массив и группируются по идентификатору. Для строковых и ссылочных полей используется агрегатная функция МАКСИМУМ, а для числовых показателей — СУММА.
Обратите внимание на важный нюанс: если формирование печатной формы или сопоставление таблиц выполняется прямо из формы документа до его интерактивной записи в базу данных, прямые запросы к табличным частям Документ.ЗаказНаряд.Работы и Документ.ЗаказНаряд.Товары не увидят изменений, внесенных пользователем на клиенте.
В такой ситуации рекомендуется выгрузить табличные части объекта во временные таблицы значений и передать их в менеджер временных таблиц запроса:
// В контексте модуля формы или команды документа
ТаблицаРабот = Объект.Работы.Выгрузить();
ТаблицаТоваров = Объект.Товары.Выгрузить();
МенеджерВТ = Новый МенеджерВременныхТаблиц;
Запрос = Новый Запрос;
Запрос.МенеджерВременныхТаблиц = МенеджерВТ;
Запрос.Текст =
"ВЫБРАТЬ * ПОМЕСТИТЬ ТЗ_Работы ИЗ &ТаблицаРабот КАК ТаблицаРабот;
|ВЫБРАТЬ * ПОМЕСТИТЬ ТЗ_Товары ИЗ &ТаблицаТоваров КАК ТаблицаТоваров;";
Запрос.УстановитьПараметр("ТаблицаРабот", ТаблицаРабот);
Запрос.УстановитьПараметр("ТаблицаТоваров", ТаблицаТоваров);
Запрос.Выполнить();
// Далее выполняем сопоставление аналогично Варианту 1,
// обращаясь к виртуальным таблицам ТЗ_Работы и ТЗ_Товары
Таким образом, использование временной таблицы с предварительным суммированием и корректная обработка значений через ЕСТЬNULL полностью решают задачу точного сопоставления и сложения стоимостей связанных табличных частей.