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

← На главную

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

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

Ограничения платформы и типичные ошибки

Проанализируем две ключевые проблемы, с которыми сталкиваются разработчики при попытке решить такую задачу «в лоб»:

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

Способ 1. Пакетный запрос с индексацией таблиц в памяти

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

Разберем по шагам алгоритм реализации:

В первом запросе пакета выбираем начисления из документа и помещаем список уникальных физических лиц во временную таблицу.
В последующих запросах пакета получаем остатки по каждому из нужных счетов с отбором по созданной временной таблице.
Выполняем пакет целиком через метод ВыполнитьПакет().
Выгружаем результаты в таблицы значений и создаем индекс по полю ФизЛицо для каждой вспомогательной таблицы. Поиск по индексированной таблице выполняется за время, близкое к O(1).
Обходим основную таблицу начислений и быстро прикрепляем нужные строки остатков.

Посмотрим на пример программной реализации:


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

Запрос.УстановитьПараметр("Ссылка", СсылкаНаДокумент);
Запрос.УстановитьПараметр("Период", ТекущаяДата());
Запрос.УстановитьПараметр("Счет1", СчетУдержаний1);
Запрос.УстановитьПараметр("Счет2", СчетУдержаний2);
Запрос.УстановитьПараметр("Счет3", СчетУдержаний3);

МассивРезультатов = Запрос.ВыполнитьПакет();

ТЗОснова = МассивРезультатов[1].Выгрузить();
ТЗОстаткиСчет1 = МассивРезультатов[2].Выгрузить();
ТЗОстаткиСчет2 = МассивРезультатов[3].Выгрузить();
ТЗОстаткиСчет3 = МассивРезультатов[4].Выгрузить();

// Индексируем вспомогательные таблицы для мгновенного поиска
ТЗОстаткиСчет1.Индексы.Добавить("ФизЛицо");
ТЗОстаткиСчет2.Индексы.Добавить("ФизЛицо");
ТЗОстаткиСчет3.Индексы.Добавить("ФизЛицо");

Отбор = Новый Структура("ФизЛицо");

Для Каждого Стр Из ТЗОснова Цикл
	Отбор.ФизЛицо = Стр.ФизЛицо;
	
	НайденныеОстатки1 = ТЗОстаткиСчет1.НайтиСтроки(Отбор);
	НайденныеОстатки2 = ТЗОстаткиСчет2.НайтиСтроки(Отбор);
	НайденныеОстатки3 = ТЗОстаткиСчет3.НайтиСтроки(Отбор);
	
	// Далее выполняем необходимую обработку полученных массивов строк
КонецЦикла;

Способ 2. Объединение запросов и выгрузка в Дерево Значений

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

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

Объединяем данные по начислениям и остаткам всех счетов через оператор ОБЪЕДИНИТЬ ВСЕ.
Добавляем поле-признак источника строки (например, ТипДанных: "Начисление", "Счет 1", "Счет 2", "Счет 3").
В конце запроса добавляем секцию ИТОГИ ПО ФизЛицо.
Выгружаем результат с параметром ОбходРезультатаЗапроса.ПоГруппировкам в ДеревоЗначений.

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


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

Результат = Запрос.Выполнить();
Дерево = Результат.Выгрузить(ОбходРезультатаЗапроса.ПоГруппировкам);

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

Способ 3. Использование Системы компоновки данных (СКД)

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

Создаем в схеме компоновки данных несколько наборов данных типа «Набор данных - запрос»: один для начислений документа и отдельные наборы для каждого бухгалтерского счета.
На вкладке Связи наборов данных настраиваем соединение основного набора с подчиненными по полю ФизЛицо.
В структуре настроек добавляем подчиненные таблицы или группировки. СКД автоматически выполнит оптимизированные запросы и свяжет данные без образования декартова произведения.

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