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