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

← На главную

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

Выясним причину проблемы: почему удваиваются и перемножаются суммы

Рассмотрим типовую ситуацию. Представим, что у нас есть главная таблица (Таблица1) и две подчиненные таблицы (Таблица2 и Таблица3), содержащие данные с привязкой по id. В подчиненных таблицах одному значению id может соответствовать несколько записей (связь «один ко многим»).

Если написать запрос с последовательными левыми соединениями:


ВЫБРАТЬ
	Таблица1.id КАК id,
	Таблица1.Pole1 КАК Pole1,
	Таблица1.summ1 КАК summ1,
	Таблица2.Pole2 КАК Pole2,
	Таблица2.summ2 КАК summ2,
	Таблица3.Pole3 КАК Pole3,
	Таблица3.summ3 КАК summ3
ИЗ
	Таблица1 КАК Таблица1
		ЛЕВОЕ СОЕДИНЕНИЕ Таблица2 КАК Таблица2
		ПО Таблица1.id = Таблица2.id
		ЛЕВОЕ СОЕДИНЕНИЕ Таблица3 КАК Таблица3
		ПО Таблица1.id = Таблица3.id

СУБД выполняет соединение реляционных таблиц через декартово произведение для подчиненных записей. Если для id = 1 в первой подчиненной таблице присутствуют 2 строки, и во второй подчиненной таблице — 2 строки, то в результате соединения мы получим 2 × 2 = 4 строки. Каждая строка из второй таблицы свяжется с каждой строкой из третьей таблицы.

При последующей группировке или расчете итогов по полю summ2 значение суммируется 2 раза вместо одного (получаем 140 вместо 70), а по полю summ3 — также перемножается (получаем 220 вместо 110).

Способ 1. Объединение выборок с помощью конструкции ОБЪЕДИНИТЬ ВСЕ

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

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


ВЫБРАТЬ
	Таблица1.id КАК id,
	Таблица1.Pole1 КАК Pole1,
	Таблица1.summ1 КАК summ1,
	Таблица2.Pole2 КАК Pole2,
	Таблица2.summ2 КАК summ2,
	NULL КАК Pole3,
	NULL КАК summ3
ИЗ
	Таблица1 КАК Таблица1
		ЛЕВОЕ СОЕДИНЕНИЕ Таблица2 КАК Таблица2
		ПО Таблица1.id = Таблица2.id

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

ВЫБРАТЬ
	Таблица1.id,
	Таблица1.Pole1,
	Таблица1.summ1,
	NULL,
	NULL,
	Таблица3.Pole3,
	Таблица3.summ3
ИЗ
	Таблица1 КАК Таблица1
		ЛЕВОЕ СОЕДИНЕНИЕ Таблица3 КАК Таблица3
		ПО Таблица1.id = Таблица3.id

В данном случае строки из Таблица2 и Таблица3 выводится параллельно в разных блоках, а недостающие колонки заполняются значениями NULL. Это исключает декартово произведение, сохраняет детализацию строк и позволяет корректно рассчитывать итоговые показатели.

Способ 2. Предварительная агрегация во временных таблицах

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

Сначала сгруппируем данные подчиненных таблиц по полю id и поместим их во временные таблицы (ВТ_Таблица2 и ВТ_Таблица3).
После этого выполним левое соединение главной таблицы со сгруппированными временными таблицами.

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


ВЫБРАТЬ
	Таблица2.id КАК id,
	СУММА(Таблица2.summ2) КАК summ2
ПОМЕСТИТЬ ВТ_Таблица2
ИЗ
	Таблица2 КАК Таблица2
СГРУППИРОВАТЬ ПО
	Таблица2.id
;

ВЫБРАТЬ
	Таблица3.id КАК id,
	СУММА(Таблица3.summ3) КАК summ3
ПОМЕСТИТЬ ВТ_Таблица3
ИЗ
	Таблица3 КАК Таблица3
СГРУППИРОВАТЬ ПО
	Таблица3.id
;

ВЫБРАТЬ
	Таблица1.id КАК id,
	Таблица1.Pole1 КАК Pole1,
	Таблица1.summ1 КАК summ1,
	ЕСТЬNULL(ВТ_Таблица2.summ2, 0) КАК summ2,
	ЕСТЬNULL(ВТ_Таблица3.summ3, 0) КАК summ3
ИЗ
	Таблица1 КАК Таблица1
		ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Таблица2 КАК ВТ_Таблица2
		ПО Таблица1.id = ВТ_Таблица2.id
		ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Таблица3 КАК ВТ_Таблица3
		ПО Таблица1.id = ВТ_Таблица3.id

Способ 3. Разделение на независимые наборы данных в СКД

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

Создаем НаборДанных1 для первой таблицы, НаборДанных2 — для второй, НаборДанных3 — для третьей.
Переходим на вкладку Связи наборов данных и связываем наборы по полю id.

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