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