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

При разработке отчетов в Системе компоновки данных (СКД) разработчики часто сталкиваются с необходимостью объединения данных из нескольких таблиц или документов. Типичная задача — вывести в одной строке информацию по основному документу (например, по накладной) и связанному с ним подчиненному документу (например, корректировке реализации).

Для решения этой задачи часто используется конструкция ОБЪЕДИНИТЬ ВСЕ (UNION ALL) во вложенном запросе с последующей группировкой результатов. Однако на практике разработчики замечают неприятный эффект: вместо одной красивой свернутой строки отчет выводит две строки. В одной строке заполнены данные накладной, а поля корректировки равны NULL, а во второй строке — наоборот. Почему так происходит и как заставить СКД свернуть эти строки в одну? Давайте разберем эту проблему подробно и рассмотрим несколько вариантов ее решения.

Выясняем причину: почему СУБД отказывается сворачивать строки?

Проанализируем классическую ситуацию. У нас есть первый запрос, который выбирает основные накладные из документа ЗаданиеНаПеревозку, и второй запрос, который выбирает корректировки по этим накладным. В первом запросе у нас физически нет данных о корректировках, поэтому мы принудительно возвращаем NULL для полей корректировки. Во втором запросе, наоборот, у нас есть данные корректировок, но сумма накладной передается как 0, чтобы избежать ее завышения при суммировании.

В итоге во вложенном запросе мы получаем следующий набор строк:

1. Строка 1: Накладная №1, Сумма = 10 000, Корректировка = NULL, Ответственный = NULL
2. Строка 2: Накладная №1, Сумма = 0, Корректировка = Корректировка №1, Ответственный = "Иванов"

Когда мы пытаемся сгруппировать этот результат по полям Накладная, Корректировка и Ответственный, СУБД видит разные комбинации группировочных полей:

• Комбинация 1: [Накладная №1, NULL, NULL]
• Комбинация 2: [Накладная №1, Корректировка №1, "Иванов"]

Поскольку значения в группировочных полях отличаются (значение NULL не равно конкретному документу или строке), СУБД обязана вывести обе строки в результат запроса. Группировка просто не может «схлопнуть» эти данные.

---

Решение 1: Использование агрегатной функции МАКСИМУМ

Самый быстрый и простой способ решить проблему «в лоб» — перенести все нечисловые поля (ссылки на документы, строки, справочники), которые в одном из запросов могут принимать значение NULL, из секции СГРУППИРОВАТЬ ПО в агрегатные функции (в частности, МАКСИМУМ) в секции выбора полей.

Как это работает? В базах данных любое реальное значение считается «больше», чем значение NULL. Следовательно, выражение МАКСИМУМ(ИмяПоля) при сравнении конкретного значения и NULL всегда выберет реальное значение. При этом само поле исключается из списка группировки, и СУБД группирует данные только по ключевым полям (например, по накладной).

Рассмотрим пример реализации такого запроса:

ВЫБРАТЬ
    ВложенныйЗапрос.Накладная КАК Накладная,
    СУММА(ВложенныйЗапрос.СуммаНакладной) КАК СуммаНакладной,
    ВложенныйЗапрос.МаршрутныйЛист КАК МаршрутныйЛист,
    ВложенныйЗапрос.Дата КАК ДатаДокумента,
    СУММА(ВложенныйЗапрос.СуммаКорректировки) КАК СуммаКорректировки,
    МАКСИМУМ(ВложенныйЗапрос.ПричиныНедопоставки) КАК ПричиныНедопоставки,
    МАКСИМУМ(ВложенныйЗапрос.ОтветственноеЛицо) КАК ОтветственноеЛицо,
    МАКСИМУМ(ВложенныйЗапрос.Корректировка) КАК Корректировка
ИЗ
    (ВЫБРАТЬ
        ЗаданиеНаПеревозкуРаспоряжения.Распоряжение.Ссылка КАК Накладная,
        ЗаданиеНаПеревозкуРаспоряжения.Распоряжение.СуммаДокумента КАК СуммаНакладной,
        ЗаданиеНаПеревозкуРаспоряжения.Ссылка КАК МаршрутныйЛист,
        ЗаданиеНаПеревозкуРаспоряжения.Ссылка.Дата КАК Дата,
        0 КАК СуммаКорректировки,
        NULL КАК ПричиныНедопоставки,
        NULL КАК ОтветственноеЛицо,
        NULL КАК Корректировка
    ИЗ
        Документ.ЗаданиеНаПеревозку.Распоряжения КАК ЗаданиеНаПеревозкуРаспоряжения
    ГДЕ
        ЗаданиеНаПеревозкуРаспоряжения.Ссылка.Проведен = ИСТИНА
        И ЗаданиеНаПеревозкуРаспоряжения.Ссылка.Дата МЕЖДУ &ДатаНачала И &ДатаОкончания
    
    ОБЪЕДИНИТЬ ВСЕ
    
    ВЫБРАТЬ РАЗЛИЧНЫЕ
        КорректировкаРеализацииРасхождения.Ссылка.ДокументОснование,
        0,
        ЗаданиеНаПеревозкуРаспоряжения.Ссылка,
        ЗаданиеНаПеревозкуРаспоряжения.Ссылка.Дата,
        ВложенныйЗапрос.СуммаКорректировки,
        КорректировкаРеализацииРасхождения.союз_ПричиныНедопоставки,
        КорректировкаРеализацииРасхождения.союз_ФизЛицо,
        КорректировкаРеализацииРасхождения.Ссылка
    ИЗ
        Документ.КорректировкаРеализации.Расхождения КАК КорректировкаРеализацииРасхождения
    ЛЕВОЕ СОЕДИНЕНИЕ Документ.ЗаданиеНаПеревозку.Распоряжения КАК ЗаданиеНаПеревозкуРаспоряжения
        ПО КорректировкаРеализацииРасхождения.Ссылка.ДокументОснование = ЗаданиеНаПеревозкуРаспоряжения.Распоряжение
    ЛЕВОЕ СОЕДИНЕНИЕ (
        ВЫБРАТЬ
            СУММА(КорректировкаРеализацииРасхождения.СуммаСНДС) КАК СуммаКорректировки,
            КорректировкаРеализацииРасхождения.Ссылка КАК Ссылка
        ИЗ
            Документ.ЗаданиеНаПеревозку.Распоряжения КАК ЗаданиеНаПеревозкуРаспоряжения
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.КорректировкаРеализации.Расхождения КАК КорректировкаРеализацииРасхождения
            ПО ЗаданиеНаПеревозкуРаспоряжения.Распоряжение = КорректировкаРеализацииРасхождения.Ссылка.ДокументОснование
        ГДЕ
            ЗаданиеНаПеревозкуРаспоряжения.Ссылка.Проведен = ИСТИНА
            И ЗаданиеНаПеревозкуРаспоряжения.Ссылка.Дата МЕЖДУ &ДатаНачала И &ДатаОкончания
            И КорректировкаРеализацииРасхождения.Ссылка.Проведен = ИСТИНА
        СГРУППИРОВАТЬ ПО
            КорректировкаРеализацииРасхождения.Ссылка
    ) КАК ВложенныйЗапрос ПО КорректировкаРеализацииРасхождения.Ссылка = ВложенныйЗапрос.Ссылка
    ГДЕ
        КорректировкаРеализацииРасхождения.Ссылка.Проведен = ИСТИНА
        И ЗаданиеНаПеревозкуРаспоряжения.Ссылка.Проведен = ИСТИНА
        И ЗаданиеНаПеревозкуРаспоряжения.Ссылка.Дата МЕЖДУ &ДатаНачала И &ДатаОкончания) КАК ВложенныйЗапрос

СГРУППИРОВАТЬ ПО
    ВложенныйЗапрос.Накладная,
    ВложенныйЗапрос.МаршрутныйЛист,
    ВложенныйЗапрос.Дата

Преимущества метода: Простота реализации, высокая скорость выполнения на небольших базах данных.

Ограничения метода: Если по одной накладной будет проведено несколько разных корректировок, функция МАКСИМУМ вернет только одну корректировку (последнюю по алфавиту/дате ссылки), а остальные корректировки пропадут из отчета. Кроме того, если у корректировок разные ответственные лица, функция МАКСИМУМ может выбрать ответственного из одной корректировки, а причину — из другой, что приведет к искажению данных.

---

Решение 2: Переход на использование Временных Таблиц (ВТ)

Использование вложенных запросов ухудшает читаемость кода и мешает СУБД оптимизировать план запроса. Намного более надежным и производительным подходом в системе 1С является разделение логики на временные таблицы.

Рассмотрим структуру запроса с использованием ВТ:

1. Сначала мы выбираем все накладные из маршрутных листов во временную таблицу ВТ_Накладные.
2. Затем выбираем все корректировки по этим накладным во вторую временную таблицу ВТ_Корректировки.
3. В итоговом запросе связываем обе таблицы с помощью ЛЕВОЕ СОЕДИНЕНИЕ. Это избавит нас от необходимости искусственно подставлять NULL-значения и использовать сомнительные группировки.

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

// Шаг 1. Получаем список накладных
ВЫБРАТЬ
    ЗаданиеНаПеревозкуРаспоряжения.Распоряжение КАК Накладная,
    ЗаданиеНаПеревозкуРаспоряжения.Распоряжение.СуммаДокумента КАК СуммаНакладной,
    ЗаданиеНаПеревозкуРаспоряжения.Ссылка КАК МаршрутныйЛист,
    ЗаданиеНаПеревозкуРаспоряжения.Ссылка.Дата КАК ДатаДокумента
ПОМЕСТИТЬ ВТ_Накладные
ИЗ
    Документ.ЗаданиеНаПеревозку.Распоряжения КАК ЗаданиеНаПеревозкуРаспоряжения
ГДЕ
    ЗаданиеНаПеревозкуРаспоряжения.Ссылка.Проведен = ИСТИНА
    И ЗаданиеНаПеревозкуРаспоряжения.Ссылка.Дата МЕЖДУ &ДатаНачала И &ДатаОкончания
;

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

// Шаг 3. Соединяем данные
ВЫБРАТЬ
    ВТ_Накладные.Накладная,
    ВТ_Накладные.СуммаНакладной,
    ВТ_Накладные.МаршрутныйЛист,
    ВТ_Накладные.ДатаДокумента,
    ЕСТЬNULL(ВТ_Корректировки.СуммаКорректировки, 0) КАК СуммаКорректировки,
    ВТ_Корректировки.ПричиныНедопоставки,
    ВТ_Корректировки.ОтветственноеЛицо,
    ВТ_Корректировки.Корректировка
ИЗ
    ВТ_Накладные КАК ВТ_Накладные
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Корректировки КАК ВТ_Корректировки
        ПО ВТ_Накладные.Накладная = ВТ_Корректировки.Накладная

Почему этот вариант лучше:

• Запрос стал гораздо проще для чтения и дальнейшей отладки в консоли запросов.
• СУБД сможет оптимально построить план запроса, используя индексы временных таблиц.
• Если у одной накладной есть несколько корректировок, отчет выведет их все (будет столько строк, сколько корректировок, но в каждой строке будут корректно заполнены данные накладной).

---

Решение 3: Переносим логику объединения на уровень СКД

Поскольку мы работаем с Системой компоновки данных, решать подобные задачи внутри одного SQL-запроса не обязательно. В СКД заложены мощные механизмы связывания и объединения наборов данных, которые работают на уровне процессора компоновки.

Способ А. Набор данных - Объединение

Вместо использования ключевого слова ОБЪЕДИНИТЬ ВСЕ внутри одного текста запроса, мы можем разделить его на два независимых набора данных типа "Запрос" в конструкторе схемы компоновки данных:

1. Создаем НаборДанных1 (выбирает только данные накладных).
2. Создаем НаборДанных2 (выбирает только данные корректировок).
3. Добавляем родительский набор данных типа Набор данных - Объединение и перетаскиваем туда наши два набора запросов.

СКД автоматически сопоставит поля с одинаковыми именами и сгруппирует их при выводе в отчет согласно настройкам группировки на вкладке "Настройки". При этом СКД сама оптимизирует передачу параметров и сборку результирующей таблицы.

Способ Б. Связи наборов данных (Data Set Links)

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

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

1. Добавим поле СуммаНакладной в список ресурсов на вкладке Ресурсы.
2. Вместо стандартного выражения Сумма(СуммаНакладной) напишем специальную формулу, которая рассчитывает сумму только по уникальным накладным:

Сумма(ВычислитьВыражениеСГруппировкойМассив("Максимум(СуммаНакладной)", "Накладная"))

Эта формула сначала вычислит максимальную сумму для каждой конкретной накладной (что уберет дублирование, возникшее из-за левого соединения с корректировками), а затем просуммирует эти уникальные значения для получения корректного общего итога по отчету.

Рекомендации по выбору метода

• Используйте Решение 1 (МАКСИМУМ) только в самых простых и очевидных отчетах, где гарантированно соблюдается связь «один к одному» (одна накладная — строго одна корректировка) и требуется быстрое исправление "на коленке".
• Отдавайте предпочтение Решению 2 (Временные таблицы), так как это промышленный стандарт разработки на платформе 1С, гарантирующий стабильную скорость работы базы данных и корректность получаемых строк при любых связях документов.
• Используйте Решение 3 (Механизмы СКД), если отчет содержит сложную иерархию данных или если вам требуется выводить корректные итоги по суммам документов без дублирования при связи «один ко многим».