Почему пропадают строки при полном соединении в запросе 1С и как правильно вывести остатки в колонки?

← На главную

При разработке отчетов и обработок в 1С часто возникает типовая задача: получить данные (например, остатки товаров) по нескольким организациям или складам и вывести их в одну строку так, чтобы для каждой организации была своя отдельная колонка. Рассмотрим ситуацию, когда программист решает эту задачу с помощью объединения таблиц через ПОЛНОЕ СОЕДИНЕНИЕ, но в результате получает гораздо меньше строк, чем ожидалось. Проанализируем причину этой ошибки и разберем по шагам правильные способы решения.

Анализ проблемы: почему отбрасываются строки

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


ВЫБРАТЬ 
    ТоварыОрганизацийОстатки.Номенклатура, 
    ТоварыОрганизацийОстатки.СерияНоменклатуры, 
    Орг1.КоличествоОстаток КАК КоличествоОстаток1, 
    Орг2.КоличествоОстаток КАК КоличествоОстаток2, 
    ТоварыОрганизацийОстатки.КоличествоОстаток КАК КоличествоОстаток3 
ИЗ 
    РегистрНакопления.ТоварыОрганизаций.Остатки КАК ТоварыОрганизацийОстатки 
ПОЛНОЕ СОЕДИНЕНИЕ Орг1 КАК Орг1 
    ПО ТоварыОрганизацийОстатки.Номенклатура = Орг1.Номенклатура 
    И ТоварыОрганизацийОстатки.СерияНоменклатуры = Орг1.СерияНоменклатуры 
ПОЛНОЕ СОЕДИНЕНИЕ Орг2 КАК Орг2 
    ПО ТоварыОрганизацийОстатки.Номенклатура = Орг2.Номенклатура 
    И ТоварыОрганизацийОстатки.СерияНоменклатуры = Орг2.СерияНоменклатуры 
ГДЕ 
    ТоварыОрганизацийОстатки.Организация = &Организация3

Выясним причину, по которой этот запрос работает как ВНУТРЕННЕЕ СОЕДИНЕНИЕ и "съедает" данные. Проблема кроется в использовании секции ГДЕ в самом конце запроса к результирующей таблице.

Когда СУБД выполняет внешнее соединение, для записей из первой и второй таблиц (Орг1 и Орг2), которым не нашлось соответствия в базовой таблице третьей организации, платформа заполняет все поля третьей таблицы системным значением NULL. В момент наложения условия ГДЕ ТоварыОрганизацийОстатки.Организация = &Организация3 система пытается сравнить NULL с переданным параметром. Результат всегда равен значению ЛОЖЬ. Строка отбрасывается. Таким образом, любое жесткое условие в секции ГДЕ на поля присоединяемой таблицы неизбежно уничтожает эффект внешнего соединения.

Оптимизация: использование параметров виртуальной таблицы

Прежде чем исправлять структуру запроса, обратим внимание на важный аспект оптимизации. В коде выше фильтрация происходит в секции ГДЕ. С точки зрения производительности платформы 1С — это серьезная ошибка.

Если накладывать условие через ГДЕ, система сначала извлечет из базы данных все остатки по всем существующим организациям, выполнит соединения, и только потом отсеет лишнее. Правильный подход — передавать фильтры непосредственно в параметры виртуальной таблицы регистра. Рассмотрим правильный синтаксис:


РегистрНакопления.ТоварыОрганизаций.Остатки(, Организация В (&МассивОрганизаций)) КАК ТоварыОрганизацийОстатки

В этом случае СУБД прочитает данные только по нужным организациям, что многократно ускорит выполнение запроса.

Решение 1: Использование конструкции ОБЪЕДИНИТЬ ВСЕ и СГРУППИРОВАТЬ

Наиболее надежный и понятный способ свести данные в колонки без использования соединений — применить объединение запросов с последующей группировкой. Разберем этот метод подробно.

Мы создаем три отдельных запроса. В каждом мы выбираем нужные измерения (Номенклатура, Серия), а для ресурсов (КоличествоОстаток) создаем свои уникальные колонки. В тех колонках, которые не относятся к текущей организации, мы ставим нули.


ВЫБРАТЬ 
    ТоварыОрганизацийОстатки.Номенклатура КАК Номенклатура, 
    ТоварыОрганизацийОстатки.СерияНоменклатуры КАК СерияНоменклатуры, 
    ТоварыОрганизацийОстатки.КоличествоОстаток КАК ОстатокОрг1, 
    0 КАК ОстатокОрг2, 
    0 КАК ОстатокОрг3 
ПОМЕСТИТЬ ВТОстатки 
ИЗ 
    РегистрНакопления.ТоварыОрганизаций.Остатки(, Организация = &Организация1) КАК ТоварыОрганизацийОстатки 

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

ВЫБРАТЬ 
    ТоварыОрганизацийОстатки.Номенклатура, 
    ТоварыОрганизацийОстатки.СерияНоменклатуры, 
    0, 
    ТоварыОрганизацийОстатки.КоличествоОстаток, 
    0 
ИЗ 
    РегистрНакопления.ТоварыОрганизаций.Остатки(, Организация = &Организация2) КАК ТоварыОрганизацийОстатки 

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

ВЫБРАТЬ 
    ТоварыОрганизацийОстатки.Номенклатура, 
    ТоварыОрганизацийОстатки.СерияНоменклатуры, 
    0, 
    0, 
    ТоварыОрганизацийОстатки.КоличествоОстаток 
ИЗ 
    РегистрНакопления.ТоварыОрганизаций.Остатки(, Организация = &Организация3) КАК ТоварыОрганизацийОстатки;

////////////////////////////////////////////////////////////////////////////////

ВЫБРАТЬ 
    ВТОстатки.Номенклатура КАК Номенклатура, 
    ВТОстатки.СерияНоменклатуры КАК СерияНоменклатуры, 
    СУММА(ВТОстатки.ОстатокОрг1) КАК ОстатокОрг1, 
    СУММА(ВТОстатки.ОстатокОрг2) КАК ОстатокОрг2, 
    СУММА(ВТОстатки.ОстатокОрг3) КАК ОстатокОрг3 
ИЗ 
    ВТОстатки КАК ВТОстатки 
СГРУППИРОВАТЬ ПО 
    ВТОстатки.Номенклатура, 
    ВТОстатки.СерияНоменклатуры

Преимущества: Этот код работает предсказуемо. Из-за объединения мы получаем множество строк с нулями в "чужих" колонках. Функция СУММА аккуратно "схлопывает" строки с одинаковой Номенклатурой и Серией в одну плоскую таблицу с корректными остатками, игнорируя проблемы отсутствующих данных и значений NULL.

Решение 2: Получение данных одним запросом с помощью конструкции ВЫБОР КОГДА

Если мы хотим избежать использования пакетов запросов и временных таблиц, можно решить задачу изящнее в рамках одного единственного запроса. Проанализируем конструкцию ВЫБОР КОГДА.

Мы обращаемся к регистру один раз, передавая в параметры отбора сразу все нужные организации. Затем на уровне полей выборки мы проверяем, какой организации принадлежит текущая строка, и направляем ее остаток в соответствующую колонку агрегатной функцией.


ВЫБРАТЬ 
    ТоварыОрганизацийОстатки.Номенклатура КАК Номенклатура, 
    ТоварыОрганизацийОстатки.СерияНоменклатуры КАК СерияНоменклатуры, 
    СУММА(ВЫБОР КОГДА ТоварыОрганизацийОстатки.Организация = &Организация1 
            ТОГДА ТоварыОрганизацийОстатки.КоличествоОстаток 
            ИНАЧЕ 0 КОНЕЦ) КАК КоличествоОрг1, 
    СУММА(ВЫБОР КОГДА ТоварыОрганизацийОстатки.Организация = &Организация2 
            ТОГДА ТоварыОрганизацийОстатки.КоличествоОстаток 
            ИНАЧЕ 0 КОНЕЦ) КАК КоличествоОрг2, 
    СУММА(ВЫБОР КОГДА ТоварыОрганизацийОстатки.Организация = &Организация3 
            ТОГДА ТоварыОрганизацийОстатки.КоличествоОстаток 
            ИНАЧЕ 0 КОНЕЦ) КАК КоличествоОрг3 
ИЗ 
    РегистрНакопления.ТоварыОрганизаций.Остатки(, Организация В (&Организация1, &Организация2, &Организация3)) КАК ТоварыОрганизацийОстатки 
СГРУППИРОВАТЬ ПО 
    ТоварыОрганизацийОстатки.Номенклатура, 
    ТоварыОрганизацийОстатки.СерияНоменклатуры

Этот код более лаконичен и, как правило, быстрее отрабатывает на стороне СУБД, так как обращение к таблице итогов выполняется единожды.

Архитектурное решение: Использование Системы Компоновки Данных (СКД)

Хотя оба вышеприведенных способа отлично работают, они обладают одним недостатком: жесткое кодирование колонок. Что если в базе появится четвертая или пятая организация? Придется вносить изменения в конфигурацию и переписывать запрос.

Рассмотрим самый правильный архитектурный подход в 1С для вывода таких отчетов — использование возможностей Системы компоновки данных. Нам не нужно "разворачивать" колонки вручную с помощью ОБЪЕДИНЕНИЕ или ВЫБОР КОГДА.

Создаем максимально простой и плоский запрос к виртуальной таблице: ВЫБРАТЬ Номенклатура, Серия, Организация, КоличествоОстаток ИЗ РегистрНакопления.Остатки(...)
Передаем отбор по массиву организаций в параметры виртуальной таблицы.
В настройках структуры отчета СКД (вкладка "Настройки") создаем группировку типа "Таблица".
В строки (группировки строк) помещаем Номенклатура и Серия.
В колонки (группировки колонок) помещаем измерение Организация.

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